Form for many-to-many relationships

[sharkey_lsu]

I have two many to many relationships between three tables. These tables are named Clients, Operators, and Platforms. I have two junction tables; JctClientOperator and JctOperatorPlatform. I am able to establish the relationships but I am not sure how to create a form with the functionality that I need. Please reference the attached image for my relationship diagram.



The first form I need to create will be named ClientDetails. This form needs to allow the end user to not only input the client details but also select one or more operators that are associated with this client.

The next form will be named OperatorDetails. As before this form needs to allow data input, but also the ability to select multiple clients as well as multiple platforms. If a client is inputed that is not present in the database, dialog will lead the EU to create a new client before returning to complete the new operator.

Lastly (for now) I need to create a form named PlatformDetails. This form also needs the functionality to input platform details. It also needs to allow the EU to select multiple Operators.

I appreciate any help that is able to be provided. I have also attached a zipped copy of what I have developed so far and would love additional advice/comment on what I have created so far.

Database Download: Inspection System.zip

[June7]

Options for data entry form with many-to-many relationship, using client and operators as example:

1. one form with two comboboxes to select client and operator

2. main form bound to Clients and subform bound to JctClientOperators with combobox to select operator

3. main form bound to Operators and subform bound to JctClientOperators with combobox to select client

[sharkey_lsu]

Options for data entry form with many-to-many relationship, using client and operators as example:

1. one form with two comboboxes to select client and operator

2. main form bound to Clients and subform bound to JctClientOperators with combobox to select operator

3. main form bound to Operators and subform bound to JctClientOperators with combobox to select client

Thanks for the quick reply but I still do not understand how any of these options will allow me to select multiple values within the combobox. Is there an example that you might know of with similar functionality?

[June7]

You would not select multiple values, you would select one value for each record. Selecting multiple values would mean setting up a multi-value field in table. I NEVER use multi-value fields.

[sharkey_lsu]

I do not want to and also would never use multi-value fields. What I need is an intuitive way for the end user to select more than one operator for a client. The current method is that the EU would need to know the Autonumber ID associated with the operator to be able to type it into the JctClientOperators Table that is on the current form. This works but is in no way easy for an EU to use. Would you have a moment to open the sample I attached and see exactly what I'm talking about?

[June7]

User does not need to know and really should not even be aware of the autonumber ID. Use a multi-column combobox. I see that you are already familiar with multi-column combobox.

Don't use a table as the SourceObject of a subform container, use a form - unless you setup the combobox in the table on the Lookup tab. However, I do not recommend setting lookups in table, especially if alias is involved.