MS Access VBA multiple list box search form

Consider building an entity-attribute table of all possible listbox values and use a saved SQL query which avoids any messy concatenation of SQL in VBA. A parameterized query using QueryDef is used to update the selected options of table of all list box values.

Table (myListBoxValues) (built once and updated with new values/categories)

Category|Value     |Selected
--------|----------|--------
Product |Product A |       1
Product |Product B |       1
Product |Product C |       1
...
Country |USA       |       1
Country |Canada    |       1 
Country |Japan     |       1

Above can be populated with multiple append queries using SELECT DISTINCT:

INSERT INTO myListBoxValues ([Category], [Value], [Selected])
SELECT DISTINCT 'Product', Tesco_Product_Name, 1
FROM dbo_busobj_file_rejections_load_temp5 b

NOTE: It is very important to default all Selected to 1 for VBA purposes. See further below. Also, if you have a mix of number and string, consider using TextValue and NumberValue columns and adjust in SQL IN clauses. Save above query as a new object and place the named object behind target: lstDeleteReject.

SQL (built once, adjust form name)

Notice the form date values are directly incorporated into WHERE clause without any date formatting conversion or concatenation needs. Also, table alias is used to avoid long name repetition.

SELECT b.Reject_Date AS [Date],
       b.Depot_Number AS [Depot No], 
       b.Depot_Name AS [Depot], b.Tesco_Product_Name AS [Product],
       b.Tesco_Brand_Name AS [Brand], b.Tesco_Packsize AS [Packsize], 
       b.TPNB, b.EAN, 
       b.Tesco_Country_of_Origin AS [Country], 
       b.Tesco_Variety AS [Variety], b.Tesco_Producer AS [Producer], 
       b.reject_qty AS [Quantity], b.batch_code AS [Batch Code], 
       b.site AS [Site], b.Tesco_Comment AS [Comment], 
       b.Tesco_Reason AS [Reason] 
FROM dbo_busobj_file_rejections_load_temp5 AS b
WHERE b.Reject_Date BETWEEN Forms!myFormName!txtDate 
                        AND Forms!myFormName!txtDateTo
  AND b.Tesco_Product_Name IN (
      SELECT [Value] FROM myListBoxValues 
      WHERE [Category] = 'Product' AND [Selected] = 1
  )
  AND b.site IN (
      SELECT [Value] FROM myListBoxValues 
      WHERE [Category] = 'Site' AND [Selected] = 1
  )
  AND b.Tesco_Producer IN (
      SELECT [Value] FROM myListBoxValues 
      WHERE [Category] = 'Producer' AND [Selected] = 1
  )
  AND b.Depot_Name IN (
      SELECT [Value] FROM myListBoxValues 
      WHERE [Category] = 'Depot' AND [Selected] = 1
  )
  AND b.Tesco_Country_of_Origin IN (
      SELECT [Value] FROM myListBoxValues 
      WHERE [Category] = 'Country' AND [Selected] = 1
  )

VBA (adjust list box names to actuals)

Dim qdef As QueryDef
Dim lstname As Variant
Dim sql As String
Dim i As Integer

sql = "PARAMETERS paramValue TEXT, paramCateg INTEGER; " _
      & "UPDATE myListBoxes SET [Selected] = 0 " _
      & "WHERE [Value] = paramValue AND [Category] = paramCateg"

Set qdef = CurrentDb.CreateQueryDef("", sql)

' ITERATE THROUGH ALL LISTBOXES BY NAME
For Each lstname in Array("lstProduct", "lstSite", "lstProducer", "lstDepot", "lstCountry")
    For i = 0 To Me.Controls(lstname).ListCount - 1  
      ' UPDATE IF AT LEAST ONE ITEM IS SELECTED
      If Me.Controls(lstname).ItemsSelected.Count > 0
        ' UPDATE [SELECTED] COLUMN TO ZERO IF VALUES ARE NOT SELECTED       
        If Me.Controls(lstname).Selected(i) = False Then
            qdef!paramValue = Me.Controls(lstname).Value
            qdef!paramCategory = Replace(lstName, "lst", "")
            qdef.Execute
        End If
      End If
    Next i
Next lstname

Set qdef = Nothing

' REQUERY LISTBOX
Me.lstDeleteReject.Requery

' RESET ALL SELECTED BACK TO 1
CurrentDb.Execute "UPDATE myListBoxValues SET [Selected] = 1"

As you can see, much better readability and maintainability. Also, if users do not select any option, the date range filters are still applied and using your universal table of all list box values, all values will be selected to returns all non-NULL values.