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
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
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
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).
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:
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
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
Much to the users surprise, when selecting the “Escargots de Bourgogne” (Burgundy snails), the category “Seafood” is selected... (What? Seafood? Seriously?) Joke aside, this is often a much appreciated enhancement to cascading combos: the ability to navigate “upstream”, so to speak.
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 ExplicitPrivate Sub cboCategory_AfterUpdate() cboProduct.Requery cboProduct = NullEnd SubPrivate Sub cboProduct_AfterUpdate() cboCategory = cboProduct.Column(2)End Sub
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:
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
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.
Dim strSQL As String strSQL _ = " SELECT ProductID, ProductName" _ & " FROM Products" _ & " WHERE CategoryID = " & cboCategory cboProducts.RowSource = strSQL
Finally, if the control used is a text box or a combo allowing `not in list´ items, this practice opens the door to SQL Injection. OK, this isn't a serious threat in Access, but every safety guide about SQL will warn you about dynamic SQL build directly from user input. The concensus is that parameters should be used.
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 = strSQL
With that change, the code at last produces the same end result as the simple `requery´ did before. And nothing more.
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.
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
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:
Private Sub cboSupplier_AfterUpdate() cboProduct.Requery If cboProduct.ListIndex = -1 Then cboProduct = NullEnd Sub
But since the exact same code is used in the other `after update´ event, we can call a common method for both. Finally, a “With / End With” construct makes it even more modular. This is the complete module at this point:
Option ExplicitPrivate Sub SynchProducts() With cboProduct .Requery If .ListIndex < 0 Then .Value = Null End WithEnd SubPrivate Sub cboCategory_AfterUpdate() SynchProductsEnd SubPrivate Sub cboSupplier_AfterUpdate() SynchProductsEnd Sub
The next step could be to change the method into a function, and to call it directly from the event properties of each filtering combo:
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:
Option ExplicitPrivate 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 WithEnd SubPrivate Sub cboFirstLetter_AfterUpdate() Cascade cboSecondLetter cboSecondLetter_AfterUpdateEnd SubPrivate Sub cboSecondLetter_AfterUpdate() Cascade cboThirdLetter cboThirdLetter_AfterUpdateEnd SubPrivate Sub cboThirdLetter_AfterUpdate() Cascade cboProductEnd Sub
The common portion of all event handler has been moved into a general Sub: Cascade.
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 ExplicitPublic Function ReSynch(pcbo As ComboBox) With pcbo .Requery If .ListIndex < 0 Then .Value = Null If .ListCount = 1 Then .Value = .ItemData(0) End WithEnd FunctionPrivate Sub cboCategory_AfterUpdate() ReSynch cboSupplier ReSynch cboProductEnd SubPrivate Sub cboProduct_AfterUpdate() cboCategory = cboProduct.Column(2) cboSupplier = cboProduct.Column(3)End SubPrivate Sub cboSupplier_AfterUpdate() ReSynch cboCategory ReSynch cboProductEnd Sub
There is nothing mysterious about the code, but notice that each combo does indeed affect the two others. This makes much more sense while playing on the form.
This 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.
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
This article is part of the EE-bookunrestricted Access.