Storing Multiple Selections From A List Box

Although versions of Access after 2007 support multivalue fields and provide some mechanisms for maintaining them, they cause a number of problems both in displaying them and in querying them. They break the principle of relational database design that a single field (column) holds only one datum, and so are inherently difficult to work with in a relational database application like Access.

The best way to store multiple items in a single field is not to do it at all. Instead, use multiple records in a related table to represent these items. There are several ways to present these records on a form in a way that users find easy to work with.

The obvious way to present multiple related items is to use a subform. An Access subform is designed to display and edit multiple records from a related table (these records being related to the record currently displayed on the main form), and does it with no code at all. However, the subform approach is a bit cumbersome to work with when all you want to do is let the user quickly choose multiple items from a list.

For small "pick-lists" I prefer the multiselect list box. However, list boxes in multiselect mode can't be bound directly to the data, so you need to use code to read the related records for each new main record and select the appropriate items in the list box. Then whenever the list box is updated you need to use code to update the set of records in the related table.

Here's code from a sample form that represents "family members", with a list box named "lstHobbies" that represents, for each family member, that person's hobbies from the list of all possible hobbies. As you can see, this will involve tables called "tblFamilyMembers" (all family members), "tblHobbies" (all the hobbies that anyone could have), and "tblFamilyMembersHobbies" (each record linking one family member to one hobby). The list box shows all hobbies, with the selected items in the list being the hobbies held by the current family member.

code: click in the frame, Select All, then Paste into your code editor

As you see, there's a fair amount of code involved, because we're using the list box to do something it wasn't built to do, but it works quite nicely.