Nuance of Multiple Combos (Selected in various order) setting the values of other combos

I have form containing 3 combos (City, State, and Country)

the user can select any of the combos in any order and the system must update the rowsources of the other combos to match the selected combos and/or validate if exiting combo value is not null and that the rowsource is correct based on active entry.

Enter new search w/o selecting reset button

      User previously entered criteria, then wants to enter new city, problem arises that the new data is not in current list?  Where do I need to place the code to clear the list on start of entry of new selection?  When they enter the name – it comes back with “Item not in list”, since the list has not been updated as of yet.  I tried to clear the row source on BeforeUpdate and OnChange with little success.

D.      Please review:
E.      Below is the various nuances I have determined so far:
F.      If User Select of City or enters a wildcard search of part of a city name First
G.      Validate State = null
a.      Set Rowsource =List of States pertinent to the current City selection.
H.      Validate State <> null
a.       Compare value of State whether it is included in the current selections of City.
i.      If True  then do nothing to state
b.      If not equal to the Current city, State combination
i.      RESET Rowsource to = List of States pertinent to the current City selection.
I.      Validate Country = null
a.      Set Rowsource =List of Country pertinent to the current City selection.
J.      Validate Country <> null
a.       Compare value of Country is included in the current selections of City.
i.      If True  then do nothing to state
b.      If not equal to the current City, Country combination
i.      RESET Rowsource to = List of Country pertinent to the current city selection.
K.      Upon going on to the next selection where the combos rowsource still contain the previous rowsources data and the user may want to enter a new city Name(or part of a Name) prior to changing the existing values in the State and/or Country.  When they enter the name – it comes back with “Item not in list”, since the list has not been updated as of yet.  I tried to clear the row source on BeforeUupdate and OnChange with little success.

L.      If user Select of State First
M.      Validate City = null
a.      Set Rowsource =List of City pertinent to the current State selection.
N.      Validate City <> null
a.       Compare value of City whether it is included in the current selections of State.
i.      If True  then do nothing to City
b.      If not equal to the Current State
i.      RESET Rowsource to = List of City pertinent to the current State selection.
O.      Validate Country = null
a.      Set Rowsource =List of Country pertinent to the current State selection.
P.      Validate Country <> null
a.       Compare value of Country is included in the current selections of State.
i.      If True  then do nothing to state
b.      If not equal to the Current city, Country combination
i.      RESET Rowsource to = List of Country pertinent to the current State selection.

Q.      If user Select of Country First
R.      Validate City = null
a.      Set Rowsource =List of City pertinent to the current Country selection.
S.      Validate City <> null
a.       Compare value of City whether it is included in the current selections of Country.
i.      If True  then do nothing to City
b.      If not equal to the Current State
i.      RESET Rowsource to = List of City pertinent to the current Country selection.
T.      Validate State = null
a.      Set Rowsource =List of State pertinent to the current Country selection.
U.      Validate State <> null
a.       Compare value of State is included in the current selections of Country.
i.      If True  then do nothing to Country
b.      If not equal to the Current city,  State combination
i.      RESET Rowsource to = List of State pertinent to the current Country selection.

Private Sub cboCity_BeforeUpdate(Cancel As Integer)
'
'End Sub
'
'Private Sub cboCity_Change()

    If Nz(Me.cboCountry.value, "") <> "" Then
        Forms![frmHomePage]![cboCountry].value = ""
        Forms![frmHomePage]![cboCountry].RowSource = "qrycourntylist"
    ElseIf Nz(Me.cboState.value, "") <> "" Then
     
        Forms![frmHomePage]![cboState].value = ""
        Forms![frmHomePage]![cboState].RowSource = "qryStateList"
        Forms![frmHomePage]![cboCountry].RowSource = "qrycourntylist"
        Forms![frmHomePage]![lstCityFilter].RowSource = "" 'SELECT CityRecID, CityName, StateCode as State , CountryCode as country FROM qryFilterListData "
        Forms![frmHomePage]![lstCityFilter].Requery
    ElseIf Nz(Me.cboCountry.value, "") = "" And Nz(Me.cboState.value, "") = "" Then
        Exit Sub
    End If
End Sub

