Multiple select listbox, concatenate to single field in table

AccessIdiot

Apologies if this has been answered before - I searched but couldn't find anything.

I have a listbox that is populated with a query. I need to enable the user to select multiple items (simply set listbox Multi Select property to Simple yes?) and then I'd like to commit the choices to a single text field in a table, concatenated and separated by commas.

So in other words if the listbox has these values:

BRN
CM
XD
RG
RTD
WA
WE

I need the user to be able to choose (for example)

BRN
XD
WE

and in the table you'd see

BRN, XD, WE

Please keep in mind that I am not only an Access Idiot but also a VB Illiterate. :-) I can understand code but not write it myself and I don't always know where to put stuff so please be very detailed if you know how to help me!

Cheers,
Melissa

AccessIdiot

OK, maybe I'm not as dumb as I thought. Turns out the answer was already out there and with a little tweaking I got it to work.

For anyone else struggling:

1) make sure your listbox has multi select set to simple (on the "other" tab in the control properties).

2) on the events tab in the On Click property add the following code:

Expand|Select|Wrap|Line Numbers

 
Private Sub ListBoxName_Click()
 
Dim SelectedValues As String

Dim frm As Form

Dim varItem As Variant

Dim lstItems As Control

Set lstItems = Me!ListBoxName
 
For Each varItem In lstItems.ItemsSelected

    If SelectedValues > "" Then

        SelectedValues = SelectedValues & ", " & lstItems.ItemData(varItem)

    Else

        SelectedValues = lstItems.ItemData(varItem)

    End If

Next varItem

Me!FieldInTableToPopulate = SelectedValues
 
End Sub

Make sure you change the items in bold above to match your project.

*edit* bold doesn't seem to work in the code container so be sure to change:

ListBoxName and FieldInTableToPopulate

Multiple select listbox, concatenate to single field in table

re: Multiple select listbox, concatenate to single field in table

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about