using Non Cascading dropdowns for a Search Tool

I have a client that insists on using a unique method of creating a filter for a listbox by selecting 1 or many combo(3) options.

here is a description of there requirements.

The expectation for the search tool is that a user can enter city or state or country or any combination of those. If the user would like to narrow the search results as described below ( country –search; state-search) then that should be an acceptable method. ALSO If the user would like to search for a city name ONLY, the expectation is that they should receive all locations that have that city name.

The user should not have any restrictions on how much or little data they must enter in the search tool. The tool should still produce all applicable location that meet those needs.

Please keep in mind that if a user would like to enter partial data in a field, that should also be tolerable.

Examples of SOME but not ALL  behaviors expected:

1.       User enters State = DF
a.      RESULTS: All states that have the code DF are returned (in this example there should be cities in two countries)
2.      User enters Country code = CAN, State = Cornwall
a.      RESULTS: Both Cornwall ON, CAN and Cornwall, PE, CAN display as a search result
3.      User enters City = M
a.      RESULTS: all locations with a city name starting with M are returned
4.      User enters city = paris; hits search; deletes paris and enters new information for searching
a.      RESULTS: “refresh” is not necessary to produce a successful search results
i.      “refresh” should be rarely used – if it is used at all ( I am assuming that there is an access restriction justification for this button)


Here is my current code.  The problem I am having is with the Selection of the City, currently removes the value if State and/or Country, so that a wildcard search can happen.  However, if the user selects the Country, then a city, the city currently removes the country. and same with state.  OR enters country, then state, then City - City resets Country & State   Is there a way to allow the user to enter any combination prior to selecting the Search button.

' Purpose   : Updates the City Selection List for further filtering
'---------------------------------------------------------------------------------------
Private Sub cmdSearch_Click()

Dim nName As String
Dim gStateID As Integer
Dim gCountryID As Integer
Dim strRS As String
Dim r As Integer
    nName = Nz(Forms![frmMainNav]![NavigationSubform].Form!cboCity.Value & "*")
    gStateID = Nz(Forms![frmMainNav]![NavigationSubform].Form!cboState.Column(0))
    gCountryID = Nz(Forms![frmMainNav]![NavigationSubform].Form!cboCountry.Column(0))

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

     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
        '1. All Null
        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" '& _
        '2. City not null, 2 null
        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
        
        '3 city & country not null, state null
        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
        
        '4. All values
        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
        
        '5. Country Not null, City/State Null
        Case Nz(Me.cboCity.Value, "") = "" And Nz(Me.cboState.Value, "") = "" And Nz(Me.cboCountry.Value, "") <> ""
            strWhere = " WHERE tblCountry.CountryRecID =" & gCountryID & ""
            strsql = strsql & strWhere
        
        '6. State not null, others null
        Case Nz(Me.cboCity.Value, "") = "" And Nz(Me.cboState.Value, "") <> "" And Nz(Me.cboCountry.Value, "") = ""
            strWhere = " WHERE tblState.StateRecID =" & gStateID & ""
            strsql = strsql & strWhere
        
        '7.City & state not null, country null
        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 & ""
            strsql = strsql & strWhere
      
         '8. State and country not null, city null
        Case Nz(Me.cboCity.Value, "") = "" And Nz(Me.cboState.Value, "") <> "" And Nz(Me.cboCountry.Value, "") <> ""
            strWhere = " WHERE tblState.StateRecID =" & gStateID & ""
            strWhere = strWhere & " AND tblCountry.CountryRecID =" & gCountryID & ""
            strsql = strsql & strWhere
      End Select

    strOrderBy = " ORDER BY CountryCode, StateCode, CityName asc"
    strsql = strsql & strOrderBy
    ' Debug.Print strsql
    Forms![frmMainNav]![NavigationSubform].Form![lstCityFilter].RowSource = strsql
    Forms![frmMainNav]![NavigationSubform].Form![lstCityFilter].Requery
'    For r = 0 To lstCityFilter.ListCount - 1
'        lstCityFilter.Selected(r) = True
'    Next r
'
 On Error GoTo 0
   Exit Sub

filterList_Error:

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

Open in new window

Private Sub cboCity_BeforeUpdate(Cancel As Integer)

    Forms![frmMainNav]![NavigationSubform].Form![cboCountry].Value = ""
    Forms![frmMainNav]![NavigationSubform].Form![cboState].Value = ""
    'set rowsource for search drop downs (resets values to ALL)
    Forms![frmMainNav]![NavigationSubform].Form![cboCountry].RowSource = "qryCountrylist"
    Forms![frmMainNav]![NavigationSubform].Form![cboState].RowSource = "qryStateList"

End Sub

Open in new window


Just to reiterate the client DOES NOT want Cascading type of combos.  I know I posted the question previously, however, the client still is happy on the current functionality.  I am working on creating a sample copy to post.
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.

Nick67Commented:
I had looked at your previous question, but OOBE
(MS may call that out of box experience--military in-law parlance operation overtaken by events)
Here was the sample I was working on.
It is not nearly complete, though, but may save some legwork.

I may get time to continue building the events.

User enters Country code = CAN, State = Cornwall
Note that Canada has no states, and Cornwall is a city not a province--did you mistype that?

Just to reiterate the client DOES NOT want Cascading type of combos.  The client wants what works with least hassle -- but that will likely be some type of cascade--even if they don't perceive it.
City.mdb
0
Karen SchaeferBI ANALYSTAuthor Commented:
Canada does have states(provinces) that can be a option for selection.  multiple countries have states/province that will treated as states.

where do you update the list?
0
Karen SchaeferBI ANALYSTAuthor Commented:
feel free to simplify.  but when I tried demo cascading combo - it was no go for the client.
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.

Nick67Commented:
Since the combos & listbox were the focus, I hand-populated the tables with data.
Each combo triggers a common afterupdate.
The Select Case logic is built.
Next is to build the SQL for the listbox in each case -- OOBE
0
Karen SchaeferBI ANALYSTAuthor Commented:
If I was to send a copy of the database what version should it be in?
0
Karen SchaeferBI ANALYSTAuthor Commented:
here is a better representation of my data for the combos.
testDataSample.xlsx
0
Karen SchaeferBI ANALYSTAuthor Commented:
what about using filter instead, just a thought?
0
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks NIck for  your time, however, I found the issue with my code darn extra parans, always get m.  turns out the sql for the state had an extra "(".

thanks again for your time.

K
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
Nick67Commented:
Glad you got 'er dun and sorry I wasn't able to help more timely

Nick67
0
Karen SchaeferBI ANALYSTAuthor Commented:
No problem have another issue if you wish to help check out my other post on Navigation subforms.  thanks again
0
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks to NIck for his time and efforts.
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.