Display combo value base on selection of another combo

I generally simply make the RowSource of the successive combo boxes refer to the actual control value, rather than writing the SQL at run-time. so the RowSource for each of these would look like:

For cboCity:
SELECT tblCity.CityName FROM tblCountry
RIGHT JOIN (tblState
RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID)
ON tblCountry.CountryRecID = tblCity.CountryRecID
WHERE tblCity.StateRecid = Forms!frmHomePage.cboState
ORDER BY CityName asc
   
For Country:
SELECT Country.CountryRecID, Country.CountryCode, Country.CountryName
FROM tblCountry as Country
RIGHT JOIN tblCity ON Country.CountryRecID = tblCity.CountryRecID
WHERE tblCity.StateRecid = Forms!frmHomePage.cboState
GROUP BY Country.CountryRecID, Country.CountryCode, Country.CountryName, tblCity.StateRecID
ORDER BY Country.CountryCode asc

Then, in your AfterUpdate event, I would use:

Private Sub cboState_AfterUpdate

    me.cboCity.Requery
    me.cboCity = NULL
    me.cboCountry.Requery
    me.cboCountry.Null

End Sub

Thanks for the input, however, I need to have the sql at runtime due to the variable changes of each of the combos depending on which is selected.  If the user select RESET then the rowsource reverts back the to basic query for each combo.  If the user Selects or inputs the City, the state and/or the country combo will be based on the City selection (Which may be a Wildcard search), if the user Selects City, then Country( the country may have more than one option).  Example

City - Paris

My current list of country indicates there are 2 Paris, 1 in Canada and 1 in France.

Hence the Country should display Canada & France.

The user will have the option to further narrow the search criteria.

But the code behind the Country can effect the State and City list.  So how do I handle if the City has data and the user selects Country = CANADA, that it will not refresh the City list, allowing them to complete the search criteria. to update the listbox filter.rowsource?

K

What do I need to change to have combo 2 display the actual value based on selection of combo 1.
This isn't your first rodeo, so I assume that you know how to make combos cascade.
You make the WHERE clause of the second combo depend upon the selected value of the first, and you issue a requery in the AfterUpdate event of the first one.
I'm not seeing that .Requery statement, though
combo 2 display the actual value based on selection of combo 1.
There's, and rightfully so, no combo1 and 2 in the code.
In fact, there's three combos in there.
1.
Me.cboCity.Requery
Me.cboCountry.Requery
Are going to be required.
2.
Also, I need to do a record count on the results of the combo and if the count = 1 then display the actual value, if >1 then combo.dropdown becomes the option.
Then you'll need recordset code, and I am not seeing any of that here.

Dim SQL as string
Dim rs as recordset
'ampersands are hell enough
'do yourself a favor and skip _
SQL = "SELECT tblCity.CityName" 
SQL = SQL & " FROM tblCountry RIGHT JOIN (tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID) ON" SQL = SQL & "  tblCountry.CountryRecID = tblCity.CountryRecID"
SQL = SQL & " WHERE (tblCity.StateRecid)= " & gStateID
SQL = SQL & " ORDER BY CityName asc"

Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset,dbSeeChanges)
Case rs.RecordCount
    Case 0
         msgbox "Oops, no good value found!"
   Case 1
         me.cboCity.RowSource = SQL
         me.cboCity.Requery
         me.cboCity.Value = rs![tblCity.CityName]
    Case Else
         me.cboCity.RowSource = SQL
         me.cboCity.Requery
         me.cboCity.dropdown
End Select

'and now the other one

Select allOpen in new window

3.
If IsNullOrBlank(Me.cboCity) = False _
        Or IsNullOrBlank(Me.cboCountry) = False Then
There's lots of possibilities there.
I don't see an ELSE clause!

***K
Your post came in while I was composing this***

I need to be able to handle all variances in the selection processes.

