Access Multi-Select Listbox, Items Selected String, Text not Integer-CodePudding

I am currently working on an Access Database and I am trying to create a string of selected items in a multi-select listbox separated by commas (ex. Option 1, Option 3, Option 5). I have been able to get the program to create a list, but it is listing Integers (1,3,5) instead of the text I wrote as the primary key for the data I am using in the listbox. I have tried changing my commands as Variant instead of Integer, but it is still listing 0-based numbers. Does anyone know a solution to this problem or a code that creates the string out of text instead of integers? Thank You!

This is the code I have currently (OptionL is the name of my list box)

Function LbxItems() As String
'Returns a list of items in the listbox
Dim lbx As ListBox
Dim varItems As Variant
Dim varItem As Variant
Dim strReturn As String
strReturn = ""
Set lbx = Me!OptionL
varItems = lbx.ItemsSelected.Count
If varItems > 0 Then
    For varItem = 0 To varItems - 1
        If varItem = 0 Then
            strReturn = CStr(lbx.ItemsSelected(varItem))
        Else
            strReturn = strReturn & "," & 
            CStr(lbx.ItemsSelected(varItem))
        End If
    Next
End If
Set lbx = Nothing
LbxItems = strReturn
End Function

CodePudding user response：

I was able to use the code from the link HansUp recommended to solve the problem. (https://stackoverflow.com/a/19353316/77335) Thank you all so much!

CodePudding user response：

.ItemsSelected(<some number>) does not give you what I think you're expecting. It does not give you the value from any column within that selected row.

Use ItemData if you want the value contained in the Bound Column of a selected row. So you could make changes like this to your existing code:

'strReturn = CStr(lbx.ItemsSelected(varItem))
strReturn = CStr(lbx.ItemData(lbx.ItemsSelected(varItem)))

However I would switch to a simpler approach like this:

Public Function LbxItems() As String
    Dim strReturn As String
    Dim varItem As Variant

    With Me.OptionL
        For Each varItem In .ItemsSelected
            strReturn = strReturn & "," & .ItemData(varItem)
        Next
    End With
    If Len(strReturn) > 0 Then
        strReturn = Mid(strReturn, 2) ' discard leading comma
    End If
    LbxItems = strReturn
End Function