UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Cascading Combo Boxes    
Cascading Combo Boxes

Related Content:
    Combo Box
    Cascading Combo Boxes
    
Cascade Combo - Leaving Null Values


Contents

Cascading Combo Boxes (a.k.a. Synchronized Combo Boxes)



Definition

Cascading Combo Boxes are a series of 2 or more Combo Boxes in which each Combo Box is filtered according to the previous Combo Box.

Quick Example

A simple example of where you could use Cascading Combo Boxes is a property address. Each address consists of a country, state (region/provence) and city. Three Combo Boxes will be used in this example. The first Combo Box, State, has a RowSource which lists all distinct countries. When a user selects a country, the State Combo Box and the City Combo Box are both filtered to only the distinct states and cities which reside within the selected country. This example is based on a normalized structure of three tables:

  • tblCountries:
    • CountryID - Primary Key - Autonumber
    • Country - Text
  • tblStates:
    • StateID - Primary Key - Autonumber
    • CountryID - Foreign Key - Long
    • StateName - Text
    • StateAbbrev - Text (short 2 or 3 letter abbreviation)
  • tblCities:
    • CityID - PrimaryKey - Autonumber
    • CountryID - Foreign Key - Long
    • StateID - Foreign Key - Long
    • CityName

How-To:
Using the example above, the first Combo Box, Country, would simply have its RowSource property set to tblCountries, and its RowSourceType property set to Table/Query.

The State Combo Box would have as its RowSource property SQL for a query which places a WHERE condition on the Foreign Key, CountryID, to equal the Country Combo Box, like this: "SELECT * FROM tblStates WHERE CountryID =" & Forms![NameOfForm]![cboCountry].Value (cboCountry is the name of the country Combo Box). Its RowSourceType property would be set to Table/Query.

The City Combo Box would have as its RowSource property SQL for a query which places a WHERE condition on the Foreign Key, StateID, to equal the State Combo Box, like this: "SELECT * FROM tblCities WHERE StateID =" & cboState (cboState is the name of the state Combo Box). Its RowSourceType property would be set to Table/Query.

In order to have the State and City Combo Boxes updated to reflect changes in the value contained in the Country Combo Box, it's necessary to invoke the Requery method for the two Combo Boxes in a procedure called from the AfterUpdate event of the Country Combo Box.

Also note that if the user is allowed to browse through existing records, you will need to invoke the Requery method for the two Combo Boxes from the form's On Current event - this will make sure the dependent Combo Boxes are up to date each time the user navigates to a record.

NOTICE:

And always avoid using ANY special characters (even spaces) in your object, control and field names.
The characters to be most avoided are: ! @ # $ % ^ & * ( ) ? > < + = ' "; : - ~ `\ | [ ] { }
Otherewise, your SQL constructs may fail. (See: Naming Conventions )

Detailed Examples

In a more detailed example, still using a normalized table structure, we will demonstrate how the normalized data flow of the cascaded information may not best suit the interface at data entry time, and how to overcome that. This detailed emaple will be using Country, State and City; which is the normalized hierarchy of those particular groupings, But normally, the data entry order would be City, State and then Country; which by data hierarchy standards is backwards.

First we'll demonstrate the data hierarchy method, expose the concepts, tables, queries, combobox RowSources, how to provide for nulls when the synchronization chain in the cascade is broken, form layout and finally the VBA code to manage the behavior. Also demonstrated are the forms for entering the data into the reference tables for "Countries", "States" and "Cities".

Then in the second part of the detailed example, we'll demonstrate those same exposures, using the same data, changing only the form layout, one query and some of the VBA code behind the form.

Finally, there is an attachment with three files, one in each Access 2000, Access 2003 and Access 2007 formats; with all the tables, relationship diagram, queries, forms and the code behind them.

THE CONCEPT:
The concept is the same as in the Quick Example - synchronize two or more comboxes so that as they are filled in, the next combobox in the sequence is filtered to display records based on a selection from the previous combobox.

What is not covered, is the handling of "Not In List" issues, so all comboboxes herein are set to "Limit to List = Yes".

THE TABLES:
The images below show the tables and relationship diagram. Note that the Country, State and City tables are prefixed with "ref"; this is just a naming convention to point out that they are 'reference' or 'lookup' tables. Notice too, that each tble uses an autonumber field as the Primary Key. These Primary Keys become Foreign Keys when collected into the other tables

Image:tables.png


