[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-11
5
Medium Priority
?
1,063 Views
Last Modified: 2014-08-12
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.
0
Comment
Question by:InlinePlastics
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 40254051
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
 

Author Comment

by:InlinePlastics
ID: 40254236
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40254384
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
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40254429
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
 

Author Comment

by:InlinePlastics
ID: 40256333
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question