Trying to create multiple multi-select cascading listboxes on access 2003 form.

5 multi-select list boxes on form.  When selection(s) are made from any 1 of the 5 list boxes(no set order), remaining 4 list boxes show data filtered by selection made (maybe use after update event???).  Then, when second list box selection(s) made, remaining 3 list boxes show data filtered by selections made from 2 list boxes and so on til reach 5th list box or user clicks command button.  Command button on form displays report using criteria from all list box selections.
InlinePlasticsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If the listboxes/combos are hierarchal in nature, then they need to be clicked in order.  Each list/combo controls what the next one in line will show so checking them out of order doesn't make any sense.  The standard technique for cascading combo/listboxes is to have the RowSource query refer to the "parent" combo/list.
For combo2
Where MyID = Forms!myForm!cbo1
For combo3
Where MyID = Forms!myForm!cbo2
For combo4
Where MyID = Forms!myForm!cbo3
etc.
In the AfterUpdate event, clear each child combo and requery the immediate child.
For combo1
Me.cbo2 = null
Me.cbo3 = null
Me.cbo4 = null
Me.cbo5 = null
Me.cbo2.Requery
For combo2
Me.cbo3 = null
Me.cbo4 = null
Me.cbo5 = null
Me.cbo3.Requery
etc.
0
InlinePlasticsAuthor Commented:
Pat,  thank you for your response.  I understand how to do this with single selections from combo boxes, but, how do I do this with multiple selections from list boxes?  I know I have to loop through the list box selections using a "For...Next" statement and concatenate each value separated by commas, but, then I am unsure how to insert the concatenated value or variable in the row source query for the remaining list boxes.  I have the following multi-select list boxes with no hierarchy: customer, broker, order, Item and Item Type.  If customer# 530031 and 680071 are selected from customer list box, only brokers for these customers will display in the broker list box, only orders for these customers will display in the order list box, etc. etc. Once a selection is made in the broker list box, the orders are narrowed down, etc. etc.
0
GrahamSkanRetiredCommented:
I think that you will have to build a query string to select the available choices, something like this:
Function GetMyQuery() As String
    Dim strTest(5) As String
    Dim strSQL As String
    Dim strAND As String
    
    If List1.Text <> "" Then
        strTest(1) = "Field1 = List1.text"
    End If
    If List2.Text <> "" Then
        strTest(2) = "Field2 = List2.text"
    End If
    If List3.Text <> "" Then
        strTest(3) = "Field3 = List3.text"
    End If
    If List4.Text <> "" Then
        strTest(4) = "Field4 = List4.text"
    End If
    If List1.Text <> "" Then
        strTest(5) = "Field5 = List5.text"
    End If
    
    strSQL = "SELECT * from MyTable WHERE "
    
    
    
    
    For i = 1 To 5
        If strTest(i) <> "" Then
            strSQL = strSQL & strAND & strTest(i)
            strAND = " AND "
        Next i
    End If
    GetMyQuery = strSQL
End Function

Open in new window

0
PatHartmanCommented:
Instead of having the RowSources have Where clauses that point to a specific list box, you will need to replace the RowSource query instead.

Pseudo code:
Dim i as Variant
Dim strIN as string

strIN = ""
For Each i in Me.lst1.ItemsSelected
    strIN = Me.lst1 & ","
Next i

strIN = Left(strIN, Len(strIN) -2)
If strIN = "" Then
    msgBox "Please select at least one item",vbokOnly
    Exit Sub
End If

Me.lst2.RowSource = "Select fld1, fld2 From YourTable Where fld3 IN(" & strIN & ");"
    

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
InlinePlasticsAuthor Commented:
Pat, I put the pseudo code For..next portion in the after update of the 1st list box and set the row source for the remaining 4 list boxes using the IN clause and it filters perfectly.  I just have to do the same on each of the remaining 4 list boxes.  Thank you sooooo much!
Lisa
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.