Private Sub cboCountry_AfterUpdate()
Dim nName As String
Dim gStateID As Integer
Dim gCountryID As Integer
Dim curDB As Database
Dim rs As Recordset
   

   On Error GoTo cboCountry_AfterUpdate_Error

    Set curDB = CurrentDb()
    nName = Nz([Forms]![frmHomePage]!cboCity.value & "*")
    gStateID = Nz([Forms]![frmHomePage]!cboState.Column(0))
    gCountryID = Nz([Forms]![frmHomePage]!cboCountry.Column(0))
    
    'Updates City Combo based on Country selection
    If Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") = "" Then
        strSQL = "SELECT tblCity.CityName" & _
                    " FROM tblCity" & _
                    " WHERE (((tblCity.CountryRecID) = " & gCountryID & "))" & _
                    " GROUP BY tblCity.CityName" & _
                    " Order By tblCity.cityname asc"
        Forms!frmHomePage!cboCity.RowSource = strSQL
        Forms![frmHomePage]![lstCityFilter].Requery
    End If
    
    'Updates State Combo based on Country selection
    If Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") = "" Then
        strSQL = "SELECT tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
                    " FROM tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID" & _
                    " WHERE (((tblCity.CountryRecID) = " & gCountryID & "))" & _
                    " GROUP BY tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
                    " ORDER BY tblState.StateCode, tblState.StateName asc"
        Forms!frmHomePage!cboState.RowSource = strSQL
        Forms![frmHomePage]![lstCityFilter].Requery
    End If

    'Updates State Combo based on Country selection
    If Nz(Me.cboCity.value, "") <> "" And Nz(Me.cboState.value, "") = "" Then
        strSQL = "SELECT tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
                    " FROM tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID" & _
                    " WHERE (((tblCity.CountryRecID) = " & gCountryID & "))" & _
                    " GROUP BY tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
                    " ORDER BY tblState.StateCode, tblState.StateName asc"
        Forms!frmHomePage!cboState.RowSource = strSQL
        Forms![frmHomePage]![lstCityFilter].Requery
    End If

   On Error GoTo 0
   Exit Sub

cboCountry_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboCountry_AfterUpdate of VBA Document Form_frmHomePage"
    
End Sub

Private Sub cboCountry_Change()
    If Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") = "" Then
        Forms![frmHomePage]![cboState].value = ""
        Forms![frmHomePage]![cboCity].value = ""
        Forms![frmHomePage]![cboState].RowSource = "qryStateList"
        Forms![frmHomePage]![cboCity].RowSource = "qrycitylist"
        Forms![frmHomePage]![lstCityFilter].RowSource = "" 'SELECT CityRecID, CityName, StateCode as State , CountryCode as country FROM qryFilterListData "
        Forms![frmHomePage]![lstCityFilter].Requery
    End If
End Sub

'
Private Sub cboState_AfterUpdate()
 
Dim nName As String
Dim gStateID As Integer
Dim gCountryID As Integer
Dim curDB As Database
Dim rs As Recordset
   
   On Error GoTo cboState_AfterUpdate_Error

    Set curDB = CurrentDb()
    nName = Nz([Forms]![frmHomePage]!cboCity.value & "*")
    gStateID = Nz([Forms]![frmHomePage]!cboState.Column(0))
    gCountryID = Nz([Forms]![frmHomePage]!cboCountry.Column(0))
    
    'Updates City Combo based on State selection
    If Nz(Me.cboCity.value, "") = "" And Nz(Me.cboCountry.value, "") <> "" Then
        strSQL = "SELECT tblCity.CityName" & _
                    " FROM tblCity" & _
                    " WHERE (((tblCity.StateRecid) = " & gStateID & "))" & _
                    " GROUP BY tblCity.CityName" & _
                    " Order By tblCity.cityname asc"
        Forms!frmHomePage!cboCity.RowSource = strSQL
        Forms![frmHomePage]![lstCityFilter].Requery
    End If
    
    'Updates Country Combo based on State selection
    If Nz(Me.cboCity.value, "") = "" And Nz(Me.cboCountry.value, "") = "" Then
        strSQL = "SELECT tblCountry.CountryRecID, tblCountry.CountryCode, tblCountry.CountryName" & _
                " FROM tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID" & _
                " WHERE (((tblCity.StateRecid) = " & gStateID & "))" & _
                " GROUP BY tblCountry.CountryRecID, tblCountry.CountryCode, tblCountry.CountryName" & _
                " ORDER BY tblCountry.CountryCode asc"
        Forms!frmHomePage!cboCountry.RowSource = strSQL
        Forms![frmHomePage]![lstCityFilter].Requery
    End If

   On Error GoTo 0
   Exit Sub

