Many-to-many subform guidance needed

[cjg.groups]

How can a form and subform update an intersection/junction table used
to maintain their many-to-many relationship? Also, please advise re:
table structure, synchronization, and relationships/linking.

Goal:
Orders can contain 1 to 4 Contacts. Contacts can associate with
multiple orders. The OrderForm contains the ContactSubForm which
should only show Contacts on that order.

Tables:
Orders - OrderID (PK)
Contacts - ContactID (PK)
RelOrdersContacts - OrderID (PK), ContactID (PK) (can I have two PKs?)

Query:
ContactsForCurrentOrder -
SELECT RelOrdersContacts.OrderID, Contacts.*
FROM Orders INNER JOIN (Contacts INNER JOIN RelOrdersContacts ON
Contacts.ContactID = RelOrdersContacts.ContactID) ON Orders.OrderID =
RelOrdersContacts.OrderID;

Forms:
OrderForm - based on Orders table
ContactSubForm - based on ContactsForCurrentOrder query, Master/Child
Fields linked on OrderID.

Orders and Contacts are related many-to-many using the
intersection/junction table RelOrdersContacts. Is there a better way?
Can I store other data there besides OrderID and ContactID?.

When I add new records to the Contacts subform, can it update
RelOrdersContacts to associate the Contact with the form's currently
shown Order? Is there a better way to maintain this many-to-many using
forms?

To establish the Master/Child Fields link, I included OrderID in the
subform's record source query. Now I get "Field can not be updated"
errors when adding new subform records (because the main form and
subform query are accessing Orders?). Should I remove the link and use
query criteria [Forms]![OrderForm]![OrderID] to show contacts for the
current order?

Why the nested INNER JOIN in the query (generated via GUI) for the
relationship? Why not WHERE Orders.OrderID=RelOrdersContacts.OrderID
AND Contacts.ContactID=RelOrdersContacts.ContactID?

This is my first database and after 9 hours reading Newsgroups, I've
made little progress. I'm tempted to do it "the wrong way" just to get
it done. Thank you very much for your help.

 

[Allen Browne]

Okay, that's a good question.

One order can have multiple contacts, and one contact can be in multiple
orders, so the junction table (RelOrdersContacts) makes perfect sense. The
table will not have 2 primary keys; it will have a two-field primary key. To
achieve that in table design, select both fields together (by clicking the
"record selector" to the left of the field name), and click the primary key
icon on the toolbar.

The interface will be a main form bound to the Orders table, with a subform
bound to the RelOrdersContacts table. Typically the subform will have a
combo box for selecting the contact.

Now your question is, how can you add a new contact? A simple solution that
lots of us use is to use the DblClick of the combo to open the Contacts form
so the user can add a new contact. Then use the AfterUpdate event of the
Contacts form to requery to combo so it gets to know about the new (or
changed) contact straight away.

The combo's DblClick event contains:
DoCmd.OpenForm "frmContacts", ,,,acFormAdd
and in this form's AfterUpdate event, requery the combo if the other form is
open:
Private Sub Form_AfterUdate()
If CurrentProject.AllForms("Orders").IsLoaded Then
Forms!Orders.RelOrdersContacts.Form!ContactID.Requery
End If
End Sub

There is another option if the value you are typing into the combo goes into
its Bound Column (i.e. it is not bound to a hidden autonumber) and is the
only field you need to enter. In that case you can use the NotInList event
of the combo. Details in:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can a form and subform update an intersection/junction table used
to maintain their many-to-many relationship? Also, please advise re:
table structure, synchronization, and relationships/linking.

Goal:
Orders can contain 1 to 4 Contacts. Contacts can associate with
multiple orders. The OrderForm contains the ContactSubForm which
should only show Contacts on that order.

Tables:
Orders - OrderID (PK)
Contacts - ContactID (PK)
RelOrdersContacts - OrderID (PK), ContactID (PK) (can I have two PKs?)

Query:
ContactsForCurrentOrder -
SELECT RelOrdersContacts.OrderID, Contacts.*
FROM Orders INNER JOIN (Contacts INNER JOIN RelOrdersContacts ON
Contacts.ContactID = RelOrdersContacts.ContactID) ON Orders.OrderID =
RelOrdersContacts.OrderID;

Forms:
OrderForm - based on Orders table
ContactSubForm - based on ContactsForCurrentOrder query, Master/Child
Fields linked on OrderID.

Orders and Contacts are related many-to-many using the
intersection/junction table RelOrdersContacts. Is there a better way?
Can I store other data there besides OrderID and ContactID?.

When I add new records to the Contacts subform, can it update
RelOrdersContacts to associate the Contact with the form's currently
shown Order? Is there a better way to maintain this many-to-many using
forms?

To establish the Master/Child Fields link, I included OrderID in the
subform's record source query. Now I get "Field can not be updated"
errors when adding new subform records (because the main form and
subform query are accessing Orders?). Should I remove the link and use
query criteria [Forms]![OrderForm]![OrderID] to show contacts for the
current order?

Why the nested INNER JOIN in the query (generated via GUI) for the
relationship? Why not WHERE Orders.OrderID=RelOrdersContacts.OrderID
AND Contacts.ContactID=RelOrdersContacts.ContactID?

This is my first database and after 9 hours reading Newsgroups, I've
made little progress. I'm tempted to do it "the wrong way" just to get
it done. Thank you very much for your help.

 

[cjg.groups]

Allen,

Thank you for your reply. Your posts throughout the years have already
helped me so much.

We may be thinking about the subform differently. ContactSubForm is
nested within OrderForm ("continuous"?), showing and taking input
for all Contacts fields; always open. I can add contacts by typing
them in.

Adding a contact does not update the junction table; thus, not
associating the contact with the order. With OrderForm viewing OrderID
2, and typing into ContactSubForm creating ContactID 5,
RelOrdersContacts should add the pair 2,5. Do I have to code that
manual update or can Access maintain the many-to-many relationship by
updating the junction table?

 

[Allen Browne]

No, it is not going to work that way.

The subform is really designed to handle the records related to the one in
the main form. Since there is no relationship between Orders and Contacts,
you are using a subform in a different kind of way. It would be possible to
use the AfterUpdate event of the subform to execute an Append query
statement to add a record to the junction table, but that is probably less
than ideal. Better to base the subform on the junction table IMHO.

 

[cjg.groups]

Allen,

Thank you for the clarification. Two questions remain:

I thought Orders and Contacts were related many-to-many, though not
directly. Both Orders and Contacts relate one-to-many to
RelOrdersContacts. Is that a real many-to-many relationship? The
Access Relationship window can apparently directly relate two tables
many-to-many, but what table layout is needed?

I will use the junction table as record source for the subform, which
contains a single combo box to select a contact. But can I have a
continuous sub-subform to display all Contacts fields? I would like
the Orders form and Contacts form both to be visible at once, not
always opening and closing the Contacts form. Subform relates junction
to Orders and sub-subform relates Contacts to junction. Is it worth
trying?

Thank you for your continued help.

 

[Allen Browne]

You cannot create a many-to-many relationship - at least not with any kind
of meaningful integrity. By creating the junction table, you end up with 2
one-to-many relations. That is the standard way to solve the many-to-many
problem.

It is possible to put the Contacts form into another subform on the Orders
form, in addition to having the junction subform. In general, though, this
one-form-fits-everything-in-the-database results in a very top heavy
interface that is slow to load and not particularly clear to navigate. I
think that point becomes clearer as you try to add other forms as well.

 

[cjg.groups]

Allen,

Thank you for the definitive reply. I will use your combo box
suggestion.

I appreciate your help and your time. MVPs are such a life saver!