Display combo value base on selection of another combo

What do I need to change to have combo 2 display the actual value based on selection of combo 1.

Rowsource of combo1 (cboState)
             strSQL = "SELECT tblCity.StateRecID, tblState.StateCode, tblState.StateName, tblcity.CountryRecID" & _
                " FROM tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID" & _
                " WHERE (((tblCity.CityName) Like [Forms]![frmHomePage]![cboCity] & '*'))" & _
                " GROUP BY tblCity.StateRecID, tblState.StateCode, tblState.StateName, tblCity.CityName, tblcity.CountryRecID"
        Forms!frmHomePage!cboState.RowSource = strSQL

Open in new window


Private Sub cboState_AfterUpdate()
 
   On Error GoTo cboState_AfterUpdate_Error
    gStateID = Me.cboState.Column(0)
    If IsNullOrBlank(Me.cboCity) = False _
        Or IsNullOrBlank(Me.cboCountry) = False Then
        Forms!frmHomePage!cboCity.RowSource = "SELECT tblCity.CityName" & _
                        " FROM tblCountry RIGHT JOIN (tblState RIGHT JOIN tblCity ON tblState.StateRecID = tblCity.StateRecID) ON" & _
                                " tblCountry.CountryRecID = tblCity.CountryRecID" & _
                        " WHERE (tblCity.StateRecid)= " & gStateID & " " & _
                        " ORDER BY CityName asc"
    
        Forms!frmHomePage!cboCountry.RowSource = "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, tblCity.StateRecID" & _
                            " ORDER BY tblCountry.CountryCode asc"
         Forms!frmHomePage!cboCountry.value = Forms!frmHomePage!cboState.Column(1)
    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

Open in new window


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.

K
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Karen SchaeferBI ANALYSTAuthor Commented:
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
Nick67Commented:
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

Open 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***
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Karen SchaeferBI ANALYSTAuthor Commented:
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.  http://www.experts-exchange.com/Database/MS_Access/Q_28678542.html#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
Nick67Commented:
I need to be able to handle all variances in the selection processes.
Ditch IF Then
Select Case True is a far more elegant way to handle complex logic.
If you make a sample, be aware they I cannot deal with accdb's
Select Case True
    Case nz(Me.cboCity.Value,"") = "" And nz(Me.cboState.Value,"") = ""  And nz(Me.cboCountry.Value,"") = ""
        'Virgin territory, no values
        'set default rowsources and requery
    Case InStr(Me.cboCity.Value,"*") > 0
        'I learned this just yesterday -- I think it is much simpler and elegant than LIKE comparison!
        ' wildcard search, do appropriate things
    Case nz(Me.cboCity.Value,"") <> "" And nz(Me.cboState.Value,"") = ""  And nz(Me.cboCountry.Value,"") = ""
        'we have a city, but nothing else
        'set  rowsources for country an possibly state and requery 

'and so on

    case else
         'default situation when all comparisons fail
End Select 

Open in new window


I think I would either declare a bunch of Booleans and then execute code blocks after the Select Case or  call whole separate procedures to set each combobox in the CASEs.  Otherwise you are in danger of having a huge and complex sub doing all this that will be hard to maintain.

I get no records
Whatever SQL is returning no records, get that MsgBox'd.
Then you can SEE the syntax Access is passing to the query engine to look for mistakes :)
First troubleshooting step with gnarly VBA  SQL!

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:
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

Open in new window


It is coming across as id and note wildcard search so I am getting type mismatch issue.
Nick67Commented:
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?
Karen SchaeferBI ANALYSTAuthor Commented:
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
Karen SchaeferBI ANALYSTAuthor Commented:
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
Nick67Commented:
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?
Karen SchaeferBI ANALYSTAuthor Commented:
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

Open in new window

Nick67Commented:
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?
Karen SchaeferBI ANALYSTAuthor Commented:
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.
Karen SchaeferBI ANALYSTAuthor Commented:
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.
Nick67Commented:
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!
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks so much you really helped.  Thanks for all the time you put into this.

K
Nick67Commented:
No Problem
Glad to have helped you over the years.

Nick67
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.