Multi-select List Box

Question

Hi, 

I have a many-to-many relationship within my Inventory database between the tblMasterInventory and the tblReceivedTransmittals with a junction table tblInvToTransmittals. I have created a form for each of these without problem. However, since there can be 100+ inventory items attached to one transmittal it would be nice to be able to select all of these items from the list box at once and then the transmittal number from its dropdown box in one go. Rather than each individiual item at a time.

Questions

#1 Is there an easier way to do this? Filling out this junction table automatically!?!? A button/macro/setting or something I'm not thinking about?

#2 If not, how do I set the inventory list box to multi select?

#3 When I do multiple inventory selections will it save each item as a single record in the junction table? My brain is telling me it won't work that way. In theory it seems like it would want to save them all under the same record and throw an error message at me. UNLESS, something I just thought of, there was some code behind it that said 'each list box selection = new record in table'!?!?

Suggestions?

Thanks!

Answer 1

The usual way of populating a junction table is using a form with a subform - no code needed.

But it is possible to use a multi-select list box instead. It does require code, both to add records to the junction table and to remove records from it, and to update the list box as the user moves from record to record in the main form. You'll find an example (zipped) in https://www.dropbox.com/s/9c3buoy9wmrc7o9/Enrollment3.zip?dl=1

Answer 2

It is possible to use an unbound multi-select list box, provided that there are no non-key attributes of the relationship type to be entered.  It does require code in the form's module to read data from the table which models the relationship type and select the relevant items in the list box, and conversely, to loop through the list box's ItemsSelected collection and insert into or delete rows from the table, the latter when an item in the list box is deselected.

You'll find an example of such a list box in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

However, note that it is not possible to assign a value to the Status attribute of the relationship type in this way, as is done in the conventional interface of a form/subform.  Even if there are no non-key attributes of the relationship type, unless there are very cogent reasons for using a list box I would recommend the use of the conventional form/subform interface, which is code free and very easy to implement.

Answer 3

I'll keep playing around with the listbox for future use, but I think for now I will just stick with a junction table in the subform. I've got it working to where you just select from the dropdown list with no code involved. 

Thanks for your help on this!