Cascading Combo Boxes

One of the benefits of combo boxes is that you can chain them together to search through large hierarchical data sets.  The technique is commonly referred to as "cascading combo boxes."

So, why is this necessary and when would you use it?

Data hierarchies

A data hierachy exists when you have data that are related via parent-child relationships.

Within data hierarchies, there are two main types: fixed and dynamic.

Fixed Hierarchies

Fixed hierarchies have well-defined levels.

For example, census-designated places (CDP) in the United States can be arranged into the following hierarchy:

• States
• Counties
• Places

Each CDP belongs to a single county.  Each county belongs to a single state.

Dynamic Hierarchies

Dynamic hierarchies can have an arbitrary number of levels.  Generally speaking, the levels themselves cannot be assigned meaningful names.  Rather, the levels are generally assigned numbers for reference.

The Windows folder structure is a good example of a dynamic hierarchy.  The third-level subfolder under the System32 folder is not related in any way to the third-level subfolder under the Documents folder.  Also, some folders may go only two levels deep, while other folders may go ten or more levels deep.

Rule of Thumb

A good rule of thumb for whether you have a fixed hierarchy or a dynamic hierarchy is how the data is modeled in a relational database.  

A fixed hierarchy is best modeled as a series of one-to-many tables.  

A dynamic hierarchy is best modeled as a single table with a self-referencing parent and/or child field.

Representing Data Hierarchies in a User Interface

Just as there are different ways to represent fixed and dynamic hierarchies in a relational design, the two types are best served by different user interfaces.

Dynamic hierarchy user interface

The best way to represent a dynamic hierarchy on a form is with a tree control.  This is what File Explorer uses for folder navigation.

Fixed hierarchy user interface

There are several ways to represent a fixed hierarchy.  

Editing records in a fixed hierarchy

The most common way to allow users to edit records in a fixed hierarchy is to use subforms.  A form in Single Form view represents the parent record.  The records in the next level down appear in a subform linked to the parent form via a master-child relationship.

Finding records in a fixed hierarchy

Subforms are great for editing child records in a fixed hierarchy.  However, if you want to provide your users a way to find a record in a fixed hierarchy, your best bet is often to go with cascading combo boxes.

Demonstration

Let's revisit our earlier example of census-designated places in the United States.  There are more than 40,000 such places.  Among those places, there are several duplicate place names.

Perhaps the most famous example is Springfield.  The hometown of Homer, Marge, Bart, Lisa, and Maggie, Springfield is the basis of a long-running discussion among Simpsons fans over which real-life town is the inspiration for the show.

The brute force approach

First, here's what not to do.  

The combo box below includes every place name in the United States.  That includes all 24 instances of Springfield.  There are 40K+ records that need to load every time the user opens the form.  Depending on where these records live and how we are retrieving them, that could kill performance.

The cascading combo box approach

A better way to provide the lookup (assuming your users know the State and County to which the place they are looking for belongs), is to break the lookup into a series of "cascading combo boxes."

When the form loads, only the State picker is populated.  The County and Place combo boxes have their RowSource properties set to an empty string.

Once the user chooses a State, the County combo box's RowSource is updated to list all the counties for that particular State.  And when the user chooses a County–you guessed it–the Place combo box's RowSource is updated to list all the census-designated places for that particular County.

Here is the concept in action:

To see how I accomplish this effect and many other cool combo box tricks, join me on Friday, June 18, 2021, at 9 AM EDT for the Eastern Time Access User Group monthly webinar.  

Image by MITCH WRIGHT from Pixabay