Access 2000 using multiselect listboxes (1 Viewer)

[Benny Wong]

Hello All,

I am working on an Access 2000 project that involves creating reports using a listbox that is set as multiselect. I know how to create reports using only one listbox that is set as multiselect, but how do I create reports that involves using multiple listboxes that is also set as multiselect. Any ideas on how to do it and if possible any sample code. Thanks for your time and consideration.

 

[Alexandre]

If you know how to do that for one listbox, there are not many differences with various ones. For one listbox, before opening your report, you build a string that is the equivalent of a Where clause in SQL:
strFilter.

For various listboxes, you'll just have to further complete this string by applying the same process to each of your listboxes and combining the resulting string with ANDs or ORs, depending on what is your logic in combining your criteria.

Ex, just to show the logics:
strFilter=""
Go through Listbox1 and build strFilterTemporary
strFilter = strFilterTemporary
Go through Listbox2 and build strFilterTemporary
if there where selected items in Listbox2 then strFilter = "(" & strFilter & ") AND (" & strFilterTemporary & ")"
Go through Listbox3 and build strFilterTemporary
if there where selected items in Listbox3 then strFilter = strFilter & " AND (" & strFilterTemporary & ")"
...

If you organize yourself well, by creating a function to return strFilter based on a given listbox control, and naming sistematically your listboxes, you can make a loop and avoid redundant code:

strFilter=""
For i=1 to 3
strFilterTemporary = fnBuildFilterStringFromListBox (Me.Controls("Listbox" & i))
strFilter = strFilter & iif(strFilterTemporary = "", "", " AND (" & strFilterTemporary & ")")
Next i

HTH