1.  User can select either City or State or Country.
2. If user enters a wildcard type of search w/* to the right of data for city name, then return all applicable Country names to use as Country.rowsource.
3.  If user Selects Country First, then requery City, except when City already contains data.
4.  if Country has applicable states, then update State, except when State already contains data.
5.  Also if the returning value of Country or State record count equals 1 then update the combo to display actual values.  if Record >1 then have the Country DropDown displayed.
6.  Update Listbox with results on click of Search button.

Also the return values for the listbox must be sorted ascending by Country, State, then City.

I am preparing a sample of my database that might help.

also see previous posting on ee.  https://www.experts-exchange.com/questions/28678542/List-box-does-not-like-rowsource.html?anchorAnswerId=40796785#a40796785

Partial success, however, unable to update the search when City has been entered and then I need to select the country after the list box has been updated.  I get no records.
Sample.zip

I found in my search for an answer to prevent numerous case statements, however, I am having an issue with the cbocity..

Private Sub cmdSearch_Click()
    Dim strRS As String

    If Me.cboCountry.ListIndex >= 0 Then strRS = strRS & " AND CountryRecID = " & gCountryID

    If Me.cboState.ListIndex >= 0 Then strRS = strRS & " AND stateRecID = " & gStateID
    
    If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND InStr(Me.cboCity.Value," * ") > 0"
    
 
    'IF FILTER FOUND ADD 'WHERE' AND REMOVE LEADING 'AND' (IF NOT FOUND IT WILL REMAIN A NULL STRING)
    If strRS <> "" Then strRS = " WHERE " & Mid(strRS, 6)

    'ADD FILTER TO QUERY
    strRS = "SELECT CityRecID, CityName, StateCode as State, CountryCode As Country" & _
            " FROM qryFilterListData " & strRS & ""

    'ADD ORDER BY CLAUSE
    strRS = strRS & " GROUP BY CityRecID, CityName, StateCode as State, CountryCode as Country"
    strRS = strRS & " ORDER BY CountryCode, StateCode, CityName"

   Forms![frmHomePage]![lstCityFilter].RowSource = strRS

Select allOpen in new window

It is coming across as id and note wildcard search so I am getting type mismatch issue.

prevent numerous case statements
No argument there.  That's the elegance of Select Case TRUE
You can have a single case statement.
You order the various CASEs from most specific to least, because the first CASE that is true gets executed, and then the structure exits.
This is far easier to write, debug, test and maintain than nested IF THENs with a whack of Boolean operators thrown in.
You can throw in breakpoints and watch as each CASE gets evaluated, and see if you got it right.

If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND InStr(Me.cboCity.Value," * ") > 0"

I like this construction InStr(Me.cboCity.Value," * ") > 0 very much better as a Boolean test to
me.CboCity.Value Like SomeStringValue with or without some wildcards -- but what are you using it for here?

InStr(Me.cboCity.Value," * ") This is searching Me.cboCity.Value for " * " and will return an integer if it finds it, zero if it does not find it.
InStr(Me.cboCity.Value," * ") > 0 This is a Boolean, either true or false
So this
If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND InStr(Me.cboCity.Value," * ") > 0"
will either be whatever strRS is with "AND TRUE" or "AND FALSE" tacked on the end.
Does that make sense in context?

Yes, however I want to return the wildcard like state results.  ie. Paris*.

How do I modify the

If Me.cboCity.ListIndex >= 0 Then strRS = strRS & " AND InStr(Me.cboCity.Value," * ") > 0"

So that it will be included in the where statement as a wilcard search?

K

Sorry to be so wishy washy,   I am just look for the simpliest way to get these combos working - did you get a chance to look as my sample db?

K

did you get a chance to look as my sample db?
It was an accdb, and I cannot deal with those.  Sorry.
Being the dev, I run Access 2003, so that everything uplevel functions nicely.
Besides, for what I do A2003 is a far superior platform.
We run Access 2003, 2010 and 2013 concurrently, but my machine is A2003.
Hence, no accdb.

So what is in strRS  that appending AND TRUE or AND FALSE to it would make sense!?!

And note " AND InStr(Me.cboCity.Value," * ") > 0"
That is looking for [space][asterisk][space] in the Me.cboCity.Value
Will you really have a space before and after the asterisk?

This is what I have got working so far, thanks to you - you help make the light bulb go off.  THANKS SO MUCH.

Private Sub cmdSearch_Click()

Dim nName As String
Dim gStateID As Integer
Dim gCountryID As Integer
    Dim strRS As String
    nName = Nz([Forms]![frmHomePage]!cboCity.value & "*")
    gStateID = Nz([Forms]![frmHomePage]!cboState.Column(0))
    gCountryID = Nz([Forms]![frmHomePage]!cboCountry.Column(0))
'Debug.Print nName, gStateID, gCountryID

    ' Filter the list box appropriately based on the combo box selection(s)
   On Error GoTo filterList_Error

     strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblState.StateCode as State, tblCountry.CountryCode as Country, tblCity.StateRecID, tblCity.CountryRecID" & _
           " FROM tblState RIGHT JOIN (tblCountry RIGHT JOIN tblCity ON" & _
                " tblCountry.CountryRecID = tblCity.CountryRecID) ON tblState.StateRecID = tblCity.StateRecID" '& _

    Select Case True
        Case Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") = "" And Nz(Me.cboCountry.value, "") = ""
            strSQL = "SELECT tblCity.CityRecID, tblCity.CityName, tblState.StateCode as State, tblCountry.CountryCode as Country, tblCity.StateRecID, tblCity.CountryRecID" & _
                  " FROM tblState RIGHT JOIN (tblCountry RIGHT JOIN tblCity ON" & _
                       " tblCountry.CountryRecID = tblCity.CountryRecID) ON tblState.StateRecID = tblCity.StateRecID" '& _

        Case Nz(Me.cboCity.value, "") <> "" And Nz(Me.cboState.value, "") = "" And Nz(Me.cboCountry.value, "") = ""
            strWHERE = " WHERE ((tblCity.CityName) Like " & Chr(39) & nName & Chr(39) & ")"
            strSQL = strSQL & strWHERE
        
        Case Nz(Me.cboCity.value, "") <> "" And Nz(Me.cboState.value, "") = "" And Nz(Me.cboCountry.value, "") <> ""
             strWHERE = " WHERE ((tblCity.CityName) Like " & Chr(39) & nName & Chr(39) & ")"
            strWHERE = strWHERE & " AND ((tblCountry.CountryRecID) =" & gCountryID & ")"
            strSQL = strSQL & strWHERE
        
        Case Nz(Me.cboCity.value, "") <> "" And Nz(Me.cboState.value, "") <> "" And Nz(Me.cboCountry.value, "") <> ""
            strWHERE = " WHERE ((tblCity.CityName) Like " & Chr(39) & nName & Chr(39) & ")"
            strWHERE = strWHERE & " AND ((tblState.StateRecID) =" & gStateID & ")"
            strWHERE = strWHERE & " AND ((tblCountry.CountryRecID) =" & gCountryID & ")"
            strSQL = strSQL & strWHERE
        Case Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") = "" And Nz(Me.cboCountry.value, "") <> ""
            strWHERE = " WHERE ((tblCountry.CountryRecID) =" & gCountryID & ")"
            strSQL = strSQL & strWHERE
        Case Nz(Me.cboCity.value, "") = "" And Nz(Me.cboState.value, "") <> "" And Nz(Me.cboCountry.value, "") = ""
            strWHERE = " WHERE ((tblState.StateRecID) =" & gStateID & ")"
            strSQL = strSQL & strWHERE
      End Select

    strOrderBy = " ORDER BY CountryCode, StateCode, CityName asc"
    strSQL = strSQL & strOrderBy
    Debug.Print strSQL
    Forms![frmHomePage]![lstCityFilter].RowSource = strSQL
   ' Forms![frmHomePage]![lstCityFilter].Requery
   On Error GoTo 0
   Exit Sub

filterList_Error:

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

Select allOpen in new window

No problem.
Sometimes you need another set of eyes.
We've strayed from the original cascading combos, though, to the using of the values of those in creating a ListBox rowsource.
Your Select Case True is beginning to look good.
You have 3 combos, with 2 conditions (null or has a value)
2^3 = 8
So there should be 8 Cases
I see only 6

1.All Null
2. City not null, 2 null
3. city & country not null, state null
4. All values
5. Country not null, other null
6. State not null, others null
You are missing
7. City & state not null, country null
8. State and country not null, city null

Even if you have 100% of the probabilities, have a CASE ELSE block
in code maintenance, it signals what you intend in the worse-case scenario and can really help you or your successor understand what is important about the form.

Select case true beats the hell out of trying to use if then and AND OR to try to cover 8 possibilities for readability, doesn't it!

I see you have the requery commented out.  You will likely need it.
Generally, to go along with that you need code to de-select any choices that were made previously.
They either stay selected in a new context (bad) or are outside the number of new choices (BANG!)
Is this a single select or multi-select listbox?

Multi Selection and I will remove all commented out temporary code.  Didn't want to remove until I am sure we are on the right path.,

1.All Null - Not need handled on load and on the reset button
2. City not null, 2 null
3. city & country not null, state null
4. All values
5. Country not null, other null
6. State not null, others null
You are missing
7. City & state not null, country null
8. State and country not null, city null

also any Ideas on how do I get the actual value displayed in combos if the  recordcount =1 and/or display the drop down recordcount > 1

Thanks for the follow thru.

Forms!frmHomePage!cboState.RowSource = strSQL
    Set rs = curDB.OpenRecordset(strSQL)
        If rs.RecordCount = 1 Then
            Me.cboState.value = Me.cboState.value
        Else
            Me.cboState.Dropdown
        End If
    rs.Close
    Set rs = Nothing

Still not displaying the actual value in combo.

Me.cboState.value = Me.cboState.value
By itself, does that look like it makes sense :)

Me.cboState.value = rs!SomeFieldThatIsTheComboBoxesIndexField
Much better!

Note that Access will only permit one concurrent dropdown, and you may need to SetFocus the control first!

Thanks so much you really helped.  Thanks for all the time you put into this.

K

No Problem
Glad to have helped you over the years.

Nick67