Multiple Selections in a list box

A

Allan S. Warrior

Can anyone give me some tips on how to structure the data and build the
control to essentially replicate what Microsoft has done in Outlook with the
Categories field? I love the way I can put an appointment or contact in
multiple categories using a single control, and can then display each item
under the unique category headers at the display level.

I'm guessing there's some work to do using a junction table to resolve the
many-many relationship between items and categories, and some specific
settings I need to use on the list box to display the available categories
and allow multiple selection, which I assume would populate the junction
table. Would this control be part of a subform based on the junction table
or the category table? This is where I become hopelessly lost.

If I can master this, there's a ton of ways I can use it, from assigning
employees to projects, categorizing library items, and, on a less important
note, organizing my music collection.

Thanks in advance for any advice.
 
A

Allen Browne

Yes, you need a junction table to resolve the many-to-many.

Set the Multi Select property of the list box to Simple or Extended.
Leave the Control Source property blank. (You cannot bind it to a control
effectively.)

Use the Current event of the form to read the matching items from the
related table, and mark them as selected.

Use the Click event of the list box to dirty the record in some way (e.g. by
assigning the existing value of a bound control to itself) so the
BeforeUpdate event of the form will fire.

Use Form_BeforeUpdate to loop through the ItemsSelected collection of the
list box, removing any related records directly from the junction table if
they are no longer selected, and appending any newly selected ones.

Use the Undo event of the form to reset the selection to the previously
saved choices (by calling Form_Current.)

If that sounds too daunting or inefficient, you could just create a subform
with a combo where the user chooses as many choices as apply, one per line,
and no code is needed.
 
A

Allan S. Warrior

Thank you so much for your advice. This is exactly the type of thing I need
to learn how to do, and it sounds within my abilities if I get to work on it.
I kept trying to bind the control to a field and it was just having none of
that. Thanks again for the instructions.
 
Top