And in design view . . . . see here how the foreign key fields are not "Required", thus allowing for gaps in the data while it is being collected and entered..

Image:tblDesign.png


This relationship arrangement "appears" to not allow Null or Empty values in the CountryID or StateID of the refCities table, but by not "Requiring" values for those two fields in the refCities table, we can still acquire relational integrity between tables where the values are provided.

Image:relat.png


THE FORMS:
The images below shows the form as it progresses throught the cascade.

First Form, the form designed to flow with the data hierarchy:

Note the order of the City, State and Country controls - they seem a bit out of order. But the purpose of this portion of the example, we'll proceed to demonstrate the behavior of what should be expected from a properly working combobox cascade set.

Image:frm_01.png

__________________________
First, select the Country:

Image:frm_02.png

__________________________
Then Select the State:
After the Country is selected, the Country combobox's After_Update procedure (VBA) will dynamically create an SQL statememt selecting all 'States' in the refStates table that match the selected CountryID; then assign that SQL statement to the State combobox control's RowSource, refresh the State combobox control, move the focus to the State combobox, then drop the list down.

Image:frm_03.png

__________________________
Finally, select the City:
Similarly, when the State has been selected, the State combobox's After_Update procedure (VBA) will dynamically create an SQL statememt selecting all 'Cities' in the refCities table that match the selected StateID; then assign that SQL statement to the City combobox control's RowSource, refresh the City combobox control, move the focus to the State combobox, then drop the list down.

Image:frm_04.png

If the State combobox is skipped, not used or has no data, you will see in the VBA code a conditional check that catches an empty (null) state value and uses a different SQL statement to to populate the City combobox's RowSource.


_____________________________________________________________________
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Second Form, the form is redesigned to flow more like people are accustomed:

Note the more proper order of the City, State and Country controls.

Image:frm_21.png

__________________________
When the City control drops down, it exposes ALL the Cities and their assicated States and Country.
In this form, the City Combobox uses the 'qrefCities2' query - shown below.
Note the gaps in the list where States?Provinces/Regions should be; they are abscent to demonstrate the ability of this method to handle null values in the cascade set. (They are also absent because I did not do any map lookups for the United Kingdom and populate them.)

Image:frm_22.png

__________________________
By typing in the first letter of the city, (in this case "w"), the list jumps to the first City record that starts with a "w".
(Normally, these would be sorted by one or more of the colums, but in this example that is not the case - sorting can easily be implemented at design time. The files in the attachment below have the sorts implemented.)

Image:frm_23.png

__________________________
Finally, once the City you want is highlighted and then 'clicked' or the 'Enter' key is pressed, the City value is committed and with some VBA, the State and Country controls are auto-populated as well.

Image:frm_24.png


THE QUERIES:
The images below shows the queries used for Cities combobox; first the query designed to flow with the data hierarchy. This one is used as the default RowSource for the cboCities control in the frmCascadingComboboxDemo form.

Image:qrefCities.png

SQL
SELECT refCities.CityID, Nz([StateID],0) AS StID, refCities.CountryID, refCities.City
FROM refCities
ORDER BY refCities.City;

__________________________
Next, the query is redesigned to flow more like people are accustomed. This one is used as the default RowSource for the cboCities control in the frmCascadingComboboxDemo2 form.

Image:qrefCities2.png

SQL
SELECT refCities.CityID, Nz([refCities].[StateID],0) AS StID, refCities.CountryID, refCities.City, refStates.State, refCountries.Country
FROM (refCities LEFT JOIN refStates ON refCities.StateID = refStates.StateID) LEFT JOIN refCountries ON refCities.CountryID = refCountries.CountryID
ORDER BY refCities.City, refStates.State, refCountries.Country;


THE REFERENCE DATA ENTRY FORMS:
For RowSource data in the comboboxes, we need a mechanism that will give users the ability to add records to the dropdown comboboxes. The following forms do just that.

Image:Countries.png Image:States.png


Image:Cities.png

The Code

frmCascadingComboxDemo:

CODE
Option Compare Database
Option Explicit

Dim strSQL As String

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next

   strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & ")) ORDER BY refStates.State;"
   'Debug.Print "cboState RowSource: " & strSQL
   Me.cboState.RowSource = strSQL  ' set new RowSource for State combobox
   Me.cboState.Requery             ' referesh it
   Me.cboState.Enabled = True      ' make sure it's enabled (It starts disabled to dissuade users from from trying to select a state before the country)
   Me.cboCity.Enabled = True       ' enable the City combobox too - a State/Province/Region is NOT required here - you can enforce it if you need to
   Me.cboState.SetFocus            ' move focus to the State control
   Me.cboState.Dropdown            ' force the list to dropdown and display the state records for the selected country

