Linking Single Values from Multiple Combo Boxes with ID Control Source to Single Foreign ID

[cgoss]

I have a many to many relationship database in Access 2016 between books and genres as an example case. The problem I am running into on my Book Form - Genre Subform setup is in trying to use multiple genre combo boxes (let's say 3) that have the genreID control source to have a different selection in each (e.g. combo box 1 select Fiction, combo box 2 select Fantasy, combo box 3 select Romance). Is there a way to have that set up where the genreIDs selected can relate back to a single bookID?



Also, I have been able to somewhat do this with a multi valued field, but I would really like to avoid using that if at all possible.

I have attached pictures for some reference.

[Welshgasman]

You would just have multiple records in Book Genre for each genre. just like your second picture?

[cgoss]

Yes, the second picture is somewhat of another solution, but it just looks pretty clunky to me, and it only functions like that if I have it in datasheet view. I wanted to see if I would be able to have a setup like the first picture in form view but where the individual combo boxes can have different selections like the second picture.

[Welshgasman]

The problem with the first picture is, what if you have 4 genre for a book?
With the second picture, that is not an issue.
You could concatenate all the genre into a textbox control so would have "Romance, Fantasy, Fiction", but would still have that structure in picture 2.
If you need to add a 4th Genre, that would be another record.

[cgoss]

The book-genre database is just an example. The database I am trying to do this in is an estimators-estimate database where - at most- only 3 people collaborate on 1 estimate. So that's why I am not necessarily trying to have potential unlimited quantity of genres - but I do see that you generally would want that if you don't have a constraint on quantity.

[orange]

I agree with Welshgasman-- structure something like

Estimate--->EstimateCollaborator<---People/Collaborators

You can add logic to constrain the number of collaborators per estimate.

[madpiet]

Yup. If you wanted to limit the number of collaborating estimators, you'd put code in the BeforeInsert event of EstimateCollaborator to check for the current number of collaborators and reject the insert if you're already at maximum. (or set the AllowAdditions property of the subform to false when the number is reached).

[cgoss]

Well, the main thing isn't the constraint - I agree I could limit that through some code, but the constraint itself isn't needed for this. I am moreso trying to have 3 individual combo boxes that can each choose a different Analyst/Collaborator and update the Junction Table for each. I.e. I would like to be able to use these three combo boxes to add 3 new records relating the respective AnalystIDs to 1 EstimateID in the Junction Table (at least I think that is what I am trying to do). Currently, what I have is in the attached picture.

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications.

[Welshgasman]

So cycle through your combos.
If they have a value use either sql to add a record or a recordset. The choice is up to you.

Now how you get them back for display the next time will be a little more complicated, though a reverse of the procedure.

Then you have the issue if you retrieve the three records and decide to amend one of them for a new value. That will add some complication as well.

I wish you well with your structure.

[madpiet]

Why not do something sensible like remove the "used" items from the combobox for the given record, so a user can't add the same analyst 3 times? Or just create a unique index on EstimateCollaborator table on (EstimatorID, EstimateID). You're making a really simple thing waaaaay more complicated than it needs to be. Maybe read Allen Browne's articles on indexing in Access.

• Indexed: To prevent duplicates in a field, set this property to Yes (No Duplicates). Using the Indexes box in table design, you can create a multi-field unique index to the values are unique across a combination of fields.