Many-to-many relationship problem

  • Thread starter Thread starter GIraffe
  • Start date Start date
G

GIraffe

The db I have has 3 tables, agreements, organizations, amendments. 1
agreement can have many organizations and/or many amendments, 1 organization
can be part of many agreements. I have setup a junction table (junctionID
(PK), agreementID, orgID, combo box (so I can see the org name) using
organizations table). I use an organization subform in my agreements form to
indicate which organizations are part of an agreement.

My organization name has 2 fields, Line 1 & Line 2. If I use Line 1 only
from the organizations table, any organizations I add, I can see. However,
if I use a query to combine Line 1 & Line 2 (through an IIf statement to make
up the entire organization name), and use this query for my combobox any new
organization I add, I cannot see to use.

I’d appreciate your thoughts.

G
 
Hi G

To see the data from multiple fields in a combo box, you must use an
expression to concatenate the values into a single calculated field in your
RowSource query. For example:

Select OrgID, OrgName1 & " " & OrgName2 from Organizations;

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your forms
in a similar way.
 
Back
Top