Access 2003 – Multi-Column List Box – Select Multiple Items

[KrazyKasper]

Access 2003 – Multi-Column List Box – Select Multiple Items

I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended).

I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the items in the list box and selects those fields to populate the list box.

My table has about 200 fields and the three fields I need are like number 32, 4, and 132 in the table. Can someone help me modify the code to get the fields I need? Here’s my code:

Private Sub SelectedContract_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_OptimizeIt3")
' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!List10.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
    Next varItem
' Check that user selected something
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything from the list" _
            , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _
             "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

Krazy (Bill) Kasper

[NeoPa]

While posting code can be useful for reference purposes, it's no substitute for a clearly stated question.