Cascading combo boxes have always been one of those little Access tasks that sounds simple until you have to build it. Pick a country, filter the state list. Pick a state, filter the city list. Pick a category, filter the products. Traditionally, that meant writing an After Update event, running a Requery, and making sure every piece of the plumbing stayed connected. It worked, but now Access 365 has a built-in way to handle ordinary cascading combo boxes and list boxes without VBA.
The big news is that Access can now link dependent combo boxes using properties that should look familiar if you have ever worked with subforms: Link Master Fields and Link Child Fields. This is not just a nice convenience for beginners, either. The really important improvement is that these linked controls work properly in Continuous Forms, Datasheet View, and Split Forms, where the old shared-row-source approach could get pretty spicy.
A cascading control is simply a list whose available choices depend on another control. The first control is the parent, sometimes called the master. The next control is the child. The parent supplies a value, and the child list displays only records that match that value.
For example, suppose a customer form has Country, State/Province, and City fields. Once the user selects United States, the State combo box should show Florida, New York, Texas, and so on. It should not offer Ontario or British Columbia. Once the user picks Florida, the City combo should offer Cape Coral, Miami, Fort Myers, and other Florida cities, but not Toronto or Vancouver.
That is not just about making the form look fancy. It helps prevent invalid combinations, keeps giant dropdown lists short, and makes data entry much easier. Nobody wants to scroll through every city in the database just to find one in their selected state. Well, maybe somebody does, but I would not hire that person to design my forms.
The new feature uses two properties on the dependent combo box or list box. Link Master Fields identifies the value coming from the parent control. Link Child Fields identifies the field in the child control's Row Source that must match it.
Using the Country to State example, imagine that your Country combo box is named CountryCombo and stores a numeric CountryID. Your State combo box gets its choices from a State table or query containing StateID, StateName, and CountryID. On the State combo, you set Link Master Fields to CountryCombo and Link Child Fields to CountryID. Access then handles the filtering automatically.
The important part is that the child combo's Row Source must include the matching field. In this case, the State combo must include CountryID in its Row Source. It does not have to be visible to the user. You can hide that column, just as you normally hide key fields in a relational combo box, but Access still needs the field available behind the scenes.
Also, the dependent control must use a Table/Query Row Source Type. This does not work with a Value List. A Value List is just a fixed collection of typed-in values, and Access has no relational field available to use for the link. If you want a proper cascade, use a table or query as the source for the child combo or list box.
The setup can continue through as many levels as you need. For a City combo box, include CityID, CityName, and StateID in the Row Source. Then set its Link Child Fields property to StateID and its Link Master Fields property to your State combo box. Country filters State, State filters City, and you have a three-level cascade without writing routine VBA event code.
One thing I strongly recommend is giving your controls useful names. Call them CountryCombo, StateCombo, and CityCombo. Do not leave them as Combo39, Combo42, and Combo57 unless you enjoy making your own life difficult. When you open the Link Master Fields dropdown and see a list of control names, CountryCombo tells you exactly what it is. Combo39 tells you that somebody made a combo box at some point in history. Possibly during the Clinton administration.
The real technical win here is how this works in a Continuous Form. In a normal single-record form, the new feature mainly saves time and eliminates a little code. With a Continuous Form, however, several records can appear on screen at once. Access is still using one control definition behind the scenes for all those visible rows, which is why traditional cascading combo techniques could be troublesome.
With the older method, you would often requery the child combo for the current record. That could leave another visible record looking blank if its saved child value was not included in the currently filtered Row Source. The data was still there. Access just could not display it because the combo's list was currently filtered for a different record.
The new built-in linking is record-aware. Access can evaluate the relationship separately for each displayed record. A Continuous Form can show one customer in the United States with Florida selected, another customer in Canada with Ontario selected, and another customer elsewhere with the appropriate matching value. This is also particularly useful for people who do a lot of work in Datasheet View or Split Forms.
The older VBA method is not obsolete, by the way. It is still useful to understand After Update events, Requery, Row Sources, query criteria, and the ways VBA can dynamically build a list. Those are important Access Legos that apply to plenty of other situations. Microsoft has simply given us a power drill for a job where we used to reach for a screwdriver.
If your new cascade does not work, check the basics before blaming Access. Make sure the parent is providing the same kind of value the child field expects. If CountryCombo stores a numeric CountryID, the CountryID in the State combo's Row Source should also be numeric. Make sure the child linking field is actually included in the Row Source, even if it is hidden. And make sure the child control's Row Source Type is Table/Query.
Another common issue is changing a parent value after a child value has already been selected. If a customer had Florida selected and you change their country to Canada, Florida is no longer a valid state choice. Access will require a new valid dependent selection. That is exactly what you want. It prevents bad combinations from being saved, such as Canada and Texas or United States and Ontario.
This feature was announced on September 24, 2026, and is rolling out gradually to Microsoft 365 Access users on the Current Channel, Version 2609. Gradual rollout means two people can both show the same version number, while one has the new properties and the other does not yet. If you do not see Link Master Fields and Link Child Fields on your combo or list box property sheet, check for Office updates, but do not panic or reinstall Office seventeen times. It may simply not have reached your installation yet.
These properties are a very welcome addition to Access. They reduce routine form plumbing, make dependent lists easier to build, and finally make this kind of relationship behave properly across multiple visible records. The old techniques are still worth knowing, but for a straightforward country-to-state, category-to-product, or similar cascade, the new built-in approach is cleaner, easier, and much less likely to make you mutter at your monitor.
Watch the embedded video for the complete walkthrough, including building a Country, State, and City example from scratch and seeing the same linked combos work in a Continuous Form.
Live long and prosper,
RR
No comments:
Post a Comment