junction-table subform combo-boxes

[Olga W]

These keep throwing curve balls at me.

I have a form in which I want to display the intermediaries (secretaries, PA's, receptionists etcetera) of our contacts.
Because some of these secretaries are important enough to us to count as contacts themselves, they are stored in our database as contacts in their own right.
And because it's possible that one person might act as an intermediary for many contacts, and one contact might have a few intermediaries, the relationship here is many to many.
So ... the relationship has to be stored with a junction table, and displayed on forms with a subform.

Rather than link the contact table straight back to itself through the junction table, I opted to create an extra link in the loop - building a table just for intermediaries, which uses the contact table ID field as a foreign key.


I've been building the subform with fields from the junction table. That worked fine.

I tried to proceed from there by making the subform's intermediaryID field a combo box - that pulled up the names of the people in the intermediary table rather than their ID numbers. This used the contact table[where the names are stored] and the intermediary table[which selects out the intermediaries] in the row source.

This hasn't worked. After I select an intermediary, when I click on something else I'm told that I "cannot add or change a record because a related one is required in the intermediaries table". The names I've selected belong to people who DO have records in the intermediaries table. My assumption is that the problem is that the link to the name is too indirect, since it isn't in the intermediaries table, but in the related contacts table. [The problem is the same if I take intermediaries out of the row source. - which has caused me troubles in the past]

I am avoiding the solution of putting the combo box in the intermediary table, and then pulling this up directly from the form.

Any ideas?

 

[SydneyGeek]

Try this instead: have only One Contact able and don't bother with the Intermediary one.
Keep the join table and set it up like this:

In relationship view add the Contact table twice, and join ContactID from Contact to ContactID in the join table. Now connect ContactID from contact_1 to the IntermediaryID in the join table.

Create a new subform using the join table; set it up as Continuous records or as Datasheet, whichever you prefer.
In design view on the Contacts form, drag and drop the subform onto the surface of the main form.
IMMEDIATELY go to the properties, select the Data tab and check the Master and Child fields. It will be showing 2 fields separated by a semi-colon on each row. Delete the semi-colon and following field from each row, then check out the data.

This layout will treat every person in your Contacts table like a first-level contact, and you will be able to add as many intermediaries as you like. You can always set up a combo in the form's header to search for the desired contacts.

Denis