End Sub

Private Sub cboState_AfterUpdate()
   On Error Resume Next

   If Nz(Me.cboState.Value, 0) = 0 Then
       strSQL = "SELECT qrefCities.CityID, qrefCities.City FROM qrefCities " & _
                "WHERE ((qrefCities.CountryID)=" & Me.cboCountry.Value & ") " & _
                "ORDER BY qrefCities.City;"
     Else
       strSQL = "SELECT qrefCities.CityID, qrefCities.City FROM qrefCities " & _
                "WHERE (((qrefCities.CountryID)=" & Me.cboCountry.Value & ") AND " & _
                "((qrefCities.StID)=" & Me.cboState.Value & ")) " & _
                "ORDER BY qrefCities.City;"
   End If
   'Debug.Print "cboCity RowSource: " & strSQL
   Me.cboCity.RowSource = strSQL   ' set new RowSource for City combobox
   Me.cboCity.Requery              ' referesh it
   Me.cboCity.Enabled = True       ' make sure it's enabled
   Me.cboCity.SetFocus             ' move focus to the City control
   Me.cboCity.Dropdown             ' force the list to dropdown and display the city records for the selected state

End Sub

Private Sub Form_Current()
' sets the values of the record level comboboxes when things are edited
   
   If Me.NewRecord Then
       Me.cboCity.RowSource = "qrefCities"    ' set default RowSource for the City combobox
       Me.cboCity.Requery                      ' referesh it
       Me.cboState.RowSource = "refStates"     ' set default RowSource for the State combobox
       Me.cboState.Requery                     ' referesh it
     Else
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & "));"
       Me.cboState.RowSource = strSQL  ' set new RowSource for State combobox
       Me.cboState.Requery             ' referesh it
       Me.cboState.Enabled = True      ' make sure cboState is enabled
       Me.cboCity.Enabled = True       ' make sure cboCity is enabled
       If Nz(Me.cboState.Value, 0) = 0 Then
           strSQL = "SELECT qrefCities.CityID, qrefCities.City FROM qrefCities " & _
                "WHERE ((qrefCities.CountryID)=" & Me.cboCountry.Value & ") " & _
                "ORDER BY qrefCities.City;"
         Else
           strSQL = "SELECT qrefCities.CityID, qrefCities.City FROM qrefCities " & _
                "WHERE (((qrefCities.CountryID)=" & Me.cboCountry.Value & ") AND " & _
                "((qrefCities.StID)=" & Me.cboState.Value & ")) " & _
                "ORDER BY qrefCities.City;"
       End If
       Me.cboCity.RowSource = strSQL   ' set new RowSource for City combobox
       Me.cboCity.Requery              ' referesh it
   End If
   
End Sub


frmCascadingComboboxDemo2:

CODE
Option Compare Database
Option Explicit

Dim strSQL As String

Private Sub cboCity_AfterUpdate()

   If Nz(Me.cboCity.Value, 0) > 0 Then
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCity.Column(2) & ")) " & _
                "ORDER BY refStates.State;"
       'Debug.Print "cboState RowSource: " & strSQL
       Me.cboState.RowSource = strSQL          ' set new RowSource for State combobox
       Me.cboState.Requery                     ' referesh it
       Me.cboState = Me.cboCity.Column(1)      ' set the value of the State combobox
       Me.cboCountry = Me.cboCity.Column(2)    ' set the value of the Country combobox
   End If

End Sub

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next

   strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & ")) " & _
                "ORDER BY refStates.State;"
   'Debug.Print "cboState RowSource: " & strSQL
   Me.cboState.RowSource = strSQL  ' set new RowSource for State combobox
   Me.cboState.Requery             ' referesh it
   Me.cboState.Enabled = True      ' make sure it's enabled (It starts disabled to dissuade users from from trying to select a state before the country)
   Me.cboCity.Enabled = True       ' enable the City combobox too - a State/Province/Region is NOT required here - you can enforce it if you need to
   Me.cboState.SetFocus            ' move focus to the State control
   Me.cboState.Dropdown            ' force the list to dropdown and display the state records for the selected country

End Sub

