More then 1 Multiselect Listbox

[mixedguy]

Here is the VB Code to open a report based on ONE multiselect listbox.

Private Sub Command4_Click()
For Each varItem In Me![List0].ItemsSelected
StrWhere = StrWhere & "Spt_Fac =" _
& Chr(39) & Me![List0].Column(0, varItem) & Chr(39) & " Or "
Next varItem
Next varItem
StrWhere = Left(StrWhere, Len(StrWhere) - 4)
DoCmd.OpenReport "rpt_99NOC", acViewPreview, , StrWhere
End Sub

If I wanted to put two or three multiselect list box on my form and allow the user to pick from them to query and produce the report, what would the VB code for that be???

Please help. Thanks in advance!

 

[Travis]

You would need to loop through each List box and concatinate the results together. Then put it as your where clause in the statement.

 

[mixedguy]

Need Example

Travis,

Can you please provide an example of the Looping Code and Concatenation? I'm not very familiar with VB code so if you could write the code "step by step" that would greatly be appreciated!

Thanks!

 

[David R]

My additions are underlined.

Private Sub Command4_Click()
Dim StrWhere As String
Dim varItem As Variant

StrWhere = "("

For Each varItem In Me![List0].ItemsSelected
StrWhere = StrWhere & "Spt_Fac =" _
& Chr(39) & Me![List0].Column(0, varItem) & Chr(39) & " Or "
Next varItem

StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ") AND ("

For Each varItem In Me![List1].ItemsSelected
StrWhere = StrWhere & "Next_Fac=" _
& Chr(39) & Me![List1].Column(0, varItem) & Chr(39) & " Or "
Next varItem

StrWhere = Left(StrWhere, Len(StrWhere) - 4) & ")"

DoCmd.OpenReport "rpt_99NOC", acViewPreview, , StrWhere
End Sub

You have to keep the 'logic' of the WHERE statement in mind - if you don't enclose the ORs in parentheses, the ANDs will foul up your logic train. If you get confused, put a MsgBox StrWhere line in to see what your WHERE clause actually looks like before you open the report.

 

[mixedguy]

More Question

David,

You answered my question about what code I would need to generate a report based on two multiselect listbox. But you have to select at least an item in each of the list box otherwise an error would come up. What would the VB Code be if the user still had the same form (two multiselect list box) but had the option of choosing in both list box or just in one list box and then clicking the button to generate the report.

Thanks both Travis and David. You've been extremely helpful!

 

[David R]

Ahhh...

I surround the entire thing with an IF loop if I want some to be able to be left blank.

If Me.List0.ItemsSelected.Count > 0 Then
For Each ...

Make sure you get the parentheses outside the IF statements in that case. It takes a bit of debugging to get it all running smoothly but when you do, it can't be beat for flexibility.

 

[mixedguy]

Fantastic!!!

Thanks David!

I added the "If, Then Statements" to the VB Code and after a bit of debugging it works! Wow this is a powerful tool for users. I'm sure they'll love it once I'm done with it.

Thanks again for all your help!