Access: Saving then Loading (and selecting) SelectedItems from ListBoxes-CodePudding

I have a form with multiple listboxes with the MultiSelect property enabled (and a single option group). For row source, each listbox reads two columns (sysID, sysName) from a table (Systems) filtered by a third column (sysType) which corresponds to the type of systems managed in that listbox (e.g. row source is SELECT Systems.sysID, Systems.sysName FROM Systems WHERE Systems.sysType=3 ORDER BY Systems.sysName; for one of them).

I have a save button that executes the following to store a CfgID to the CfgSys table with each sysID (Systems.sysID = CfgSys.sysID) for later recall. It works like this (varSys is an array of Ints):

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

Some preamble and wrap-up omitted there for brevity. That part works great, for CfgID 1 I have rows in CfgSys corresponding to each of the 20 or so entries spread among the various listboxes.

I have a load button I would like to read those rows and select the various entries previously stored for each listbox. That part is driving me nuts. So far I have:

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")

But I can't figure out how to translate the SysID's/.itemData values from the Save part of the form into indexed line/row #'s for the .Selected collection of the listboxes, so right now it just selects one item in the longest of the listboxes, purely because it has a bunch of rows. Google hasn't helped me nor has Microsoft's reference on ListBox.ItemsSelected. The Debug.Print statement in there successfully prints the name of each listbox control and the sysID corresponding to the row I want to set, but I've been stuck at this last bit for 2 days - anyone able to lend some insight? Is this even possible?

CodePudding user response：

Per June7 above needed to iterate through .ItemData and match that way. Can't seem to mark their comment as the answer, but with their guidance ended up with this:

                Else
                strCtl = "lst" & rs!sysTypeName
                For i = 0 To frm.Controls(strCtl).ListCount - 1
                    If CStr(rs!sysID) = frm.Controls(strCtl).ItemData(i) Then
                        frm.Controls(strCtl).Selected(i) = True
                    End If
                Next i

Since ItemData returns strings the CStr was needed to get it to match up.