Multi Select Listbox Query Criteria

[Robert_DubYa]

I'm trying to pass the values of a multi-select listbox on a form to a query
as criteria. The criteria in the query is:

In ([Forms]![frmEntry]![lstWorkCenters])

I have the property set to simple multiselect, but my query returns no
results. If I type the data in manually the query works fine. Any help
would be greatly appreciated.

Robert

 

[Ken Sheridan]

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