cboState_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboState_AfterUpdate of VBA Document Form_frmHomePage"

End Sub
Private Sub cmdReset_Click()

    On Error GoTo HandleError
    Forms![frmHomePage]![cboCountry].value = ""
    Forms![frmHomePage]![cboState].value = ""
    Forms![frmHomePage]![cboCity].value = ""
    'Forms![frmHomePage]![cboState].Visible = True
   
    Forms![frmHomePage]![cboCountry].RowSource = "qryCountrylist"
    Forms![frmHomePage]![cboState].RowSource = "qryStateList"
    Forms![frmHomePage]![cboCity].RowSource = "qrycitylist"
    Forms![frmHomePage]![lstCityFilter].RowSource = "SELECT CityRecID, CityName, StateCode as State , CountryCode as country FROM qryFilterListData "
    Forms![frmHomePage]![lstCityFilter].Requery
    Forms![frmHomePage]![Location Detail].Form.RecordSource = ""
    Forms![frmHomePage]![Location Detail].Form.Visible = False

HandleExit:
   
   Exit Sub
HandleError:
   MsgBox Err.Number & " - " & Err.Description
   Resume HandleExit

End Sub

Open in new window

Karen SchaeferBI ANALYSTAsked:
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:
When you have hierarchical data, it doesn't make sense to enter it out of order.  As you can see by your list of nuances, it gets very complicated.  If you want to do it, you'll need three sets of queries for each and the combos will only be dependent on the one chosen as the filter.
1. Unfiltered.
2. Filtered by comboX
3. Filtered by comboY

comboX and comboY would be different for each combo.
City:
a. Unfiltered
b. Filtered by State
c. Filtered by Country
State:
a. Unfiltered
b. Filtered by City
c. Filtered by Country
Country:
a. Unfiltered
b. Filtered by City
c. Filtered by State

Your clear button would set all the RowSources to the unfiltered queries.
Me.cboCity.RowSource ="qUnfilteredCity"
Me.cboState.RowSource = "qUnfilteredState"
Me.cboCountry.RowSource = "qUnfilteredCountry"

The afterUpdate events of each combo would set the other combos to be filtered by "it".
So, the AfterUpdate event of City:
Me.cboState.RowSource = "qFilterStateByCity"
Me.cboCountry.RowSource = "qFilterCountryByCity"

AfterUpdate event of State:
Me.cboCity.RowSource = "qFilterCityByState"
Me.cboCountry.RowSource = "qFilterCountryByState"

AfterUpdate event of Country:
Me.cboCity.RowSource = "qFilterCityByCountry"
Me.cboState.RowSource = "qFilterStateByCountry"
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
Karen SchaeferBI ANALYSTAuthor Commented:
this is what I have so far, unfortunately, my client wants to be all search by any of the options.

I tried very hard to convince them to go the hierarchical approach, but some user may want to review all data pertinent to just a country, of be able to drill down further, same with City - the city drop down my return multiple country names for the same city name.  ie (Paris)  there is a Paris, On, Canada and Paris, France.  Each drop down starts off with the basic list of Cities not related to any country, list of all states not related to any country, and list of all countries in the system.  But if the user selects one of the combos they might want to be able to further limit the results, hence the many different nuances or display all results for the selected combo only.

Also need to validate if the other combos already contain data, if so does it match the other list of selection the user just made - if other combo contains data you do not want to override previous selections for the narrowing of the search.

I currently have a reset button to clear the combo selections, but they are not happy with click the button each time they want to start a new search,  How do I allow them to enter new selection and update the combos accordingly, if what data they are entering isn't included in the current combo rowsorces?

