Access VBA列表框选值生成报表SQL代码问题排查求助

你的代码存在两个核心问题，会直接导致功能失效或报错：

1. 数组下标越界与赋值错位 #

ItemsSelected返回的是列表框选中项的索引集合，这些索引从0开始计数。原代码中arr(item - 1)的写法，当选中项索引为0时会生成arr(-1)，触发数组下标越界异常；同时若选中项不连续，还会导致数组位置与选中项不匹配，出现空值填充的情况。

正确的数组填充方式是用计数器逐个赋值：

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

With Me.lstOptions
    ReDim arr(.ItemsSelected.Count - 1)
    i = 0
    For Each item In .ItemsSelected
        arr(i) = .ItemData(item)
        i = i + 1
    Next
End With

2. SQL语法错误（文本字段未加单引号） #

cityName是文本类型字段，IN子句中的每个文本值必须用单引号包裹，否则Access会将其识别为无效语法或字段名，导致查询执行失败。

修改字符串拼接逻辑，给每个值加上单引号：

s = Join(arr, "','")
s = "'" & s & "'" ' 给首尾补全单引号
strRowSource = "Select cityName from tblMain Where cityName In (" & s & ")"

完整修正后的代码 #

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

With Me.lstOptions
    ReDim arr(.ItemsSelected.Count - 1)
    i = 0
    For Each item In .ItemsSelected
        arr(i) = .ItemData(item)
        i = i + 1
    Next
End With

s = Join(arr, "','")
s = "'" & s & "'"
strRowSource = "Select cityName from tblMain Where cityName In (" & s & ")"

内容的提问来源于stack exchange，提问作者Gourge Gourginian