Many times when helping someone you’ll discover one or more tables in a database that contain multiple Yes/No fields. You can pretty much count on this meaning the database is not normalized. The usual dialog when the OP (original poster) is told they need to fix their structure is something like:

OP: “But I want to use check boxes on my forms!”

Responder: “That’s fine, but you still need to fix your structure. The easiest way to do this is with a continuous subform with combo boxes from which the user makes the selections.”

OP: “My users need to use checkboxes, not make selections from combo boxes!”

Responder: “Ok. That’s completely possible, but it takes some VBA code to accomplish.”

OP: “I don’t know anything about code. Having different fields is much easier. I don’t need to change anything.”

Responder: “What happens when you have to add another checkbox field? You’ll have to change the table and every query, form and report that uses that table if you add another field. What if you need to filter for only those persons with two of the items selected? It’s much easier to query one field than to work out criteria for 20 fields in one table.”

It boils down to the fact that how you want forms to look and how they interact with the end user has ABSOLUTELY NOTHING to do with normalizing the database. You can still use checkboxes, or a variation on that theme (like a multi-select listbox) with a normalized database.

The attachment demo’s three different methods of doing so:

1)frmPeopleUsingMultiSelectListbox uses a multi-select listbox and VBA code
2)frmPeopleUsingTempTableAndCheckBoxes uses a subform bound to a temporary table along with VBA code to append and remove choices
3)frmPeopleUsingUnboundCheckboxes uses multiple unbound checkboxes that are ‘filled’ at run time to the correct items from the available list and then VBA to append and remove selections as needed

I was trying to implement the feature from frmPeopleUsingMultiSelectListbox, and I noticed a problem.

Here is one way to replicate it:

1. Open frmPeopleUsingMultiSelectListbox
2. Move to the second record (Barney Rubble).
3. Select Alabama and Delaware.
4. Close the form and reopen.
5. Delaware is no longer an option for any person (not even for Barney!).

Alabama is still an option, presumably because it had already been selected (for Fred).

It appears that data re getting correctly inserted into tblPeopleStates, so I suspect the problem has to do with the OnCurrent code or row source for the list box. Any ideas?

The fix is simple:

Change the row source for the unbound listbox to:

SELECT tblStates.StateID, tblStates.StateName FROM tblStates;

There is no need for including the tblPeopleStates, nor any WHERE criteria with PeopleID. Just let everyone have the full list of States in the listbox, and the OnCurrent code does the work or reselecting the States that are assigned to them.