use list box for query filter

[Guest]

Just a quick question

I am creating a form that will ultimately open a table based on a query.
One of my filter criteria is a list box that will allow multiple selections.
How would the where criteria look in the query i.e.
"forms![frm:2000RulesTableFilter]![lstRulesTableDetail] or would it be more
like In ("forms![frm:2000RulesTableFilter]![lstRulesTableDetail]")

I just can't get it to work!!

TIA

 

[Douglas J. Steele]

You can't simply refer to the control like that if it's multiselect: it
returns Null even if only one row is selected. You have to use VBA to set
the criteria, something like:

Dim strCriteria As String
Dim varSelected As Variant

With Forms![frm:2000RulesTableFilter]![lstRulesTableDetail]
For Each varSelected In .ItemsSelected
strCriteria = strCriteria & .ItemData(varSelected) & ", "
Next varSelected
End With

If Len(strCriteria) > 0 Then
strCriteria = [NameOfField] In (" & Left$(strCriteria, Len(strCriteria)
End If


To use that in a query, you'd have to reset the SQL associated with the
query.

 

[Dirk Goldgar]

In

Just a quick question

I am creating a form that will ultimately open a table based on a
query. One of my filter criteria is a list box that will allow
multiple selections. How would the where criteria look in the query
i.e. "forms![frm:2000RulesTableFilter]![lstRulesTableDetail] or would
it be more like In
("forms![frm:2000RulesTableFilter]![lstRulesTableDetail]")

I just can't get it to work!!

That's because it can't work. <g> A multiselect list box has no value,
and you can't refer to its selected items in that way.

There are couple of ways to work around this. One way is to use code to
loop through the list box's ItemsSelected collection and build a
comma-separated list of all the values, then build a SELECT statement
that uses the "In (your list)" operator in its WHERE clause, and then
dynamically update the query's SQL to set it to the statement you
constructed. Or you could set the RecordSource property of a form to
that SELECT statement.

There are other ways, but this is about the most efficient.