Use Records in Multi Select Listbox in query

[TimTDP]

On a form I have a multi select list box. I want to use the records selected as a filter in a query.

I thought that I would create an unbound control (strProductsSelected) on the form and populate it with the records selected like this:

Me.strProductsSelected = ""

If Me.lstSelectProduct.ItemsSelected.Count = 0 Then Exit Sub

For Each varItem In Me.lstSelectProduct.ItemsSelected
    Me.strProductsSelected = Me.strProductsSelected & Me.lstSelectProduct.Column(0, varItem) & " or "
Next

Me.strProductsSelected = Left(Me.strProductsSelected, Len(Me.strProductsSelected) - 4)

If for example 3 records were selected, strProductsSelected would look something like this: 838 or 905 or 56

and then use a query like this:
SELECT ProductId, ProductCode, ProductDescription FROM tblProduct
WHERE (((tblProduct.ProductId)=[Forms]![frmPrintProductSelected]![strProductsSelected]));

But when I run the query no records are returned!
What am I doing wrong?

Is my method the most efficient?
How many ProductId's can be included in strProductsSelected am I limited to 256 characters?

Thanks in advance

 

[Mr. B]

Try having your code create a comma delimited string instead of adding the "or" between each value. Then use an In() method that uses the delimited string in the criteria field in your query.

 

[boblarson]

You can't do it this way. You may be able to use

WHERE (((tblProduct.ProductId) In(Eval([Forms]![frmPrintProductSelected]![strProductsSelected]))));

But I've found I have to build the query in VBA if I want to use multi-select the way you are doing the code now but having to modify the query by use of a querydef object.

Dim qdf As DAO.QueryDef
Dim strWhere As String
Dim varItem As Variant
Dim strSQL As String
 
For Each varItem In Me.lstSelectProduct.ItemsSelected
    strWhere = strWhere & Me.lstSelectProduct.ItemsSelected(varItem) & ","
Next
 
If Right(strWhere, 1) = "," Then
   strWhere = Left(strWhere, Len(strWhere)-1)
End If
 
Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")
strSQL = "Select * From ... " & _
" Where [FieldNameHere] In(" & strWhere & ")"
 
qdf.SQL = strSQL
 
qdf.Close
Set qdf = Nothing