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:

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

[NeoPa]

Hi Melissa. A more compact version of the code might be :

Private Sub ListBoxName_Click()
    Dim strSelected As String
    Dim varItem As Variant
 
    With Me.ListBoxName
        For Each varItem In .ItemsSelected
            strSelected = strSelected & "," & .ItemData(varItem)
        Next varItem
        Me.FormControlToPopulate = Mid(strSelected, 2)
    End With
End Sub

See the technique used to avoid different versions of the code to add the first item. I use this concept frequently in my code.

NB. Also, A field is not the same thing as a control. A field is where data is held and may contain different values for each logical record in the record source. A control, on the other hand, is simply a window onto that data within a form or report (assuming the control is bound - otherwise it's not even that connected).