These results upon clicking the Search will built a List box, for further selection to display the detail.
0
PatHartmanCommented:
When you use hierarchical combos, it is quite logical to not make selections in lower levels.  Therefore, you can chose a country but not a state.

but they are not happy with click the button each time they want to start a new search
There is no way for you to know when a combo has a selected value if it is left over from a previous filter or is part of the current filter.  That is why the clear button MUST be used to start each search.  The question to ask is "If you want a to control b and b to control a and a to control c and c to control a and b to control c and c to control b, how can I tell what is what?"  At least with the reset button, you can tell what hasn't been touched.  Therefore, if a combo is empty, you know you can apply a filter and if it is not, you know you cannot apply a filter because it has been used to filter this combo.

I have done this type of filtering with two combos and you can work it out.  It is the third combo that causes the problem without the reset button to clear the filter.

Bottom line is money.  How important to them is this "flexibility"?  You can make three cascading combos work in a matter of minutes.  I'm going to guess that you've been at this for hours if not longer.  How about offering them an option.  You can have an option group that toggles between "hierarchical" and "independent".   Then your code could work normally if they click hierarchical and not require a reset between searches.  The independent flag can make it work the way I suggested.  I understand that what I suggested still isn't quite what they asked for but you can implement it in a few minutes and they might be OK with it as a compromise.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
ok after reexamining this process it was decided to simplify the process.  What do you know about using combo.listcount to determine if the filtered record has happened?

Found this code:
Public Sub SizeCustomerList() 
 
 Dim ListControl As Control 
 
 Set ListControl = Forms!Customers!CustomerList 
 With ListControl 
 If .ListCount < 8 Then 
 .ListRows = .ListCount 
 Else 
 .ListRows = 8 
 End If 
 End With 
 
End Sub

Open in new window


How would you recommend proceeding with the existing code I gave earlier?  would if be possible to give me an example based on my code?

K
0
Karen SchaeferBI ANALYSTAuthor Commented:
thanks Pat,

Here is my solution I was able thanks in part to you.

Private Sub cboCity_AfterUpdate()

Dim nName As String
Dim gStateID As Integer
Dim gCountryID As Integer
Dim curDB As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim strSQL As String
Dim strSQL1 As String

   On Error GoTo cboCity_AfterUpdate_Error

    Set curDB = CurrentDb()
    nName = Nz([Forms]![frmHomePage]!cboCity.value & "*")
   

    strSQL = "SELECT tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
            " FROM tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID"
       
        If Not IsNull(cboState) Then _
        strSQL = strSQL & " WHERE ((tblCity.CityName) Like " & Chr(39) & nName & Chr(39) & ")" & _
            " GROUP BY tblState.StateRecID, tblState.StateCode, tblState.StateName" & _
            " ORDER BY tblState.StateCode"
   
    Me.cboState.RowSource = strSQL
        If IsNull(cboCountry) And Not IsNull(cboState) Then Me.cboState.SetFocus

    strSQL1 = "SELECT tblCountry.CountryRecID, tblCountry.CountryCode, tblCountry.CountryName" & _
            " FROM tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID"
         
        If Not IsNull(cboCountry) Then _
        strSQL1 = strSQL1 & " WHERE ((tblCity.CityName) Like " & Chr(39) & nName & Chr(39) & ")" & _
                    " GROUP BY tblCountry.CountryRecID, tblCountry.CountryCode, tblCountry.CountryName" & _
                    " ORDER BY tblCountry.CountryCode asc"

    Me.cboCountry.RowSource = strSQL1
    'If IsNull(cboState) And Not IsNull(cboCountry) Then Me.cboCountry.SetFocus
       
        If IsNull(cboState) And Not IsNull(cboCountry) Then
            Me.cboState.SetFocus
        ElseIf IsNull(cboCountry) And Not IsNull(cboState) Then
            Me.cboCountry.SetFocus
        ElseIf Not IsNull(cboCity) And Not IsNull(cboCountry) Then
            Me.cmdSearch.SetFocus
        End If


     On Error GoTo 0
   Exit Sub

cboCity_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboCity_AfterUpdate of VBA Document Form_frmHomePage"
End Sub
0
PatHartmanCommented:
Glad you got it working.
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.