0

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
LindsayRose
  • 1
  • 2
  • 1
    Lindsay, without your code, it is guess work ... – Gustav Aug 31 '22 at 15:17
  • Now show your listbox RowSource SQL. – June7 Aug 31 '22 at 15:58
  • Property Sheet- Row Source is "SELECT [OptionT].[OptionID], [OptionT].[OptionName] FROM OptionT;" – LindsayRose Aug 31 '22 at 16:35
  • See [here](https://stackoverflow.com/a/19353316/77335) for an example of how to fetch values from selected rows of a multi-select list box and concatenate them into a string variable. – HansUp Aug 31 '22 at 17:09
  • Value of each selected row is the bound column, in this case OptionID. If you want a different return, reference column by its index. Your code is also missing line continuation character `& _`. But could just be on one line `strReturn = strReturn & "," & lbx.Column(1, varItem)`. – June7 Aug 31 '22 at 17:11
  • Another example http://allenbrowne.com/ser-50.html. Don't need a Count of items selected. – June7 Aug 31 '22 at 17:23

2 Answers2

0

.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
HansUp
  • 95,961
  • 11
  • 77
  • 135
0

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!

LindsayRose
  • 1
  • 2
  • 1
    This is typical text of a comment, not an answer. Please, put your comment in the right place and consider deleting this one. – ALeXceL Sep 06 '22 at 02:09