Multiple selection from List Box isn't saved in current field/record

[Aya via AccessMonster.com]

Hi. I've created a form named Job Safety Analysis which analyzes all the
steps in a task to prevent risks at an working area. It was working properly
and I've added an List Box named Equipment to the form.

I wanted it to allow multiple selection of security equipment like helmet,
masks, gloves, goggles, etc; in case the user wants to select two or more
equipments (masks and goggles, helmet and gloves, masks and gloves, etc). The
list box value is going to be saved in the Equipment field I've created in my
Security table and I want a multiple selection to appear in that field.

In the Property of the list box, I've selected in the Others tab, the Simple
option of the Multi Select, so I can select more than one option. I run the
form and selected the record which I wanted to have two equipments, it
selects them. But when I save it, it seems to doesn't save my multiple
selection in the table record which I've assigned for it to save. And I open
the report, to see if the multiple selection is working properly, the field
appears blank with no value.

I'm trying to create an Expression Builder in an textbox to combine the
selected values in the list box and show it in the text box. The expression
that I was planning was like this: have the selected values in the list box
and show it in the text box as a single value (Select1, Select2) That's means
that I want it to appears the first selection, then a comma and the second
selection. And continue this until the user chose the equipment he/she needed
for the task analyzed.

Then save the textbox as the value in the Equipment field of the Security
table.

I will appreciate if anyone can help me with this problem. Thanx.

Lima

 

[Douglas J. Steele]

What you're trying to do is strongly discouraged: it's actually a violation
of database normalization principles to store more than one piece of
information in a single field. The appropriate way to do what you're trying
to do is to use 2 separate tables: one to hold all of the information
currently in the table, and the second to hold one row for each item
selected in the list box, linked back to the entry in the first table.

It's for this reason that you cannot bind a multi-select listbox to a field
in the form's recordsource.

If you're determined to do it, the code to return a comma-separated list of
all of the items selected in the list box would be something like:

Dim strSelected As String
Dim varItem As Variant

strSelected = ""

For Each varItem in Me.MyListBox.ItemsSelected
strSelected = strSelected & _
Me.MyListBox.ItemData(varItem) & ", "
Next varItem

If Len(strSelected) > 0 Then
strSelected = Left$(strSelected, Len(strSelected) - 2)
End If