Multi-Select Combo Box With a Junction Table (1 Viewer)

[B99]

I have a form that I want to use a multi-select combo box with a junction table as its control source. Let's say I have a table to store people and a table to store hobbies, and a junction table with FK_people and FK_hobby. I want to be able to enter a record for a person and select zero to many hobbies for that person from the combo box and store the resulting records in the junction table.

I've seen several posts on the Internet that say this isn't possible but I have a hard time believing that. If I were to change the DB structure so that the hobbies were a multi-value field on the people table, then it would be very easy to do. And from what I've read, multi-value fields are a front-end cover for a hidden junction table. One option I read was to use a subform, but I'd like to avoid that if I can. Another option I thought about was making the combo box unbound and using VBA to select/insert/update the hobbies when scrolling through people records. I'm not very strong with VBA and would like to limit the coding.

Is there a (somewhat) easy way to do this?

 

[theDBguy]

Hi. Check out this demo at UA.

UtterAccess.com

www.utteraccess.com

 

[plog]

Generally you enter data into a junction table via a subform. The main form holds person info, then the subform is a continous form based on the junction table with a combo box. User selects a value, then can go to the next line of the subform to select another.

 

[B99]

Thanks for the quick responses!

DBGuy, that's an interesting post and a neat way to make the subform look like a multi-select combo box.

If I understood the intent correctly, the poster was not opposed to using MVF, and his workaround was for pre-A2007 (I'm using 2016). I've seen mixed reviews on using MVF with most experienced developers saying to avoid them at all costs because of upscaling issues and seemingly bad DB design (hidden tables notwithstanding). But if I'm not worried about upscaling, is there really a downside to moving the hobbies to a MVF? The implementation is much cleaner (no code).

 

[theDBguy]

Thanks for the quick responses!

DBGuy, that's an interesting post and a neat way to make the subform look like a multi-select combo box.

If I understood the intent correctly, the poster was not opposed to using MVF, and his workaround was for pre-A2007 (I'm using 2016). I've seen mixed reviews on using MVF with most experienced developers saying to avoid them at all costs because of upscaling issues and seemingly bad DB design (hidden tables notwithstanding). But if I'm not worried about upscaling, is there really a downside to moving the hobbies to a MVF? The implementation is much cleaner (no code).

Well, if you really like to use MVFs, then check out this other demo from the same developer.

UtterAccess.com

www.utteraccess.com

 

[MajP]

I've seen several posts on the Internet that say this isn't possible but I have a hard time believing that.

Concur. Not sure what amatuers said that.
I just did one yesterday far harder than what you are suggesting.

I'd like to create a form with several checkboxes, but I'm not sure how to structure the tables to capture the checked items

1) Are you saying this is just a personal DB? You are not tracking multiple people in the database? If so you need to relate daily tasks to a person. 2) I doubt you need a creation date table. That is just a field in another table. 3) As far as tbl levels I think that is (min,plus, and elite)...

www.access-programmers.co.uk

Here is one even harder, but the idea is easy. Yours would be a piece of cake. In the one below. The OP wanted a way to assign artists to specific tracks, with specific intruments. You pick the album and it brings up the tracks. You pick the artist and instrument. Then click on a track to assign that artist/instrument. So yes yours can be done easily. FYI that list on the left is a continuous form.

 

[B99]

Thanks for the help and suggestions; you guys are helping me learn a lot!

I think I'll try the MVF route again. I really like the look and usability of a combo box in this instance and if it works, it's a very quick process. It worked for data entry on a small test that I did; hopefully I won't run into any issues down the road when querying.

 

[theDBguy]

Thanks for the help and suggestions; you guys are helping me learn a lot!

I think I'll try the MVF route again. I really like the look and usability of a combo box in this instance and if it works, it's a very quick process. It worked for data entry on a small test that I did; hopefully I won't run into any issues down the road when querying.

Okay, good luck! Let us know how it goes.