Problem with multi-select in a list

[amit]

Hi,

I'm having some trouble conceptualizing and implementing a
multi-select list.

Do I need to have a field corresponding to all the items
on a list in a table, and then store the ID in those
fields if selected from the list? How is this implemented?
I've tried using list-wizard, but I couldn't figure out
how to store multiple items. I'm aware of the mult-select
property in the List properties (which I set to extended),
but what after that?

As an example, I have a table called 'tblPerson', with
PersonID (PK), PersonFirstName and PersonLastName. I have
another table called 'tblColors' which has ColorID(PK) and
ColorName. One person can select 0 or more colors. There
are 6 colors in the table.

Do I need to add 6 fields in tblPerson (or in a separate
table called tblPersonColor), corresponding to the maximum
number of colors that can be selected? What happens if
more colors are added to tblColors in future?

Thanks for your help!!

-Amit

 

[ScottE]

You have a classic one-to-many relationship. The standard
way to handle this is to design one table as you have it,
then a second table with two fields: the PersonID and the
color. In Tools/Relationships, drag PersonID from the
main table to PersonID in the color table. In the window
that pops up, select "enforce referential integrity."

Then set your listbox to work with the color table.

To programaticcaly iterate through the items selected in a
listbox, try:

Dim lst as Listbox
Dim lstItems as variant

set lst = [{yourlistbox}]

For Each lstItem In lst.ItemsSelected
{whatever you want to do with them}
Next lstItem

Hope this helps!