RE: Access 07 Use multiselect listbox to filter report

Based on the error you are receiving, it sounds as if the hack is executing
prematurely. It needs to execute AFTER this block:

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ItemNumber] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Quotebook: " & Left$(strDescrip, lngLen)
End If
End If

BUT, based off your explanation of what you are doing, I no longer believe
this will work for you. If I am reading that correctly, your query needs to
return values based on pairs of data, e.g. B784 & Green, B784 & Blue, etc.
While the method above would return those values, it would also return B784 &
Purpl, if that record existed.

So what I think you could do instead is create a concatenated field in the
query that fills your multi-select listbox:

"Select ItemNumber & ItemColor As ItemNumberColor, ItemNumber, ItemColor
From ...". Use this field as the bound column, and include the same field in
qryMain. Then restructure Allen's code to build the where clause from that.
You no longer need the hack.

Does that make any sense?


.

Relevant Pages