Multi Select list box as parameter for report or query?

[Guest]

I’m not very experienced working with Multi Select list boxes; particularly,
using the list box values as the parameters for a query. I’ve discovered
that it’s not as simple as point to the list box in the query build as in
working with combo boxes.
Here’s what I need help with:
I need to run a report that returns only the rows with parameter values
taken from my list box.
I have three tables that will be joined in order to get the field values I
need in my report.
Tableâ€A†has records of products, Tableâ€B†has records of customers, and
Table “C†is an associative table – it has customer products.
So, my list box has the values of all the products. One selects one or more
products, then selects the report command button. This should take you to
the report which will return the customer products of only the customers
associated with the products selected in the products listbox.

Also, is it best to send this directly to the report or build it into the
query, which is the record source for the report?

Thanks for your help!

 

[Arvin Meyer [MVP]]

While you can fill hidden text boxes and use them in a query, it isn't
feasible if there are more than 4 or 5 choices. Most of the time I build a
hidden text box (txtSelected) and use code to fill the form or report.
Here's an example:

Private Sub lstElevation_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstItem
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With


strSQL = "SELECT * FROM tblItem WHERE (((tblItem.ModelID) In ("
& Me.txtSelected & ")) "

Reports!rptItems.Recordsource = strSQL

Do Cmd.OpenReport "rptItems", acViewPreview

End Sub