Robert:
You can't simply reference the list box. You need to first build a value
list of the items selected by iterating through its ItemsSelected collection.
To do this add a hidden text box to the form containing the list box and in
the list box's AfterUpdate event procedure put code which assigns the value
list to the hidden text box, e.g.
Dim varItem As Variant
Dim strItemList As String
Dim strCriteria As String
Dim ctrl As Control
Set ctrl = Me.ActiveControl
For Each varItem In ctrl.ItemsSelected
strItemList = strItemList & "," & ctrl.ItemData(varItem)
Next varItem
' remove leading comma
strItemList = Mid(strItemList, 2)
' assign value list to hidden text box
Me.txtHidden = strItemList
To use a value list in a query you'd normally use the IN operator, but
unfortunately this does not accept parameters. There is a workaround
available at the following link however:
http://support.microsoft.com/kb/100131/en-us
Add the GetToken and InParam functions from the link to a standard module.
Then in your query call the InParam function like so:
SELECT *
FROM [YourTable]
WHERE InParam([YourFieldName], [Forms]![YourForm]![txtHidden]) = TRUE;
The function calls do affect performance to some degree, but it should be
acceptable.
Ken Sheridan
Stafford, England
