cascading multiple selection list boxes?

[kidrobot]

Has anyone ever done this?

I have two multiple selection list boxes, where the second one filters off the first one. So if the user picks 3 items from the first list box then a re-query should update the 2nd list box with the corresponding items.

I've tried to do this like they are combo boxes but it doesn't work. Any help would be great!!

 

[kidrobot]

btw I am doing this to run a query to have the list box as criteria. I have this working for one list box. So after I get the second list box working I should be able to have both boxes as criteria right?

 

[Banana]

The thing is that if you set listbox's result as criteria, you will get only one selection, whether more than one were selected or not.

In order to get the complete listing, you'll need to loop through the selection, add it to a string then insert that string in the query's criteria.

You'll need to do VBA here. You may be able to get away with using the click event if it is not too intensive. Otherwise an double click or a button will be a good placeholder for the code.

 

[kidrobot]

Banana, I see what you're saying and I have tried to build the Code myself. Here is what I have, but I'm not sure how I can re-query my list box with this code. Hopefully you can help me out.

Private Sub lstTrains_AfterUpdate()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
   Set frm = Forms!MainForm
   Set ctl = frm.lstTrains
   strSQL = "Select * from StopOrder where [Train]="
   'Assuming long [Train] is the bound field in lb
   'enumerate selected items and
   'concatenate to strSQL
   For Each varItem In ctl.ItemsSelected
       strSQL = strSQL & ctl.ItemData(varItem) & " OR [Train]="
   Next varItem

   'Trim the end of strSQL
   strSQL = Left$(strSQL, Len(strSQL) - 12)
   
End Sub

 

[Banana]

If the query is working correctly and all you need to do is requery the listbox, then you just need this:

ctl.Requery

Edit: I just noticed there's nothing about updating the rowsource of the listbox... If you aren't updating the SQL, nothing will change.

ctl.Rowsource = strSQL

As matter of note, it may be more efficient to use a stored parameter query, and insert the strSQL in the parameter instead of re-writing the rowsource's SQL each time you update it.

 

[kidrobot]

If the query is working correctly and all you need to do is requery the listbox, then you just need this:

ctl.Requery

Don't I need to re-query the other listbox? Well here is my updated code, but it still isn't working..

Private Sub lstTrains_AfterUpdate()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

   Set frm = Forms!mainform
   Set ctl = frm.lstTrains
   Set ctl2 = frm.LstOS
   
   strSQL = "Select * from StopOrder where [Train]="
   'Assuming long [Train] is the bound field in lb
   'enumerate selected items and
   'concatenate to strSQL

   For Each varItem In ctl.ItemsSelected
       strSQL = strSQL & ctl.ItemData(varItem) & " OR [Train]="
   Next varItem

   'Trim the end of strSQL
   strSQL = Left$(strSQL, Len(strSQL) - 12)
   
   Debug.Print strSQL
   ctl2.Requery
   
End Sub

My debug.print gives me...
Select * from StopOrder where [Train]=204 OR [Train]=205 OR [Train]=206 OR [Train]=207

 

[kidrobot]

Edit: I just noticed there's nothing about updating the rowsource of the listbox... If you aren't updating the SQL, nothing will change.

ctl.Rowsource = strSQL

As matter of note, it may be more efficient to use a stored parameter query, and insert the strSQL in the parameter instead of re-writing the rowsource's SQL each time you update it.

does the ct.rowsource=strsql go at the end of my code?

So in essence would this stored query be a temp query?

 

[Banana]

Yes, that's correct; just insert it before you requery the listbox. We'll concentrate on getting it to work before we try parameter query.

You only requery the other listbox if that listbox's content has changed, too.

 

[kidrobot]

When I do this... ctl.RowSource = strSQL

it blanks all the values out of my Train Listbox.

to give you a better picture the end of my code looks like this

   strSQL = Left$(strSQL, Len(strSQL) - 12)
   
   Debug.Print strSQL
   ctl.RowSource = strSQL
   ctl.Requery

 

[Banana]

Hmm.

And the strSQL is returning the correct SQL when you look at the immediate window, no?

If so, copy and paste it into the listbox's rowsource in design mode then run it to see if it's showing as expected.

 

[kidrobot]

ahhh I totally forgot I need to put quotes around the train numbers... in other words.

[Train]="204" OR [Train]="205" OR [Train]="206" OR [Train]="207"


I'll try and fix this and give you an update! thanks with everything so far.

Edit: Nevermind !!

 

[kidrobot]

AWESOME. I have it working!!!

Final code...

Private Sub lstTrains_AfterUpdate()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

   Set frm = Forms!mainform
   Set ctl = frm.lstTrains
   Set ctl2 = frm.LstOS
   
   strSQL = "Select * from StopOrder where [Train]="
   'Assuming long [Train] is the bound field in lb
   'enumerate selected items and
   'concatenate to strSQL

   For Each varItem In ctl.ItemsSelected
       strSQL = strSQL & "'" & ctl.ItemData(varItem) & "'" & " OR [Train]="
   Next varItem
   Debug.Print strSQL

   'Trim the end of strSQL
   strSQL = Left$(strSQL, Len(strSQL) - 12)
   
   Debug.Print strSQL
   ctl2.RowSource = strSQL
   ctl.Requery
   ctl2.Requery
End Sub

 

[Banana]

Glad you got it to work.

Don't forget to delete all debug.print from the final code.

 

[kidrobot]

One more thing. Sometimes the 2nd list box will show the same value twice. Is there any way I can make it select Unique values?

I tried

strSQL = "Select DISTINCT* from StopOrder where [Train]="

which doesn't seem to work.

 

[kidrobot]

never mind I figured it out =)

 

[Banana]

Why not post the solution about duplicating value for others' benefit?

 

[kidrobot]

Sure, instead doing from * I did...

strSQL = "Select DISTINCT OS from StopOrder where [Train]="

So the query will only have one column, instead of every column.