access VBA list box-CodePudding

I have an access database and I want to make a report based on items selected from a list box, I wrote following codes for that purpose, can anybody help me and tell me what's wrong with my codes? Thanks for your help

Dim arr() As Variant, item As Variant, strRowSource As String, s As String

With Me.lstOptions

    ReDim arr(.ItemsSelected.Count - 1)
    
    For Each item In .ItemsSelected
        arr(item - 1) = .ItemData(item)
    Next

End With
s = Join(arr, ",")

strRowSource  = "Select cityName from tblMain Where cityName In (" & s & ")"

CodePudding user response：

As cityName is likely a string, you need to put the items into (single) quotes.
Try

s = "'" & Join(arr, "','") & "'"

Update I thought your code to create the intermediate array is working and your problem was just because of the missing quotes. Your loop is using a for-each-loop with item as index-variable. Item will not contain 1, 2, 3 but an index to the selected item and therefore is not valid as index to your array (use the debugger to confirm and understand).

You could introduce an array-index variable:

Dim arrIndex as long
For Each item In .ItemsSelected
    arr(arrIndex) = .ItemData(item)
    arrIndex = arrIndex   1
Next
s = "'" & Join(arr, "','") & "'"

Or you could fill s directly, without the intermediate array:

Dim s As String
For Each item In .ItemsSelected
    s = s & IIf(s="", "", "','") & .ItemData(item)
Next
s = "'" & s & "'"

(Disclaimer: I don't have MS-Access available and therefore the code is untested).