Please assist on how to have each combo box display their own list and then be filtered

Question

I am seriously battling with integrating my combo boxes on a form.

I have 3 combo boxes:

1- Company - cboComp - tblCompany

2- Category - cboCat - tblCategory

3- FLEET NO - cboFlt - tblFltNo

These (1&2) are then sorted(criteria) via cboComp with the row source of tblFltSetup I have setup each combo box so that they filter into each other but have only managed to figure out how to do this according to the cboComp as long as it has a value selected, and if there is no value selected in cboComp then the other 2 combo boxes show nothing to select in their drop down list. This also applies to cboFlt, cboComp & cboCat must have values else I cant select a value for cboFlt.

Basically I want the combo boxes (1,2 & 3) to show their individual full list of options in the drop down regardless if any of the other combo boxes have a value selected but I then want the combo boxes to filter according to each individual combo box accordingly If I decide to only filter by cboCat & cboFlt for example.

Is this possible and how would I do this?

Answer 1

There are several design choices for implementing this:

1. Use the AfterUpdate event of upstream ComboBoxes to requery downstream ComboBoxes.
2. Use buttons to filter and reset the RowSource of the ComboBoxes and Requery them.
3. Cascade Requery downstream ListBoxes based on clicking items in upstream ListBoxes. Arrange the ListBoxes in columns, and place search TextBoxes above them.

Since item 1 is closest to your question, I've included code below and images to demonstrate approximately how I would do that (unrefined and incomplete). I'm filtering an Orders table based on a Customers table field from the sample Northwind database. The ComboBoxes are unbound. After a user changes the text in a ComboBox, it fires the AfterUpdate event.

Private Sub cboCustomers_AfterUpdate()

Dim strCustomer As String
Dim intCustomerID As Integer

'Store Customer data before losing focus
strCustomer = cboCustomers.Text

With cboOrders
    .SetFocus
    'The Customer ComboBox has NO data
    If strCustomer = "" Or IsNull(strCustomer) Then
        'Select unfiltered Orders table
        .RowSource = "SELECT [Order ID], [Customer ID], [Shipped Date], [Payment Type] " & _
                              "FROM Orders ORDER BY [Shipped Date];"
    'The Customer ComboBox has data
    Else
        'Select filtered Orders table
        intCustomerID = cboCustomers.Column(0)
        .RowSource = "SELECT [Order ID], [Customer ID], [Shipped Date], [Payment Type] " & _
                              "FROM Orders WHERE ((([Customer ID])=" & intCustomerID & ")) " & _
                              "ORDER BY [Shipped Date];"
    End If
    .Requery
End With
cboCustomers.SetFocus

End Sub

ComboBoxes Before Changing Text and Filtering

ComboBoxes After Changing Text and Filtering

ComboBoxes restored After Deleting Text in Companies ComboBox