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.
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
but they are not happy with click the button each time they want to start a new searchThere 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.
ASKER
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:
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
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 SubHow 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
ASKER
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]!c
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
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]!c
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
Glad you got it working.
ASKER
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.