Interchangeable cascading combo boxes

[gauntletxg]

I have a customer address book form, with over 500 customers in it right now. There's a combo box with a list of all the customer names, and the user can select the customer they want to view the contact info. I'm trying to make it easier for people to find the customer they want.

I have 2 cascading combo boxes. The first is customer type, and the second is state. These work fine if you follow them in order. So, when both of these combo boxes are null, my customer combo box shows all records. I can then pick a customer type, and the state combo box only shows states where we have customers of that specific type. I can then choose a state to find customers of type X in state Y.

The problem is, I can't get these combo boxes to work independently. So when I select just a customer type, my customer combo box will not display any records until I select a state. The same is true for selecting just a state.

Anyone know what piece of the puzzle I'm missing? Thanks.

 

[Pampers]

Can you maybe give some more detail on how your cascading comboxes are working?

Pampers
Keeping it simple can be complicated

 

[fneily]

How about having your type combo and state combo without any event coding. Just regular comboboxes. Then have an option group with Filter By Type, Filter By State, Filter By Both. Then the user's choice of the option group would dictate which combobox values are used to create the source for the customer combobox.

 

[Pampers]

or, straight from the combo's

Private Sub FillCustomerCombo()
Dim strRowSource As String

'Both Selected
If Me.cboCustomerTypeFilter & "" <> "" And Me.cboStateFilter & "" <> "" Then
    strRowSource = _
"SELECT tblCustomer.ID, tblCustomer.Name, tblCustomer.CustomerTypeID, tblStates.StateID " & _
"FROM (tblCustomer INNER JOIN tblCustomerType ON tblCustomer.CustomerTypeID=tblCustomerType.CustomerTypeID) INNER JOIN tblStates ON tblCustomer.StateID=tblStates.StateID " & _
"WHERE (((tblCustomer.CustomerTypeID) = Forms!Form3!cboCustomerTypeFilter) And ((tblStates.StateID) = Forms!Form3!cboStateFilter)) " & _
"ORDER BY tblCustomer.Name;"


End If

'Nothing selected
If Me.cboStateFilter & "" = "" And Me.cboCustomerTypeFilter & "" = "" Then
    strRowSource = _
    "SELECT tblCustomer.ID, tblCustomer.Name " & _
    "FROM tblCustomer " & _
    "ORDER BY tblCustomer.Name"
End If

'CustomerTypeFilter selected
If Me.cboCustomerTypeFilter & "" <> "" And Me.cboStateFilter & "" = "" Then
strRowSource = _
"SELECT tblCustomer.ID, tblCustomer.Name, tblCustomer.CustomerTypeID " & _
"FROM tblCustomer INNER JOIN tblCustomerType ON tblCustomer.CustomerTypeID = tblCustomerType.CustomerTypeID " & _
"WHERE tblCustomer.CustomerTypeID = [Forms]![Form3]![cboCustomerTypeFilter] " & _
"ORDER BY tblCustomer.Name"
End If


'StateType Selected
If Me.cboStateFilter & "" <> "" And Me.cboCustomerTypeFilter & "" = "" Then
    strRowSource = _
    "SELECT tblCustomer.ID, tblCustomer.Name, tblCustomer.StateID " & _
    "FROM tblCustomer INNER JOIN tblStates ON tblCustomer.StateID = tblStates.StateID " & _
    "WHERE tblCustomer.StateID = [Forms]![Form3]![cboStateFilter]" & _
    "ORDER BY tblCustomer.Name"
End If

Me.cboCustomerFilter.RowSource = strRowSource
Me.cboCustomerFilter.Requery

End Sub

Pampers
Keeping it simple can be complicated

 

[fneily]

And what does the user do when they select both, but then only wants just type or just state? An option group, the user selects just once, then can do either or both without reselecting or resetting.

 

[Pampers]

he or she just deletes the CustomerType or the State... and the code will rerun.



Pampers
Keeping it simple can be complicated