Relationship Help?

[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.

 

[Marshall Barton]

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

Set the list box's RowSource to a query that joins the
tblConventionLocations table to the ConventionCenter table
with a where clause to filter the query to the form's ConvID
text box:

SELECT ConventionCenter.ConvCtrID, ConventionCenter.descr
FROM ConventionCenter INNER JOIN tblConventionLocations
ON ConventionCenter.ConvCtrID=ConventionLocations.ConvCtrID
WHERE ConventionLocations.ConvID=Forms!theform.txtConvID


Personally, I prefer to use a continuous subform instead of
a list box. Base the subform on the ConventionLocations
table with the Link Master/Chhild properties set to ConvID.
Then, use a combo box with its RowSource set to a query
based on the ConventionCenter table:

SELECT ConvCtrID, descr
FROM ConventionCenter
ORDER BY descr