<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Cascading Combos

Published on
16,932 Points
4,432 Views
5 Endorsements
Last Modified:
Awarded
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
    cboProducts.Requery

Open in new window

the macro action 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).



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 = Null

Open in new window

Likewise, the macro doubles in size

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
    cboCategory = cboProduct.Column(2)

Open in new window

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 Explicit

Private Sub cboCategory_AfterUpdate()
    cboProduct.Requery
    cboProduct = Null
End Sub

Private Sub cboProduct_AfterUpdate()
    cboCategory = cboProduct.Column(2)
End Sub

Open in new window




Let'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!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.
    Dim strSQL As String
    strSQL _
        = " SELECT ProductID, ProductName" _
        & " FROM Products" _
        & " WHERE CategoryID = " & cboCategory
    cboProducts.RowSource = strSQL

Open in new window

Changing the row source implicitly performs a requery, quite obviously. Ah, wait, if categories aren't coded with numbers but with an alphanumeric key, the value needs to be quoted.
        & " WHERE CategoryID = '" & cboCategory & "'"

Open in new window

If the code might contain a quote, this needs to be handled as well.
        & " WHERE CategoryID = '" & Replace(cboCategory, "'", "''") & "'"

Open in new window

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

Open in new window

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
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:
Private Sub cboSupplier_AfterUpdate()
    cboProduct.Requery
    If cboProduct.ListIndex = -1 Then cboProduct = Null
End Sub

Open in new window

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 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 Sub

Open in new window

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.



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
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 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 Sub

Open in new window

The common portion of all event handler has been moved into a general Sub: Cascade.



Dancing 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 Sub

Open in new window

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.

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.
5
Author:harfang
1 Comment

Expert Comment

by:developingprogrammer
thanks for sharing this harfang!
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Make it easier to see the current record on your Microsoft Access forms! To highlight the current record with a yellow background color, use Conditional Formatting, a control to keep track of the primary key value, a control to change color, and a l…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month