Filter query via multiselect

[Post=2994 NYIntensity]

Gosh it's been a long time... Here's what I've got - it works when I select one item from the listbox. If I select more than one, the query ends up showing ALL records.

Code: Select all

Private Sub Command2_Click()
   Dim Q As QueryDef, DB As Database
   Dim Criteria As String
   Dim ctl As Control
   Dim Itm As Variant

   ' Build a list of the selections.
   Set ctl = Me![lstBuilds]

   For Each Itm In ctl.ItemsSelected
      If Len(Criteria) = 0 Then
         Criteria = ctl.ItemData(Itm)
      Else
         Criteria = Criteria & " OR " & ctl.ItemData(Itm)
      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("qryReport")
   Q.SQL = "SELECT DISTINCT tblIMAGES.Build, tblIMAGES.Date_Created, tblUPDATES.Update_Name, tblUPDATES.Update_Date_Released, tblUPDATES.Update_Date_Applied, tblUPDATES.Update_Description " & _
   "FROM tblUPDATES INNER JOIN (tblIMAGES INNER JOIN tblLINKS ON tblIMAGES.ID = tblLINKS.ImageID) ON tblUPDATES.ID = tblLINKS.UpdateID " & _
   "WHERE (((tblIMAGES.ID)=" & Criteria & "));"
   Q.Close

DoCmd.OpenReport "rptUpdates", acViewPreview
End Sub

[Post=3000 NYIntensity]

I got it! I had to change

Code: Select all

Criteria = Criteria & " OR " & ctl.ItemData(Itm)

to

Code: Select all

Criteria = Criteria & " or ((tblimages.id) =" & ctl.ItemData(Itm) & ")"