Establishing new Records in a Many to Many Relationship

[JWalcott]

I have developed a many to many relationship between two tables by creating a third table. This third table allows duplicates from both of the original tables, as this was the intention of the junction table. So that's fine and dandy, but now creating the forms for users to view and add data I am having a problem!
Form contains Clients, subform contains contacts for the selected client and the hidden junction id which connects them. So, let's say user wants to add a new contact for the active client. The contacts are not unique to the clients, in other words this contact may already exist in the contacts table.
Plan was for a combo box on the subform to see if this new contact already exists. The subform does not allow for the combo box to "Find a record on my form based on the value I selected in my combo box". Therefore the subform does not fill with the data of the selected record in the combo box.
The goal is for the user to search existing contacts, if the new contact for the particular client does already exist, just select the contact from the drop down, the subform will fill with the contacts already existing info, click save and a new record has been established in the junction table with the active client and contact.

Junction ID :: Client :: Contact
1092 :: 2 :: 354
1093 :: 3 :: 355
1094 :: 3 :: 641
1095 :: 3 :: 220
1096 :: 4 :: 264
1097 :: 4 :: 220

Say, user is viewing client 4 data, and needed to add 220 into the list of contacts. 220 already exists, just need a new record in the junction table.

 

[SydneyGeek]

To clarify, you have Clients, Contacts, and the join table, Junction, right? If so, the links would be Clients::Junction via ClientID, and Contacts::Junction via ContactID. Junction can have its own ID if you like, or be set up as a compound key using both ClientID and ContactID. If you do the compound key, you will not be able to create duplicates of the same Contact / Client combination. If the above is not clear, give a yell.
Anyway, to the form. For data entry, base the form on Junction. It will need 2 combos -- the first based on ClientID from the Clients table, the second based on ContactID from the Contacts table. Both of these can be set up using the Wizard. Be sure to hide the key field.
To see all Contacts for a Client, you can switch to the first form you mentioned. Again, in Design view, drag a button onto the form with the Wizards switched on. From the Form actions, pick the one that lets you open another form and display specific data. Pick the form that you want to open, use the ClientID field as the match field, and you're set.

Denis

 

[Swamp Thing]

Suppose the combination "client=2, contact=255" occurs three times in the junction table. What does this mean? Is there any attribute that distinguishes the three occurances from each other? If not, perhaps you can just have an extra column that contains a count. Whenever you want to add a similar contact, just increment the count value for 2::255.
So if you want to represent the pair 2:255 occcuring five times, you will set the count field to 5. Of course, in this case you will have only one row corresponding to 2::255 (a candidate for a primary key!).

 

[SydneyGeek]

Another option -- in the subform, make ContactID a Combo. It can be in Datasheet view, but when you add a new contact using the combo, you should get all the details flowing into the form. When you link the form with the subform, it's the ContactID field that should be the link.

Denis

 

[JWalcott]

Thanks for the suggestions. I created a new form for establishing a new connection between an existing client and an existing contact. This form is based upon the Junction table. The user accesses this form after checking the contents of a combo box displaying existing contacts.
The Junction table has a unique id, as there is a possibility of duplicate contacts for the same client. A new field has been added to the junction table which clarifies the type of contact.
Anyway, it works.
Thanks again.