I see that you are using the form from my CorrelatedLists demo. In the demo the filtering of the rows in the form is done by means of parameters in the form's RecordSource query:
SELECT Customers.ID, [First Name] & " " & [Last Name] AS Customer,
Products.[Product Name], SUM([Order Details].Quantity) AS Quantity
FROM Products INNER JOIN ((Customers INNER JOIN Orders
ON Customers.ID = Orders.[Customer ID]) INNER JOIN [Order Details]
ON Orders.[Order ID] = [Order Details].[Order ID])
ON Products.ID = [Order Details].[Product ID]
WHERE (InParam(Customers.ID,Forms!frmMultiSelectCorrelated!txtCustomerList) = TRUE
OR Forms!frmMultiSelectCorrelated!txtCustomerList IS NULL)
AND (InParam(Products.ID,Forms!frmMultiSelectCorrelated!txtProductList) = TRUE
OR Forms!frmMultiSelectCorrelated!txtProductList IS NULL)
GROUP BY Customers.ID, [First Name] & " " & [Last Name], [Last Name], [First Name], Products.[Product Name] ORDER BY [Last Name], [First Name], Products.[Product Name];
Because the IN operator does not accept a parameter as its value list the above query uses the following module, originally published by Microsoft, to simulate the use of the IN operator:
Option Compare Database
Option Explicit
'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'============================================================
Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function
Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function
The two parameters referenced by the form's query are hidden text boxes in the form. These are populated by the AfterUpdate event procedures of the two multi-select list boxes:
Private Sub lstCustomers_AfterUpdate()
Dim varItem As Variant
Dim strCustomerList As String
Dim strSQL As String
With Me.lstCustomers
If .ItemsSelected.Count <> 0 Then
For Each varItem In .ItemsSelected
strCustomerList = strCustomerList & "," & .ItemData(varItem)
Next varItem
' remove leading comma
strCustomerList = Mid(strCustomerList, 2)
' assign list to hidden control
Me.txtCustomerList = strCustomerList
Me.txtProductList = Null
strSQL = "SELECT Products.ID,[Product Name], SUM(Quantity) " & _
"FROM Products INNER JOIN (Orders INNER JOIN [Order Details] " & _
"ON Orders.[Order ID] = [Order Details].[Order ID]) " & _
"ON Products.ID = [Order Details].[Product ID] " & _
"WHERE [Customer ID] IN(" & strCustomerList & ") " & _
"GROUP BY Products.ID,[Product Name] " & _
"ORDER BY Products.[Product Name]"
Me.Requery
End If
Me.lstProducts.RowSource = strSQL
End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub lstProducts_AfterUpdate()
Dim varItem As Variant
Dim strProductList As String
With Me.lstProducts
If .ItemsSelected.Count <> 0 Then
For Each varItem In .ItemsSelected
strProductList = strProductList & "," & .ItemData(varItem)
Next varItem
' remove leading comma
strProductList = Mid(strProductList, 2)
' assign list to hidden control
Me.txtProductList = strProductList
Me.Requery
End If
End With
End Sub
The first of the above two procedures also builds and assigns an SQL statement to the RowSource property of the second list box to correlate it with the first, so that it lists only those products ordered by the selected customers.