Multi-Column List Box – Select Multiple Items (1 Viewer)

[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 dbptimizeIt1 " & _
"WHERE dbptimizeIt1.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