Microsoft Access does not have dropdown controls. Instead, it has a far superior control: the combo box.
What's the difference between a combo box and a dropdown?
The main difference is that the text in a dropdown control is locked and disabled. With a standard dropdown box, the only way to use the keyboard to select an option is to press the first letter of the option you want. If there are multiple options that start with that letter, you have to press that letter multiple times.
Some dropdown controls allow you to enter the first few letters of the option to narrow your selection. This is similar to how a combo box behaves, but there are two important differences: 1) there is no visual cue to show the user what they've already typed, and 2) if you pause too long between key presses, the filtering resets.
For example, select the dropdown below and press "M". "Maine" is selected. Wait a few seconds then press "I." Now, "Idaho" is selected. Next, press "M" then "I" in quick succession. For most browsers, this will select "Michigan."
This is not a very discoverable feature, and I imagine most non-technical users wouldn't even think to try it. To make matters worse, not all dropdowns handle this behavior the same way. Predictability is an important part of building an intuitive user interface, so this is a big negative for dropdown controls.
Combo boxes, on the other hand, are a combination of a text box and a list box. Since they are part text box, you can type text into the control and see exactly what you typed. If you want to see the available options, you can click on the down arrow to drop down the box or call the
.Dropdown method in VBA. Both of these features are easily discoverable even to non-technical users.
Benefits of Combo Boxes
In my article, Combo Boxes: Use Responsibly, I listed several benefits of the combo box control:
- Provides discoverable options for new users
- Is unobtrusive for
- Supports autocomplete
- Can be used to provide on-demand "tooltips"
- Can be cascaded together to perform hierarchical searches
- Can provide auto-updating "most frequently used" lists to freeform text fields
Many of these benefits are unique to combo boxes. Let's explore them in more detail.
Discoverable options for new users
A friend of mine growing up used to prank call the Home Shopping Network. When the operator asked how they could help with his purchase, he would respond, "I don't need any help. I'm just browsing."
Many new application users are like that. They don't want to read the manual, especially if you have one. Rather, they prefer to browse your menus, forms, and reports as they treat themselves to a self-guided tour of your facilities. Then, when they break things, they'll (occasionally) ask questions–or, perhaps, just skip to the part where they blame you for the "bug."
One way to assist users on their tour is to provide easily discoverable "context-sensitive" help. The important thing is to hide this help in plain sight, lest the new user realizes that it is, in fact, help and dismisses it as a matter of principle.
A great technique to hide your help in plain sight is to provide combo boxes with multiple columns of information explaining the consequences of selecting each option.
Unobtrusive for experienced users
Unlike dropdowns that force experienced users to fall back on the mouse or perform text-based searches without visual feedback, a combo box will not slow down an experienced user that wants to take advantage of a keyboard-driven interface.
This is sort of a follow-on from the previous point. When enabled, the "Auto Expand" property saves users from having to type out long strings of text. The autocomplete works off of the first visible column in the combo box.
You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters that you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box.
To get the most out of this feature, you should set the first visible column to be one that has high cardinality. In other words, you want that column to have a wide variety of options, with relatively few duplicates. For example, a "Full name" combo box would work better if the names were entered "last, first" as opposed to "first last." (At least for U.S. names; this varies by culture.)
It's like having an on-demand tooltip
You can display a short (1-5 character) code that represents a longer concept. If the user is not sure what the code represents, a quick click of the dropdown arrow will provide the needed context.
Cascading combo boxes for "hierarchical searches"
Let's say you have a table with a unique ID for every street in every city in every state in the United States. You could have a combo box with four columns: 1) a hidden ID column; 2) the street name; 3) the city name; and 4) the state name.
Leaving aside the fact that it would take forever to load such a combo box. And not worrying about the fact that only the first 65,535 records would be displayed in the dropdown area. You would not want to be the poor user having to choose the Main Street in Honesdale, PA from the list of thousands of Main Streets.
However, you could split the selection into three separate combo boxes. Box 1 would be the state. Box 2 would be the city. It would be filtered to only show the cities that belong to the state in box 1. Box 3 would be the street. It would be filtered to only show the streets that belong to the city in box 2.
So long as there was an index that covered the state, city, and street fields, this would perform well, too.
Auto-updating "most frequently used" lists
This is a technique I've been using quite a bit recently. It works really well with a freeform text field where there is a lot of duplicate data in the field, but the field needs to allow arbitrary text. A common situation is a "Reason" field.
Many local governments in the U.S. provide a "homestead discount" on property taxes for an individual's primary residence. To be eligible for the discount, the taxpayer must meet certain criteria: can't have homestead on another parcel; must be a resident of the taxing district; must be a residential building; etc.
When an application is denied, the clerk needs to provide a reason. We could let the supervisor manage a list of pre-approved reasons for denying an application. Or, we could manage the list for them.
- Replace the "Reason" text box with a combo box
- Set the "Limit to List" property to No
- Set "Auto Expand" to Yes
- Set "Row Source" to:
SELECT TOP 10 DenialReason FROM HomesteadApp GROUP BY DenialReason ORDER BY Count(*) DESC
There are a few caveats with this approach.
- You'll want an index on the DenialReason field
- The DenialReason field should be 255 characters or less, otherwise Access sees it as a Memo field and performance suffers
- The reasons have to be consistent enough that the top X choices are reasonably common