Storing Data in a table from a List Box

[twoolfolk]

I am very new to access. I have created a form with list
boxes that pop up when No is selected in an option group
containing Yes, No, and N/A. I can get the table to
store the data from the list box only if the MultiSelect
Property is None. When I set the MultiSelect Property to
Simple, it allows the user to select more than one
option, but will not store the data in the corresponding
field in the table. Can you help me to get it to save
all the data selected? Please be very specific in your
details.

 

[Guest]

This requires some coding. Hopefully you have familirazed yourself with Access VBA. In your properties for your list box, there is an "After Update" option. In that property you start an event procedure which will allow you to run code. In this case the code will run after each change you make when you are clicking your selections.

The best way to test this is make a text box and put it in the form next to your list box. Make the text box pretty wide and long so you can see it working.

1. Make a text box next to your list box and name it, "txtSelected" (without the quotes)
2. Put the code below into the "After Update" event procedure for the list box
3. In the line "With Me!nameoflistbox" replace the nameoflistbox with the actual name of your list box
4. Compile the code, and test it out!

In my example below, It puts each line selected on a separate line in my text box. If you don't want a new line for each record selected, take out the "& vbCrLf" in line 8 below. This forces a carriage return and line feed after each selected record.
-------------------------------
Dim varItem As Variant
Dim strList As String

With Me!nameoflistbox
If .MultiSelect = 0 Then
Me!txtSelected = .VALUE

Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & vbCrLf
Next varItem

Me!txtSelected = strList

End If
End With
---------------------------------

 

[twoolfolk]

Thank you for such a detailed reply. I do not have
experience in Access Vba code, but this did help. I have
tested it and it worked for the text box. When I used
the same code in the After Update for my list box, and
then went to the form and made two selections, it is
giving me an error message that reads, Microsoft Access
can't find the field 'txtselected' referred to in your
expression. You may have misspelled the field name, or
the field may have been renamed or deleted. the line
that it is referring to in the code is the one that reads

Me!txtSelected = strList

Can you help me to fix this? Also, where in the table
will this information be saved?

Thanks,

-----Original Message-----
This requires some coding. Hopefully you have

familirazed yourself with Access VBA. In your properties
for your list box, there is an "After Update" option. In
that property you start an event procedure which will
allow you to run code. In this case the code will run
after each change you make when you are clicking your
selections.

The best way to test this is make a text box and put it

in the form next to your list box. Make the text box
pretty wide and long so you can see it working.

1. Make a text box next to your list box and name

it, "txtSelected" (without the quotes)

2. Put the code below into the "After Update" event procedure for the list box
3. In the line "With Me!nameoflistbox" replace the

nameoflistbox with the actual name of your list box

4. Compile the code, and test it out!

In my example below, It puts each line selected on a

separate line in my text box. If you don't want a new
line for each record selected, take out the "& vbCrLf" in
line 8 below. This forces a carriage return and line
feed after each selected record.