One two Many - choosen by a form check box??

[Marcio]

Hello there,

I´m a begginner in access development. I´ve started to develop an aplication and it is going pretty well so far. It´s an event controler.

The problem is:

There is a form that the user can create/edit the events.
But this events happens in many cities. As a solution a thought the best way would be to make a button on EVENT FORM that open a WHAT SITES FORM and then the user would mark the cities that has participate of the event... submit... and then the cities would be associeded to that event in a field... separated by comma...in alphabeticall order... whatever...

HOW CAN i DO THIS?????

the explanain was clear enough???


Thanks in advance,
Márcio

 

[Parker]

Read up on 'Relations Collection' in VBA help.

Using this you can write a piece of code that will do what you want.

 

[Rich]

It's not a good idea to store several records as one, however if you insist you could use a multiselect list box

 

[Marcio]

so what to do them?

It's not a good idea to store several records as one, however if you insist you could use a multiselect list box

Ok, thanks...
but now I ask myself... what is the best solution?

I have a table where I save some events. The input/edit of this data is made in a form.

But each Event occurr in many diferents places all over the world.
So.. how can I do.... a form that I can mark some check box... whatever.. and then it will be recorded that this event happened in the checked places.

What tables should I have...? What relationship this tables should have?
HELP!


Thanks in advance,
Márcio

 

[The_Doc_Man]

Your solution requires not less than three tables.

Parent table: The event. Has to include an event number as prime key along with everything specific to the event EXCEPT its location data. The event number PROBABLY could be an autonumber as long as you don't have constraints on what the number represents.

Child table: The event's location. Has to include an event number to link to the parent. Must also include a location field. In this table, the event number is a foreign key. Either the child table has no prime key or its prime key is an autonumber that doesn't enter into this form.

Location table: The list of possible locations including anything you need to know about that location. Like long address, etc. If there is a short number like a branch number, you might wish to use it in place of the longer location name, but maybe not.

Relationships:
One-to-Many, event being the one, event locations being the many.
One-to-Many, event location being the one, possible event locations being the many.

How to implement:

On the form where you enter the event data, include a sub-form.

In the sub-form, build a field that has a drop-down box based on the possible locations. The combo box or list box wizard can help you here. Drive this sub-form from the event locations table.

Link the sub-form and main form through the event number. Subforms have properties called ParentLink and ChildLink or something similar. The idea is that this link identifies the field names for parent and child through which the linkage is established, i.e. the basis for the relation.

How you would use it:

Enter data for an event.

In the sub-form, select the drop-down for the first location. Hit the TAB key. (This should create a new entry, another drop-down box that is currently empty.) Select another location. TAB again.

At some point you reach the last location for that event. Use your mouse to save the parent by clicking on the selector bar.

Possible concept problem: In this simplified model, you cannot have an event without a location.

Possible procedural problem: In this simplified model, you cannot select a location that isn't defined yet. So if you open a new "office" you must add it to the list of known event sites FIRST.

 

[Marcio]

Event FORM --- SUBFORM RELATIONSHIP

Ok!!!! Now we are talking!
Actually I have already implemented your sugestions and its already working. But it still have some pendencies.

As I hope you can see, I´ve attached some bitmaps.. to clear my intentions.

First PROBLEM:

I´ve divided the "potential sites" in two tables - national and international.
I´d like to make a radio button on "Event form" so that I could first choose If the site Í´m going to ADD is NATIONAL or INTERNATIONAL and then the form will only show one of them.

Also, when navigating throw events already registered, I´d like to see the sites... no matter if they are national or international.

Second Problem:

I´ve already figured out how to make a QUERY by users input date... that result in EVENTS between the wanted dates... but i can´t see the sites related to that events...


Third Problem:

I also want to make a search that results in EVENTS realized in between a specific date and that happen only in a specific site.... is that possible?


-----------------------------------------------------

I´m gonna include my relationships print screen so that you can tell me if this is the best solution....


THANK A LOT FOR THE PREVIUS HELP,

THANKS in ADVANCE to next one

Márcio


thanks in advance,
Márcio