Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
Dim Q As QueryDef
Dim db As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Dim stDocName As String
' Build a list of the selections.
Set ctl = Me![LeaList]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Modify the Query.
Set db = CurrentDb()
Set Q = db.QueryDefs("individual LEA Green Form Report")
Q.sql = "SELECT [new final table].ProjectsCode, [new final table].[Project description], [new final table].[Date of Stage 4 Completed] FROM [new final table]WHERE [new final table].[Instruments & Quantity] In(" & Criteria & ");"
Q.Close
' Run the report from the listbox fed query
stDocName = "individual LEA Green Form Report"
DoCmd.OpenReport stDocName, acPreview
Exit_Command20_Click:
Exit Sub
Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click
End Sub
lea list is the listbox, and you will have the edit the Q.sql....
hth
ps . no idea how the get the code in nice quotes / formatting / sorry!
John
