Help with Relationship?

[cvegas]

This has always stumpted me and I really need to learn how to accomplish this.

I'm really not sure if this is a relationship issue, a linking issue, or
something completly different.

We have a table that has Convention Information in it.
ConvID is a autonumber.

We have another table with Las Vegas Convention Center information in it.
ConvCtrID is also an auto number.

A particular convention can take place in 1 or many of the Convention Centers.

I have created another table tblConventionLocations which has two fields
ConvID and ConvCtrID

What I want to accomplish is to View a form, based on the tblConventions,
and to have a listbox showing what convention center(s) this show will be
held in.

I also need to be able to add new Convention information, assign 1 or
multiple convention centers to that record and have the new info display in
the listbox

I'm just not getting it. Any help or diection would be appreciated.

 

[Ted Allen]

This is a common type of relationship. It sounds like you have the
relationship correct. You have a table of conventions, a table of convention
centers, and a join table which has a many to one relationship with each of
those tables.

Achieving what you want to do on the form will be very easy if you use
subforms rather than a list box (although I haven't tried setting up list
boxes to work in this manner so it is possible you could do it that way - but
it doesn't seem like it would be a straightforward).

To do what you want to do using subforms, create a new form just using the
join table and the convention center table as the data source. Then place a
dropdown box on the form that will list the convention center but be bound to
the convention center ID (actually there are a number of ways to set up the
combo, that is another topic - post back if you need help with the combo).
There is no need to add the convention ID to the form, although it will exist
in the data source.

Now, go to your main form, which uses only the conventions table as the data
source. Add a subform object to the form. A wizard will ask you how you
want to join the subform to the form. Select to join based on the fields
containing the convention ID (which exists in the data sources for both
forms, even though it isn't displayed on the subform).

Once you have done this, your subform will automatically display the entries
in your join table for a given convention. In addition, if you add a new
record in the subform by selecting an additional convention center, Access
will automatically fill in the Convention ID in the join table based on the
join between the form and the subform.

Hopefully this will help, post back if you have questions.

-Ted Allen