Access中ListBox选中项的保存与加载选中实现问题

场景说明 #

• 窗体包含多个启用MultiSelect属性的ListBox和一个选项组
• 每个ListBox的行来源从Systems表读取sysID、sysName两列，通过sysType筛选（示例行来源：SELECT Systems.sysID, Systems.sysName FROM Systems WHERE Systems.sysType=3 ORDER BY Systems.sysName;）
• 保存功能已实现：将CfgID与选中项的sysID存入CfgSys表，运行正常，CfgID=1时已存入20+条记录

已实现的保存代码 #

Save_Config:
    i = 0
    For Each ctl In frm.Controls
        If ctl.ControlType = acListBox Then
            For Each varItm In ctl.ItemsSelected
                varSys(i) = ctl.ItemData(varItm)
                i = i + 1
            Next varItm
        ElseIf ctl.ControlType = acOptionGroup Then
            varSys(i) = ctl.Value
            i = i + 1
        End If
    Next ctl
    For i = LBound(varSys) To UBound(varSys)
        If (Not IsNull(varSys(i))) And (varSys(i) <> 0) Then
        strSQLIns = "INSERT INTO CfgSys (CfgID, SysID) VALUES (" & varCfgID & "," & varSys(i) & ");"
        DoCmd.RunSQL (strSQLIns)
        End If
    Next

当前加载代码（存在问题） #

Load_Config:
    strSQL = "SELECT CfgSys.CfgID, CfgSys.SysID, Systems.sysType, SysTypes.sysTypeName FROM " & _
             "(SysTypes RIGHT JOIN Systems ON SysTypes.[SysType] = Systems.[SysType]) " & _
             "RIGHT JOIN CfgSys ON Systems.[sysID] = CfgSys.[SysID] WHERE CfgSys.[CfgID] =" & varCfgID & ";"
    Set rs = db.OpenRecordset(strSQL)
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
                If rs!sysTypeName = "Electrical" Then
                    strCtl = "optElectrical"
                    frm.Controls(strCtl).Value = rs!sysID
                    Debug.Print strCtl & ": " & rs!sysID
                Else
                    strCtl = "lst" & rs!sysTypeName
                    frm.Controls(strCtl).Selected(rs!sysID) = True
                    Debug.Print strCtl & ": " & rs!sysID
                End If
                .MoveNext
            Wend
        End If
    End With
    Response = MsgBox("Configuration loaded.", vbOKOnly Or vbInformation, "Load Successful")

遇到的问题 #

无法将保存的SysID/.itemData值转换为ListBox的.Selected集合所需的行索引，导致仅能在最长的ListBox中选中一个项。Debug.Print能正确输出目标控件名和对应的sysID，但选中状态无法正确设置。

解决方案 #

问题核心是：ListBox.Selected需要的是行的索引位置，而不是sysID的值。必须遍历目标ListBox的行，找到sysID匹配的行后，再设置其Selected属性为True。

优化后的加载代码 #

Load_Config:
    Dim ctl As Control
    Dim rs As Recordset
    Dim strSQL As String
    Dim strCtl As String
    Dim targetList As ListBox
    Dim rowIdx As Integer
    
    ' 加载前清空所有控件的选中状态，避免残留旧配置
    For Each ctl In frm.Controls
        If ctl.ControlType = acListBox Then
            ctl.Value = Null
        ElseIf ctl.ControlType = acOptionGroup Then
            ctl.Value = 0
        End If
    Next ctl

    strSQL = "SELECT CfgSys.CfgID, CfgSys.SysID, Systems.sysType, SysTypes.sysTypeName FROM " & _
             "(SysTypes RIGHT JOIN Systems ON SysTypes.[SysType] = Systems.[SysType]) " & _
             "RIGHT JOIN CfgSys ON Systems.[sysID] = CfgSys.[SysID] WHERE CfgSys.[CfgID] =" & varCfgID & ";"
    Set rs = db.OpenRecordset(strSQL)
    
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst
            While (Not .EOF)
                If rs!sysTypeName = "Electrical" Then
                    strCtl = "optElectrical"
                    frm.Controls(strCtl).Value = rs!sysID
                    Debug.Print strCtl & ": " & rs!sysID
                Else
                    strCtl = "lst" & rs!sysTypeName
                    Set targetList = frm.Controls(strCtl)
                    
                    ' 遍历ListBox的所有行，匹配sysID对应的行索引
                    For rowIdx = 0 To targetList.ListCount - 1
                        ' sysID对应ListBox的第一列（Column索引从0开始）
                        If targetList.Column(0, rowIdx) = rs!sysID Then
                            targetList.Selected(rowIdx) = True
                            Exit For ' 找到匹配项后终止循环，提升效率
                        End If
                    Next rowIdx
                    
                    Debug.Print strCtl & ": " & rs!sysID & " (行索引：" & rowIdx & ")"
                End If
                .MoveNext
            Wend
        End If
    End With
    
    ' 释放对象
    Set rs = Nothing
    Set targetList = Nothing
    Response = MsgBox("Configuration loaded.", vbOKOnly Or vbInformation, "Load Successful")

关键说明 #

1. 清空原有状态：加载前重置所有控件的选中状态，避免旧配置干扰新的加载结果
2. 行索引匹配：通过遍历ListBox的Column集合，对比每行的sysID值，找到对应的行索引后设置选中状态
3. 效率优化：找到匹配项后立即退出循环，减少不必要的遍历操作

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