This code uses the Employees table from the Northwind Sample Database. The form contains a Listbox (Lst1) with all of the employees in it and its bound column is to the primary Key in the Employees table (EmployeeID). A report is created (rptEmpSQL) to show the desired fields, and its Record Source is a query named qryRpt. The query gets deleted and then rebuilt each time you run the code. This code Loops through the Listbox and captures those items selected and adds it as the criteria (Where condition) to the SQL statement for the query using by using the function IN().
Private Sub cmdRpt_Click()
Dim strSQL As String
Dim db As DAO.database, qdf As DAO.QueryDef
Set db = CurrentDb
Dim var
On Error Resume Next
'Delete the existing query qryRpt
DoCmd.DeleteObject acQuery, "qryRpt"
On Error GoTo errHandler
strSQL = "Select * From Employees Where employeeID In("
For Each var In Lst1.ItemsSelected
strSQL = strSQL & Lst1.ItemData(var) & ", "
Next var
strSQL = Left(strSQL, Len(strSQL) - 2) & ")"
Set qdf = db.CreateQueryDef("qryRpt", strSQL)
DoCmd.OpenReport "rptEmpSQL", acViewPreview
errExit:
Exit Sub
errHandler:
MsgBox Err.Number & " : " & Err.Description
Resume errExit
End Sub
HTH
PaulF
