Multiple Selection List Box

[NoviceNana]

Hi Everyone,

I'm brand new to the group. I have a question about Multiple Selection
List Boxes. I did a search in the archives, but found quite a few
topics about Multi-select list boxes, but none of them answered my,
very basic, question.

I want to create a list box for a "Race" field on my form that allows
the user to choose more than one race category. I have the Race field
in my main table that is bound to another table with the 5 racial
categories. I created a list box on my form which contains the 5
linked racial categories and selected Extended in the Multiple
Selection section of the list box properties.

By doing this I was allowed to select multiple races in the list box,
but the information was not saved in the underlying table. From
looking through the archives in this group I now know that there is
some underlying programming that needs to occur, but I couldn't find a
topic that specifically addresses what the underlying programming is...
So basically I just need to know the proper steps to make a basic, yet
functional, list box..

any help would be GREATLY appreciated!!
Nana

 

[Douglas J. Steele]

To be blunt, the reason it didn't work is because it's a bad idea. Trying to
store more than one value in a single field is a violation of database
normalization principles, so Access doesn't support it.

What you've got is a typical many-to-many relationship: one individual can
be associated with more than one race, and each race can be associated with
more than one individual. To implement many-to-many relationships, you
introduce a third "intersection" table that's related to both of the other
tables. You then store one row in that intersection table for each valid
combination of individual and race. If one individual is associated with 3
races, you'd put 3 rows in the intersection table.

Typically you'd use a subform to handle the relationship.

If you look in the Northwind Traders database that came with your version of
Access, the three tables Products, Order Details and Orders illustrate what
I'm talking about. There's a many-to-many relationship between Products and
Orders (Each Product can appear on more than one Order, each Order can
contain more than one Product), so Order Details resolves this many-to-many.
The forms Orders and Orders Subform shows how to handle updating the
records.

 

[Guest]

I did a database about 5 years ago that has exacly that same requirement.
What Douglas is recommending is exacltly correct.
The only difference is, since it was a federally mandated system, the race
codes had to be specific values, so we used a Value List in a combo for our
race codes. The race subform was bound to a ClientRace table.
tblClientRace
Client_ID - fk to tblClient
Race_Code - from the combo.

 

[Douglas J. Steele]

Not sure why you felt you had to use a Value List in the combo just because
the race codes were mandated.

Putting them in a table would still make sense, so that you could join them
to the table to get descriptions for reports.

 

[Guest]

No reports used that field. It was only used in electronic transmissions to
the state and federal governments. The transmission had to be in DBase
format that was a flat file with a different field for each race code.
The value list included a description column for user convenience.