Combo Boxes

5 Advanced Combo Box Techniques to Take Your Access Forms to the Next Level

Increase the combo box's "target area," lazy load your data, add progressive filtering (for multiple columns!), and implement dead-simple cascading.

Mike Wolfe

Jun 16, 2021 • 5 min read

Here are a few of the techniques I will be demonstrating for the monthly Eastern Time Access User Group webinar at 9:00 AM on Friday, June 18, 2021. Visit the ET Access User Group page to register for the webinar.

Increasing the Combo Box "Target Area"

When designing a user-interface that is optimized for the mouse, one key consideration is the "target area" of your controls. The larger the target area, the more user-friendly the control is. The dropdown target area for a traditional combo box is rather small: it's just the little box on the far right of the control with the down-pointing arrow.

One way to make the combo box more user-friendly is to increase the effective target area of the dropdown feature by forcing a dropdown when the user clicks on the label or inside the text area of an empty combo box control.

Both combo boxes can be dropped down by clicking the down arrow; the enhanced one can be dropped down by clicking anywhere within the combo box or on the attached label (if there is no value in the box).

I've implemented this code two different ways: as a function and as a WithEvents class module.

You can call the function version from the Property Sheet Event tab:

Calling the EnterCombo() function from the OnMouseUp and OnKeyUp events.

You can call the WithEvents class module from the form's code-behind module:

Private mComboBoost As New weComboTargetSizeBoost

Private Sub Form_Open(Cancel As Integer)
    mComboBoost.Init Me.cbMyCombo
End Sub

Lazy Loading Combo Boxes

Loading every single entry of a combo box with tens of thousands (or more!) entries is a recipe for poor performance (just ask the Walmart greeter). A better approach in that situation is to force the user to enter some minimum number of characters before populating the dropdown box.

Here's an example of that feature in action:

The combo box is not populated with options until the user enters at least three characters.

Here is all the code we need to implement the above functionality:

Private MinThreeLookup As New weComboLookup

Private Sub Form_Load()
    MinThreeLookup.Initialize Me.cbMinThree, 3
    MinThreeLookup.UnfilteredRowSource = _
        "SELECT US_State " & _
        "FROM US_State " & _
        "WHERE US_State Like '**' ORDER BY US_State; "
End Sub

Cascading Combo Boxes

When you have a well-defined data hierarchy, cascading combo boxes provide a quick way for your users to navigate the various levels of the hierarchy.

Here's an example of the technique in action:

Each combo box only gets populated when the user chooses a value from the previous combo box(es).

Do you like the idea, but think it's more work than it's worth? What if I told you that you could implement the above functionality with only two lines of code?

Private mCascade As New weCascade

Private Sub Form_Open(Cancel As Integer)
    mCascade.Cascade Me.cbState, Me.cbCounty, Me.cbPlace
End Sub

No, seriously. That's the entirety of the calling code. Don't believe me? Tune in to the webinar on Friday to see for yourself.

Progressive Combo Box Filtering

What if–instead of tens of thousands of records–you merely have a few hundred. In that case, it probably makes sense to load all the records in the combo box when the form opens. But that's enough records that you may want to filter the options as the user enters text.

Here's what that may look like:

Make your combo boxes work like Google.com by implementing progressive filtering.

How much code do we need to call the above feature? How does two lines of code sound?

Private mStateLookup As New weComboLookup

Private Sub Form_Load()
    mStateLookup.Initialize Me.cbStateLookup
End Sub

Multi-Column Progressive Combo Box Filtering

Now this one is just plain awesome. Progressive combo box filtering techniques are pretty common on the internet. But how many of them allow progressive filtering of every column in the dropdown box?

What's the three-letter code for the Chicago airports again?

And, of those that do, how many can be implemented with only two lines of code?

Dim mAirportLookup As New weComboLookup

Private Sub Form_Open(Cancel As Integer)
    mAirportLookup.Initialize Me.cbAirport
End Sub

OK, What's the Catch?

Surely, there's more to each of these implementations than just a couple lines of code, right? Of course there is.

But that's the beauty of encapsulation. All the logic is tucked away inside a handful of class modules.

The actual code can get a little hairy inside the class modules, but that's OK. Most of that hairiness is a result of years of playing "edge case whac-a-mole." Write the class module, call it from a bunch of forms, log some errors from unusual circumstances, handle the unusual circumstance inside the class module. Lather, rinse, repeat.

The encapsulation ensures that the calling code stays clean and readable. And with each newly-handled edge case, the class module gets better and better.

This is cool and all but...DO YOU HAZ TEH CODEZ?

This is where I end my article like a sixth grade book report. If you want to know how the story ends, you need to read the book! And, by "read the book," I mean you need to join me this Friday for the live webinar. Details below:

Image by Rahul Pandit from Pixabay