Browse All Articles > Cascading Combos
This is another question that appears very often on EE, sometimes as “synchronized combos”. It is so common that Access now offers a demo database, downloadable from the “samples” section on the welcome screen, demonstrating two methods to achieve this result. “How to” pages exist for this since version 2.0 at least.
What result exactly? The user selects something from a first combo box, and is offered a filtered list of choices in a second. This can go on in a third, forth, or more “cascading” combos.
Given the frequency of the question, this article is bound to be redundant. Someone, surely, has already posted exactly what you are about to read? Strangely enough, no, not exactly. The solutions offered almost always suggest to rewrite the `row source´, which makes the process appear technical, and in fact raises a couple of technical questions rather artificially.
In this article I will show a very simple method first, and then move on to the more popular methods, to discuss their particular problems.
The demo database can be opened in parallel to follow the examples more easily. Make sure to switch to design view to examine the queries, the combo box properties, and the Visual Basic module.
CascadingCombos.mdb
Imagine a form where the user will select a product in a combo box. Given the number of products, it would be easier to select a category first, thus filtering the list of products to only those belonging to that category.
The row source for the categories combo is the table name. To be a little more explicit, the query can look like this:
SELECT CategoryID, CategoryName FROM Categories
In order to read the current value of this combo, we need its name, let's call it cboCategory. Now the query for the list of products is simply
SELECT ProductID, ProductName FROM Products
WHERE Category = cboCategory
It already works! However, the user has to press F9 in the products combo to refresh the list, and he or she doesn't see any product until a category has been chosen. The second point is often intended: many developers think that users should follow a precise order of events. More about that later.
To refresh the products combo when the categories combo has been updated requires one line of Visual Basic code, or one macro instruction, both in the `after update´ event. The Visual Basic line of code is
Requery; control: cboProduct
And that concludes the simple example! All the fuss about cascading combos reduced to that? Not entirely, but the above is the simplest solution, which can be taught successfully on the second day of an introductory class about Access (the fist day being dedicated to tables and queries).
If the user selects “Beverages”, and then “Côte de Blaye”, all is good. If he or she then goes back to categories and selects “Condiments”, the product list is cleared. No surprise. However, if “Beverages” is selected again, the expensive wine appears again! The product combo has remembered its last value.
This might or might not be a problem. Most developers feel it is, and also clear the products combo when they requery it. The code becomes twice as complex:
Requery; control: cboProduct
SetValue; item: cboProduct; value: Null
I will stop showing macro equivalents for Visual Basic instructions. SetValue already poses a problem: it's not a “trusted” instruction and recent versions of Access will not let you use it without some grief. In older versions, this is about as far as you can go with macros anyway, because there is no error management. Finally, there is very little support to be found anywhere for macro development and maintenance.
Another problem mentioned earlier is that the list of products doesn't display anything until a product has been selected. This might make sense in some rare cases. Most of the time, all products should be displayed by default. A user might have forgotten the category for snails, but know their name in French...
The query can be slightly changed to display all records when no category was selected.
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE cboCategory Is Null Or cboCategory=CategoryID
ORDER BY ProductName
Note that I have added a sort order and also a third column. This is useful in order to automate the reverse update: show the category of the product that was just selected. This again requires one entire line of VB in the combo's `after update´ event
At this point, your cascading combos are already better than 99% of those I have encountered in actual, live Access applications. Before going into technical details, this is the complete module built so far:
Query Parameters
Most solution you might encounter on the Web, including Microsoft's own samples, will convince you that the “full” name of controls needs to be used. In other words, “cboProducts” isn't enough, you need to write “Forms!frmOrders!cboProducts” (I just had to name my form to write this). The full name is indeed the way to designate that particular combo should you need it elsewhere (a query, another form, etc), but when writing the row source of a control on the same form, you don't need this complication.
Technically, the query is executed in the name space of the form. When Jet encounters a name it can't resolve, “cboProducts”, it will not pass it directly to VB, or rather it will do so only after looking at the names defined on the form. This mechanism is very useful. The “full” name isn't stable; it changes if you change the form name or if you copy-paste your controls to another form; it changes also when the combos are on a subform, in which case you might have to write:
Forms!frmOrders!subOrderDetails.Form!cboProducts
This becomes very hard to explain: “subOrderDetails isn't the name of the form, but the name of the subform control on the form frmOrders”; “you can skip .Form, I guess”; “It should work also if you use dots instead of bangs”; “no, the square brackets are only needed if you have spaces in a name”, ... I could go on.
Simply remember this: a query in a control can use all names defined on the form; this includes all other controls on the same form.
If you need a control from the parent form, use “Parent!‹control›”, if you need a control from a subform, use “‹subform control›!‹control›”. You should never have to name the main form at all.
If you want to be explicit, you can declare the parameter in the row source query. This is useful to Jet (it will resolve the name before trying to optimise the query), and to you or the next developer as internal documentation. For various reasons, I have taken the habit to always use square brackets around parameters, but that isn't a requirement unless spaces or special characters are used.
PARAMETERS [cboCategory] Byte;
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE [cboCategory] Is Null Or [cboCategory]=CategoryID
Even if you test this query on its own, Jet will know that cboCategory must be a number between 0 and 255, or Null. It will prompt you for it, and accept only a numeric value in range. It will also optimize the query with that information at hand, and save it that way.
Rewriting the Row Source
I always wonder why this is so popular; it has no benefits, and several disadvantages. The idea is as follows: in the first combo's `after update´ event, instead of issuing a requery, the row source is rewritten entirely.
It's already obvious that the parametric solution is much simpler, and it's “officially” also much safer. It doesn't end here. Rewriting the row source has the following consequences:
In summary, although this solution has earned many experts many points over the years, it is intrinsicly flawed, quite inefficient, and needlessly complex. The only good thing about it is that the novice developer has to acquire rudimentary knowledge about string manipulation and the Access object model in order to implement the solution.
To be honest, the code can (and in fact should) handle the case where cboCategory is left blank or has been cleared by the user:
This has turned into a rant, so I need to soften up a little.
Using the full path of a control has its uses in many cases. It isn't a bad practice as such, it's just needlessly complicated if it isn't necessary. Cascading combos are always on the same form and are thus perfect candidates for direct parametric queries using the other controls' names.
The same goes for rewriting the row source of a combo box. It does make sense, sometimes, but it should always be weighted against solutions without it. In the present case, there is no question that parametric queries are largely superior, on any scale you may choose.
Let's return to the selection of a product. We can already filter by category, but we could also filter by some other criteria. If I add a combo box displaying suppliers, the supplier ID can be used as additional or alternative filter. The products query becomes
PARAMETERS [cboCategory] Byte, [cboSupplier] Long;
SELECT ProductID, ProductName
FROM Products
WHERE ([cboCategory] Is Null Or [cboCategory]=CategoryID)
AND ([cboSupplier] Is Null Or [cboSupplier]=SupplierID)
Note the brackets. Without them, the AND operator takes pecedence over OR, creating quite unexpected results. I also removed the additional fields: with two filtering combos, the “upstream” approach is no longer desirable.
This leads to another slight improvement in the code. Remember that the value of the products combo is cleared, in order to avoid a ghost value, without visible clue. Ideally, this should occur only if that is the case, if the value is indeed a “ghost”. When that is the case, i.e. when nothing is visibly selected, the combo's `list index´ property is -1. So the event handler can clear the value only if it has become a ghost. This would be:
After Update: =SynchProducts()
Leaving only a single function in the module. In the demo file, I added yet another criteria (a yes/no field: discontinued), to show how easy it is to provide multiple filtering choices to the user.
The previous examples don't really convey the idea of a cascade. One would have to see at least three combo boxes, with selections to be made in sequence, to evoque this image.
The only meaningful examples I have encountered involve accounting codes, and that is perhaps because I'm not an accountant. Some people tend to think in lists, or in outline mode, and likewise organize items in categories, sub-categories, sub-sub-categories... In database design, this would be best represented as a hierarchy, involving a different tool-set than cascading combos.
As a demonstration, instead of inventing fake accounting codes, multi-level cascading combos can be simulated by selecting letters. Select the first letter of a product, the next combo displays a list of available second letters. Select the second, and see a list of available third letters. This is surprisingly realistic, and demonstrates the idea quite well.
The first query in this demo is
SELECT DISTINCT Left(ProductName,1) FROM Products
The next one is
SELECT DISTINCT Mid(ProductName,2,1) FROM Products
WHERE ProductName Like cboFirstLetter+'*'
Many additional combos can be added, each extracting the next letters from the product names starting with the combination of letters selected above.
Each combo box uses all the previous selections (implicitly or explicitly) as parameters, and this is the first characteristic of the cascade. The second is that each selection affects the combo boxes below. Technically, the `after update´ event of each combo affects the next one, and should therefore trigger its `after update´ as well, and so on. Typically, selecting an item in a combo will clear any existing selections made below.
When there is only one item to choose from, the user feels a little cheated, so it makes sense (at least for this demo) to automatically select that single item. This addition makes the “cascading effect” very real, and you can try it out in the demo database. The entire module is as follows:
In some cases, the data model creates indirect relationships between tables. For example, a user can filter a list of products by choosing a category, or a supplier. If both filters are used, it would make sense to show only the suppliers who actually supply at least one product in that category or, reversely, only the categories of all the products of a selected supplier. Finally, if no filter is used, the selection of a product could automatically document the combo boxes for category and supplier.
When several combo boxes are interconnected like that, they “dance” with one another, hence the name. This is a rather special and unusual type of user interface, and should not be used lightly. However, when done correctly an in the right context, it is a very pleasant way to navigate complex data-sets.
Dancing combos make sense only when different filtering options (here the category and the supplier) are themselves linked through a many-to-many relationship. There can be several suppliers in each category of products, and each supplier might have products in several categories. Therefore, the selection of a single category can yield a list of suppliers, and the selection of a supplier can display the list of categories of all their products. In other words, the cascading effect can go both ways, and the user is free to work in the way that seems the most logical in any given situation.
Indeed. This is a frequent variation on the question, one that sometimes only becomes apparent after some comments have already been exchanged. After all, why should it matter whether the form is continuous or not? The fact is that the list of items available in a combo changes after each requery, and that the same list is used for all visible records.
Anyway, this section grew so much that I chose to move it into a separate article, more focused and pertinent. It is best read after this one, as the basics are not repeated there: Cascading Combos on Continuous Forms and Datasheets.
Although this article is a little long, I hope you have found the answer you needed before the end of the second section. I have shown this technique with success to students on the second day of an elementary Access class. Prerequisites are only basic knowledge of queries and form design.
In practice, the problems arise when trying to implement a complex (needlessly complex) solution to achieve the desired result. Just keep it simple, and you will get it working in no time. The more elaborate examples require just a little longer, but what a pleasure when your combos dance in tune!
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
What result exactly? The user selects something from a first combo box, and is offered a filtered list of choices in a second. This can go on in a third, forth, or more “cascading” combos.
Given the frequency of the question, this article is bound to be redundant. Someone, surely, has already posted exactly what you are about to read? Strangely enough, no, not exactly. The solutions offered almost always suggest to rewrite the `row source´, which makes the process appear technical, and in fact raises a couple of technical questions rather artificially.
In this article I will show a very simple method first, and then move on to the more popular methods, to discuss their particular problems.
The demo database can be opened in parallel to follow the examples more easily. Make sure to switch to design view to examine the queries, the combo box properties, and the Visual Basic module.
CascadingCombos.mdb
A simple example
Imagine a form where the user will select a product in a combo box. Given the number of products, it would be easier to select a category first, thus filtering the list of products to only those belonging to that category.
The row source for the categories combo is the table name. To be a little more explicit, the query can look like this:
SELECT CategoryID, CategoryName FROM Categories
SELECT ProductID, ProductName FROM Products
WHERE Category = cboCategory
To refresh the products combo when the categories combo has been updated requires one line of Visual Basic code, or one macro instruction, both in the `after update´ event. The Visual Basic line of code is
cboProducts.RequeryRequery; control: cboProduct
Refining the example
If the user selects “Beverages”, and then “Côte de Blaye”, all is good. If he or she then goes back to categories and selects “Condiments”, the product list is cleared. No surprise. However, if “Beverages” is selected again, the expensive wine appears again! The product combo has remembered its last value.
This might or might not be a problem. Most developers feel it is, and also clear the products combo when they requery it. The code becomes twice as complex:
cboProducts.Requery
cboProducts = NullRequery; control: cboProduct
SetValue; item: cboProduct; value: Null
Another problem mentioned earlier is that the list of products doesn't display anything until a product has been selected. This might make sense in some rare cases. Most of the time, all products should be displayed by default. A user might have forgotten the category for snails, but know their name in French...
The query can be slightly changed to display all records when no category was selected.
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE cboCategory Is Null Or cboCategory=CategoryID
ORDER BY ProductName
cboCategory = cboProduct.Column(2)At this point, your cascading combos are already better than 99% of those I have encountered in actual, live Access applications. Before going into technical details, this is the complete module built so far:
Option Explicit
Private Sub cboCategory_AfterUpdate()
cboProduct.Requery
cboProduct = Null
End Sub
Private Sub cboProduct_AfterUpdate()
cboCategory = cboProduct.Column(2)
End SubLet's get technical
Query Parameters
Most solution you might encounter on the Web, including Microsoft's own samples, will convince you that the “full” name of controls needs to be used. In other words, “cboProducts” isn't enough, you need to write “Forms!frmOrders!cboProduc
Technically, the query is executed in the name space of the form. When Jet encounters a name it can't resolve, “cboProducts”, it will not pass it directly to VB, or rather it will do so only after looking at the names defined on the form. This mechanism is very useful. The “full” name isn't stable; it changes if you change the form name or if you copy-paste your controls to another form; it changes also when the combos are on a subform, in which case you might have to write:
Forms!frmOrders!subOrderDe
Simply remember this: a query in a control can use all names defined on the form; this includes all other controls on the same form.
If you need a control from the parent form, use “Parent!‹control›”, if you need a control from a subform, use “‹subform control›!‹control›”. You should never have to name the main form at all.
If you want to be explicit, you can declare the parameter in the row source query. This is useful to Jet (it will resolve the name before trying to optimise the query), and to you or the next developer as internal documentation. For various reasons, I have taken the habit to always use square brackets around parameters, but that isn't a requirement unless spaces or special characters are used.
PARAMETERS [cboCategory] Byte;
SELECT ProductID, ProductName, CategoryID
FROM Products
WHERE [cboCategory] Is Null Or [cboCategory]=CategoryID
Rewriting the Row Source
I always wonder why this is so popular; it has no benefits, and several disadvantages. The idea is as follows: in the first combo's `after update´ event, instead of issuing a requery, the row source is rewritten entirely.
Dim strSQL As String
strSQL _
= " SELECT ProductID, ProductName" _
& " FROM Products" _
& " WHERE CategoryID = " & cboCategory
cboProducts.RowSource = strSQL & " WHERE CategoryID = '" & cboCategory & "'" & " WHERE CategoryID = '" & Replace(cboCategory, "'", "''") & "'"It's already obvious that the parametric solution is much simpler, and it's “officially” also much safer. It doesn't end here. Rewriting the row source has the following consequences:
the previous compiled version is dicarded;
the new verion is checked for errors and optimized;
the compiled version is kept in memory (unsaved);
the new query is executed;
when the form closes, the compiled query is discarded.
In contrast, the parametric query is already analyzed, optimized, compiled, and saved. It only need to be re-run.
In summary, although this solution has earned many experts many points over the years, it is intrinsicly flawed, quite inefficient, and needlessly complex. The only good thing about it is that the novice developer has to acquire rudimentary knowledge about string manipulation and the Access object model in order to implement the solution.
To be honest, the code can (and in fact should) handle the case where cboCategory is left blank or has been cleared by the user:
Dim strSQL As String
strSQL _
= " SELECT ProductID, ProductName" _
& " FROM Products"
If Not IsNull(cboCategory) Then _
strSQL = strSQL & " WHERE CategoryID = " & cboCategory
cboProducts.RowSource = strSQLThis has turned into a rant, so I need to soften up a little.
Using the full path of a control has its uses in many cases. It isn't a bad practice as such, it's just needlessly complicated if it isn't necessary. Cascading combos are always on the same form and are thus perfect candidates for direct parametric queries using the other controls' names.
The same goes for rewriting the row source of a combo box. It does make sense, sometimes, but it should always be weighted against solutions without it. In the present case, there is no question that parametric queries are largely superior, on any scale you may choose.
Trickle down combos
Let's return to the selection of a product. We can already filter by category, but we could also filter by some other criteria. If I add a combo box displaying suppliers, the supplier ID can be used as additional or alternative filter. The products query becomes
PARAMETERS [cboCategory] Byte, [cboSupplier] Long;
SELECT ProductID, ProductName
FROM Products
WHERE ([cboCategory] Is Null Or [cboCategory]=CategoryID)
AND ([cboSupplier] Is Null Or [cboSupplier]=SupplierID)
This leads to another slight improvement in the code. Remember that the value of the products combo is cleared, in order to avoid a ghost value, without visible clue. Ideally, this should occur only if that is the case, if the value is indeed a “ghost”. When that is the case, i.e. when nothing is visibly selected, the combo's `list index´ property is -1. So the event handler can clear the value only if it has become a ghost. This would be:
Private Sub cboSupplier_AfterUpdate()
cboProduct.Requery
If cboProduct.ListIndex = -1 Then cboProduct = Null
End SubOption Explicit
Private Sub SynchProducts()
With cboProduct
.Requery
If .ListIndex < 0 Then .Value = Null
End With
End Sub
Private Sub cboCategory_AfterUpdate()
SynchProducts
End Sub
Private Sub cboSupplier_AfterUpdate()
SynchProducts
End SubAfter Update: =SynchProducts()
Cascading Combos
The previous examples don't really convey the idea of a cascade. One would have to see at least three combo boxes, with selections to be made in sequence, to evoque this image.
The only meaningful examples I have encountered involve accounting codes, and that is perhaps because I'm not an accountant. Some people tend to think in lists, or in outline mode, and likewise organize items in categories, sub-categories, sub-sub-categories... In database design, this would be best represented as a hierarchy, involving a different tool-set than cascading combos.
As a demonstration, instead of inventing fake accounting codes, multi-level cascading combos can be simulated by selecting letters. Select the first letter of a product, the next combo displays a list of available second letters. Select the second, and see a list of available third letters. This is surprisingly realistic, and demonstrates the idea quite well.
The first query in this demo is
SELECT DISTINCT Left(ProductName,1) FROM Products
SELECT DISTINCT Mid(ProductName,2,1) FROM Products
WHERE ProductName Like cboFirstLetter+'*'
Each combo box uses all the previous selections (implicitly or explicitly) as parameters, and this is the first characteristic of the cascade. The second is that each selection affects the combo boxes below. Technically, the `after update´ event of each combo affects the next one, and should therefore trigger its `after update´ as well, and so on. Typically, selecting an item in a combo will clear any existing selections made below.
When there is only one item to choose from, the user feels a little cheated, so it makes sense (at least for this demo) to automatically select that single item. This addition makes the “cascading effect” very real, and you can try it out in the demo database. The entire module is as follows:
Option Explicit
Private Sub Cascade(pcbo As ComboBox)
' requery and auto-select if only one item is in the list
With pcbo
.Requery
If .ListCount = 1 Then .Value = .ItemData(0) Else .Value = Null
End With
End Sub
Private Sub cboFirstLetter_AfterUpdate()
Cascade cboSecondLetter
cboSecondLetter_AfterUpdate
End Sub
Private Sub cboSecondLetter_AfterUpdate()
Cascade cboThirdLetter
cboThirdLetter_AfterUpdate
End Sub
Private Sub cboThirdLetter_AfterUpdate()
Cascade cboProduct
End SubDancing Combos
In some cases, the data model creates indirect relationships between tables. For example, a user can filter a list of products by choosing a category, or a supplier. If both filters are used, it would make sense to show only the suppliers who actually supply at least one product in that category or, reversely, only the categories of all the products of a selected supplier. Finally, if no filter is used, the selection of a product could automatically document the combo boxes for category and supplier.
When several combo boxes are interconnected like that, they “dance” with one another, hence the name. This is a rather special and unusual type of user interface, and should not be used lightly. However, when done correctly an in the right context, it is a very pleasant way to navigate complex data-sets.
Dancing combos make sense only when different filtering options (here the category and the supplier) are themselves linked through a many-to-many relationship. There can be several suppliers in each category of products, and each supplier might have products in several categories. Therefore, the selection of a single category can yield a list of suppliers, and the selection of a supplier can display the list of categories of all their products. In other words, the cascading effect can go both ways, and the user is free to work in the way that seems the most logical in any given situation.
Option Explicit
Public Function ReSynch(pcbo As ComboBox)
With pcbo
.Requery
If .ListIndex < 0 Then .Value = Null
If .ListCount = 1 Then .Value = .ItemData(0)
End With
End Function
Private Sub cboCategory_AfterUpdate()
ReSynch cboSupplier
ReSynch cboProduct
End Sub
Private Sub cboProduct_AfterUpdate()
cboCategory = cboProduct.Column(2)
cboSupplier = cboProduct.Column(3)
End Sub
Private Sub cboSupplier_AfterUpdate()
ReSynch cboCategory
ReSynch cboProduct
End SubThis doesn't work on a continuous form!
Indeed. This is a frequent variation on the question, one that sometimes only becomes apparent after some comments have already been exchanged. After all, why should it matter whether the form is continuous or not? The fact is that the list of items available in a combo changes after each requery, and that the same list is used for all visible records.
Anyway, this section grew so much that I chose to move it into a separate article, more focused and pertinent. It is best read after this one, as the basics are not repeated there: Cascading Combos on Continuous Forms and Datasheets.
Conclusion
Although this article is a little long, I hope you have found the answer you needed before the end of the second section. I have shown this technique with success to students on the second day of an elementary Access class. Prerequisites are only basic knowledge of queries and form design.
In practice, the problems arise when trying to implement a complex (needlessly complex) solution to achieve the desired result. Just keep it simple, and you will get it working in no time. The more elaborate examples require just a little longer, but what a pleasure when your combos dance in tune!
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: