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

 

[Guest]

The problem you are experiencing is incorrect use of a multi select listbox.
A multi select list box will not return a value on it's own. It stores the
selections in a collection property named ItemsSelected. There is a good
example in VBA Help under ItemsSelected that shows how to loop through the
selections. To do what you want, you will have to loop through the
selections, concatenate them, and store them in a text box that is bound to
the field in your table where you want to store them. If you have the list
box bound to that field now, you will need to change it. To do this, you
will not be able to use the expression builder. This will take some VBA
coding.

The real issue here is that you should not be storing multiple values in one
field. What you should have is a child table that has a record for each
equipment item selected for the parent table.