Many to many relationship forms

[Tree]

I have a Physicians table (name, address, etc.) and a Hospitals table (similar fields) in a many to many relationship via a junction table containing the primary key from each of these tables. Each physician can work at multiple hospitals, and each hospital can have multiple physicians.



I have built a form for entering new records for Physicians. I would like to include a subform in which the user can (1) select all the associated hospitals for that physician from a drop-down list of all hospitals in the hospital table, or (2) enter a new hospital name that creates a new hospital record.

I have created a subform from the junction table that enables me to enter in the AutoNumber associated with a hospital record, but then the user has to know the hospital's AutoNumber, instead of simply selecting a hospital name from a list.

[rpeare]

You can create a combo box where the BOUND column is the hospital ID, and the second column is the hospital name. When you display the combo box if you set the BOUND column (typically the first column) to 0 and the second column to something wider than 0, when the person does data entry they will only see the hospital name and they can type it in as normal but the value that is stored will be the hospital ID.

Adding a new hospital if it does not exist in your list is a bit more complex normally I provide a button that would, for instance, say 'add hospital' it would open up a hospital editing screen to allow the user to put something in, then when the form closes I would force a refresh of the data and requery the combo box that contains the hospital names.

[Tree]

I understand your suggestion but do not know how to do it.

The subform is connected to the junction table, where the HospitalID resides, but the Hospital name is in the Hospital table. How do I create a two-column combo box in the subform that is connected to both the junction table and the Hospital table?

[ssanfu]

.... When you display the combo box if you set the BOUND column (typically the first column) to 0 and the second column to something wider than 0, ........

Shouldn't this be:

...When you display the combo box if you set the BOUND column (typically the first column) width to 0 and the second column width to something wider than 0,.....

[rpeare]

Can you post a sample database it'd be far easier to modify yours than write one from scratch. Just make sure there's no personal information in the database, then zip it and post it on this forum.