Filter Listbox by listbox selection?

[dpelizzari]

I am currently working on a form for my users to look at budget data. I have several listboxes for them to filter queries by. What I was wondering is, if it is possible to have data in one list box be filtered by the preceeding listbox? For example: Three list boxes: Report_Period, Budget, Dept. Each list contains all the values possible. This data is all in a filter table (and the listboxes created using SELECT DISTINCT queries to that table for the specific field). So, if I select Budget XYZ, could I filter the Dept listbox to only show the dept.'s associated with that budget? These are all multi-select listboxes by the way, so budget ABC and XYZ would only show the departments associated as well.

 

[pbaldy]

You'd combine

http://www.baldyweb.com/CascadingCombo.htm

with the multiselect loop to build your SQL:

http://www.baldyweb.com/multiselect.htm

 

[dpelizzari]

thanks, Paul, I was kind of thinking that was what would be required, just under a time crunch currently was hoping for something quick & easy. So, basically, create the multi-select loop based on the Budget, then apply that filter to the Dept query. I did similar for the queries that run off these filters (including some additional code to allow them to select all by simply not making a selection).

 

[pbaldy]

The multiselect listbox makes things more complicated, as you have to loop the selected items. I'd use the second method from the Cascading Combo link, and adapt the looping code into it.

 

[dpelizzari]

finally getting back around to this, so, I am able to filter the next listbox by the previous, however, I then realized I needed to filter the third list box by the first two (have a headache just thinking about the 6th iteration)... so, thinking logically, I just need to add in additional variables and perform the same process...

Dim StrWhere As String
Dim StrWhere2 As String
Dim ctl As Control
Dim ctl2 As Control
Dim VarItem As Variant
Dim VarItem2 As Variant
Dim RegionValue As String
Dim GBUValue As String
Set ctl = Me.GBU
Set ctl2 = Me.Region
For Each VarItem In ctl.ItemsSelected
StrWhere = StrWhere & "'" & ctl.ItemData(VarItem) & "',"
Next VarItem
For Each VarItem2 In ctl2.ItemSelected
StrWhere2 = StrWhere2 & "'" & ctl2.ItemData(VarItem2) & "',"
Next VarItem2
StrWhere = Left(StrWhere, Len(StrWhere) - 1)
StrWhere2 = Left(StrWhere2, Len(StrWhere2) - 1)

However, when I run this, I am getting a runtime error 438, and it highlights the line "For Each VarItem2 in Ctl2.ItemSelected". More :banghead:

 

[dpelizzari]

Hmmm, played with this some more, must have had a typo somewhere, the error went away.

 

[pbaldy]

Probably here:

ItemSelected

as it's actually

ItemsSelected

 

[dpelizzari]

thanks, P, the spell check is strong with you

 

[pbaldy]

LOL! Happy to help.