Simple form for Many to many relationship (2 Viewers)

[OregonCoder]

Hello,
I am trying to create a simple form would look like the 2nd attached Image. Only the form would allow the user to select a new name for each "Job Title" Using a Combo Box. The query was created using the Multiple to Multiple relationship in the first image. I am hoping for something simple for the end user.

Thanks in advance

 

[CJ_London]

Forms don’t work that way - basic rule is one form, one table

As constructed you have a many to many relationship, I.e. one member can have many job titles and one job title can have many members- is that what you want? Because that is not what you are describing. If so your joining table probably needs more fields (such as start/end dates)

what you are describing does not require the joining table, just a foreign key to the member in the job title table

and your form would be based on the job title table using a combo to select the required member- if members cant have more than one job title you would need to modify the combo rowsource to exclude members already selected

or you can do it the other way round - include a foreign key to the job title in the members table

or perhaps it is a many to many relationship and you need to provide some more detail

 

[MajP]

Echoing what @CJ_London already said.

You can have two versions of this. A main form that you select a job title and then a subform to add names. And or a main form with Names and then a subform to select job titles. I like to make a single form with some code so I can switch between the two

Here is an example of the first form, but the reverse is almost identical in design
1. I did not use a bound main form instead I used the combo box that pulls Titles. However, I could bind the main form to the titles table instead
2. The subform is linked to the main form by the titleID and the titleID foreign key in the subform.
The subform in this case is bound only to the junction table
3. A combo lets you then store a member id into the junction table

This is the general tricks regardless of how you vary it. A junction table will have two foreign keys (memberID, titleID). One of those keys is the link to the main form so it gets populated automatically by the subform. The other foreign key usually gets populated by the combobox pull down in the subform.

Try to repeat what I did and build the same thing reversed. Have a combo to pick a member and then a subform to select the Titles. If you can do thiat you can then put buttons on both forms to switch back and forth between forms. Allowing the user to select members and enter titles or select titles and enter members.

I would not try to allow adding members or titles here. This form then gets complicated. I would have pop up forms to add members, and a pop up form to add titles. You can do that combined but that gets confusing and complicated.

 

[June7]

If you want only one form, it would be bound to junction table with comboboxes to select both title and person.

You want to allow person to have multiple job titles - at same time as shown in your example? If a person can have only one title then that is a 1 to many relationship - unless person can change titles over time and you care about that history. Or if each title can associate with only one person, same concept.

 

[Pat Hartman]

Here's another example. It shows how the view from either side can be different. In one case it is a subform and in the other, it is a popup.

PS, Best practice is to enforce RI on your relationships.

 

[GPGeorge]

Here's yet another sample illustrating the appropriate approach.

And, you will find other examples in the Northwest Starter Edition.

 

[cheekybuddha]

And, you will find other examples in the Northwest Starter Edition.

@GPGeorge, it's that the Northwind by Northwest starter edition?

 

[GPGeorge]

This is a day for me not to be writing stuff online or in email. This one isn't even the worst.