Private Sub cboState_AfterUpdate()
   On Error Resume Next

   If Nz(Me.cboState.Value, 0) = 0 Then
       strSQL = "SELECT qrefCities2.* FROM qrefCities2 WHERE ((qrefCities2.CountryID)=" & _
                Me.cboCountry.Value & ") ORDER BY refCities.City, refStates.State, refCountries.Country;"
     Else
       strSQL = "SELECT qrefCities2.* FROM qrefCities2 WHERE (((qrefCities2.CountryID)=" & _
                Me.cboCountry.Value & ") AND ((qrefCities2.StID)=" & Me.cboState.Value & ")) " & _
                "ORDER BY refCities.City, refStates.State, refCountries.Country;"
   End If
   'Debug.Print "cboCity RowSource: " & strSQL
   Me.cboCity.RowSource = strSQL   ' set new RowSource for City combobox
   Me.cboCity.Requery              ' referesh it
   Me.cboCity.Enabled = True       ' make sure it's enabled
   Me.cboCity.SetFocus             ' move focus to the City control
   Me.cboCity.Dropdown             ' force the list to dropdown and display the city records for the selected state

End Sub

Private Sub Form_Current()
' sets the values of the record level comboboxes when things are edited
   
   If Me.NewRecord Then
       Me.cboCity.RowSource = "qrefCities2"    ' set default RowSource for the City combobox
       Me.cboCity.Requery                      ' referesh it
       Me.cboState.RowSource = "refStates"     ' set default RowSource for the State combobox
       Me.cboState.Requery                     ' referesh it
     Else
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & ")) " & _
                "ORDER BY refStates.State;"
       Me.cboState.RowSource = strSQL  ' set new RowSource for State combobox
       Me.cboState.Requery             ' referesh it
       Me.cboState.Enabled = True      ' make sure cboState is enabled
       Me.cboCity.Enabled = True       ' make sure cboCity is enabled
       If Nz(Me.cboState.Value, 0) = 0 Then
           strSQL = "SELECT qrefCities2.* FROM qrefCities2 WHERE ((qrefCities2.CountryID)=" & _
                    Me.cboCountry.Value & ") ORDER BY refCities.City, refStates.State, refCountries.Country;"
         Else
           strSQL = "SELECT qrefCities2.* FROM qrefCities2 WHERE (((qrefCities2.CountryID)=" & _
                    Me.cboCountry.Value & ") AND ((qrefCities2.StID)=" & Me.cboState.Value & ")) " & _
                    "ORDER BY refCities.City, refStates.State, refCountries.Country;"
       End If
       Me.cboCity.RowSource = strSQL   ' set new RowSource for City combobox
       Me.cboCity.Requery              ' referesh it
   End If
   
End Sub


frmCities:

CODE
Option Compare Database
Option Explicit

Dim strSQL As String

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next

   If Nz(Me.cboCountry.Value, 0) = 0 Then
       Me.Filter = ""      ' set value of form's Filter to nothing
       Me.FilterOn = False ' turn form's filtering off
       strSQL = "SELECT 0 As StateID,'ALL' As State,0 As CountryID FROM refStates  " & _
                "UNION SELECT refStates.StateID, refStates.State, refStates.CountryID " & _
                "FROM refStates ORDER BY State;"
       Me.cboState.RowSource = strSQL  ' assign cboState's modified RowSource
       Me.cboState.Requery             ' refresh it
       Me.cboState = 0                 ' set it to 'zero' (so it displays "ALL")
       DoCmd.ShowAllRecords            ' ensure form is showing all records
     Else
       Me.Filter = "CountryID = " & Me.cboCountry.Value    ' set value of form's Filter to selected Country
       Me.FilterOn = True                                  ' turn form's filtering on
       Me.txtCountryID.DefaultValue = Me.cboCountry.Value  ' set default value of record's CountryID in form's detail section
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & "));"
       Me.cboState.RowSource = strSQL      ' assign cboStateID's modified RowSource (in form header)
       Me.cboState.Requery                 ' refresh it
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
              "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & "));"
       Me.txtStateID.RowSource = strSQL    ' assign txtStateID's modified RowSource (in form detail section)
       Me.txtStateID.Requery               ' refresh it
   End If
   Call cboState_AfterUpdate ' provides cleanup after altering the form header's Country filter

End Sub

