Combo Boxes: Use Responsibly

I think the combo box is the most wonderful control in the Access toolbox.

It is also the most dangerous.

Why are they so great?

Combo boxes provide the best signal to noise ratio of any control in Access.  You can pack a tremendous amount of content into a small footprint.  It's the rare control that works equally well for mouse-optimized user interfaces and keyboard-optimized user interfaces.

A combo box:

• Provides discoverable options for new users
• Is unobtrusive for advanced users
• 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

Why are they so dangerous?

Combo boxes are like carbon monoxide.  They are the silent killers of Access performance.  A single poorly designed combo box can bring a form to its knees.

How does this happen?  Usually it's because the developer doesn't realize how much data that combo box is pulling across the network.

A common scenario

Let's say you have a form for adding invoices.  It's a form that is bound to an Invoice table.  There are 10,000 records in the Invoice table.  After a few years of using this form, your users start complaining that it's getting slow.

The Invoice table must have gotten too big and it's slowing things down, you think.  Since this form is only for adding new records, you set the Data Entry property of the form to True, so that no Invoice records get loaded when the form opens.  You triumphantly release the new version to your users.

And the users...keep complaining.  

It turns out that the Invoice table wasn't the problem after all.  The problem is your combo boxes.  You got so excited after reading the intro to this article, that you went crazy and added combo boxes everywhere.

• A salesperson combo box with the company's 35 salespeople
• A product combo box with all 2,500 of your products
• A customer combo box populated with all 5,000 customers
• A ZIP code combo box populated with all 41,692 ZIP codes in the U.S.
• A street combo box with the 1,000 most common street names in the U.S.
• An order date combo box with every business day prefilled from 2000 - 2099
• An order time combo box with the 1,440 options from 12:00 AM - 11:59 PM (data validation, y'all!)

OK, so maybe this isn't that common of a scenario.

Of course, the actual number of records in each combo box doesn't much matter.  The point is that each combo box is its own recordset with its own call to the database.  And if any one of those underlying queries is slow, it can affect the performance of the entire form.  

I mean, do you really need to use a GROUP BY query every time you want to get the 1,000 most common street names in the country.  And, if you are going to do that by using a table that includes every instance of every street name, the least you could do is index the street name column.  C'mon, man.

Upshot

The point I'm trying to make here is not that you should avoid combo boxes.  On the contrary!  I think they're great.

Actually, let me rephrase that: I think they're grrrrrrrreat!

Just remember that they can cause real performance issues if you're not careful with them.

In upcoming articles, I'll address some of the ways we can deal with poor performance from our combo boxes, using techniques such as cascading, lazy loading, and incremental filtering.

Image by Viktor Ristic from Pixabay