A Lazy-Loading Combo Box In Only 3 Lines of Code
Combo boxes that pull down too many records can slow down your Access forms. Boost performance with my class module and 3 lines of code.
If you're not careful, a single combo box can wreck the performance of your form.
Let's say your combo box is based on a list of hundreds of thousands of records. (For the sake of this article, we'll assume that cascading combo boxes are not appropriate.) If you set the RowSource so that every record in the underlying table appears in your combo box, that form is going to grind to a halt.
One way to get the advantages of a combo box without paying the severe performance penalty is to use a technique known as "lazy loading."
It's called lazy loading because when the form first opens, nothing is populated in the combo box. If you click the triangle, the drop down list will appear, but it will be empty.
The combo box will remain empty until the user enters some minimum number of characters. Then, and only then, will the combo box be populated. But instead of all the hundreds of thousands of records appearing, the code uses those first few entered characters to filter the list of possibilities. Instead of hundreds of thousands of entries, the combo box might only contain a couple of hundred entries. The smaller list will load much faster.
How To Do It
During my presentation on Advanced Combo Box Techniques, I demonstrated this lazy loading approach. The combo box is empty when the form first opens. You'll want to make sure the combo box's Row Source property is cleared out:
After the user enters three characters, the row source is updated with the matching items:
Surprisingly, it takes only three lines of code to implement this technique:
- Declaration of a new
weComboLookupobject at the top of the form's module
- A call to the
.Initializemethod in the Form_Load event
- Setting the
.UnfilteredRowSourceproperty to a SQL string or a named query
The key part is the double asterisk in the WHERE clause. Once the user enters the minimum number of characters, the text from the combo box gets inserted between these asterisks to perform the filtering.
Enter the code below in the combo box's form module. To apply this technique to multiple combo boxes, you will need separate instances of the weComboLookup object for each combo box.
Of course, to make this work, you'll also need the weComboLookup class module.
To see the technique in action, jump to the 34:43 mark of my Access User Group presentation.
Image by Masakazu Kobayashi from Pixabay