Private Sub cboState_AfterUpdate()

   If Nz(Me.cboCountry.Value, 0) = 0 And Nz(Me.cboState.Value, 0) > 0 Then ' check values of filter comboboxes in form header
       ' in this condition - constrain the State values
       Me.Filter = "StateID = " & Me.cboState.Value    ' set form's Filter value
       Me.FilterOn = True                              ' turn form's filtering on
       Me.txtStateID.DefaultValue = Me.cboState.Value  ' set default value of record's StateID in form's detail section
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.StateID)=" & Me.cboState.Value & "));"
       Me.txtStateID.RowSource = strSQL    ' assign txtStateID's modified RowSource
       Me.txtStateID.Requery               ' refresh it
     ElseIf Nz(Me.cboCountry.Value, 0) > 0 And Nz(Me.cboState.Value, 0) = 0 Then
       ' in this condition - constrain the Country values
       Me.Filter = "CountryID = " & Me.cboCountry.Value    ' set form's Filter value
       Me.FilterOn = True                                  ' turn form's filtering on
       Me.txtStateID.DefaultValue = 0                      ' set default value of record's StateID in form's detail section
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.CountryID)=" & Me.cboCountry.Value & "));"
       Me.txtStateID.RowSource = strSQL    ' assign txtStateID's modified RowSource
       Me.txtStateID.Requery               ' refresh it
     ElseIf Nz(Me.cboCountry.Value, 0) > 0 And Nz(Me.cboState.Value, 0) > 0 Then
       ' in this condition - constrain both the Country and State values of the form's recordset
       Me.Filter = CountryID = " & Me.cboCountry.Value & " And StateID = " & Me.cboState.Value"
       Me.FilterOn = True      ' turn form's filtering on
       Me.txtStateID.DefaultValue = Me.cboState.Value
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev " & _
                "FROM refStates WHERE (((refStates.StateID)=" & Me.cboState.Value & ") AND ((refStates.CountryID)=" & Me.cboCountry.Value & "));"
       Me.txtStateID.RowSource = strSQL    ' assign txtStateID's modified RowSource
       Me.txtStateID.Requery               ' refresh it
     ElseIf Nz(Me.cboCountry.Value, 0) = 0 And Nz(Me.cboState.Value, 0) = 0 Then
       ' in this condition - show all records
       Me.Filter = ""
       Me.FilterOn = False
       Me.txtStateID.DefaultValue = 0      ' set default value of record's StateID in form detail section (shows "ALL")
       strSQL = "SELECT refStates.StateID, refStates.State, refStates.StateAbbrev, refCountries.Country " & _
                "FROM refStates LEFT JOIN refCountries ON refStates.CountryID = refCountries.CountryID;"
       Me.txtStateID.RowSource = strSQL    ' assign txtStateID's modified RowSource
       Me.txtStateID.Requery               ' refresh it
   End If
   Me.Requery

End Sub

Private Sub Form_Dirty(Cancel As Integer)
' sets the values of the record level comboboxes when things are edited
   If Me.cboCountry.Value > 0 Then
       Me.txtCountryID = Me.cboCountry.Value
   End If
   If Me.cboState.Value > 0 Then
       Me.txtStateID = Me.cboState.Value
   End If

End Sub


frmStates:

CODE
Option Compare Database
Option Explicit

Dim strSQL As String

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next

   If Nz(Me.cboCountry.Value, 0) = 0 Then
       Me.Filter = ""          ' set value of form's Filter to nothing
       Me.FilterOn = False     ' turn form's filtering off
       DoCmd.ShowAllRecords
     Else
       Me.Filter = "CountryID = " & Me.cboCountry.Value    ' set form's Filter value
       Me.FilterOn = True                                  ' turn form's filtering on
       Me.txtCountry.DefaultValue = Me.cboCountry.Value    ' set default value of record's CountryID in form's detail section
   End If

End Sub

Private Sub Form_Dirty(Cancel As Integer)
' sets the values of the record level comboboxes when things are edited
   If Me.cboCountry.Value > 0 Then
       Me.txtCountry = Me.cboCountry.Value
   End If

End Sub


frmCountries:
This form has no procedures.



The Demo File:

(Finally!)

The link below contains:

 • CascadingComboboxDemo2K.mdb (340k)
 • CascadingComboboxDemo2K3.mdb (364k)
 • CascadingComboboxDemo2K7.accdb (488k)
Cascading Combobox Demo  <--- click to retrieve file
Edit Discussion
Thank you for your support!
This page has been accessed 37,690 times.  This page was last modified 17:47, 23 October 2014 by doctor9. Contributions by Mark Davis, Cpetermann, Walter Niesz and Jack Leach and others  Disclaimers