Lazy Loading Combo Boxes

Don't load tens of thousands of records into a combo box. Instead, wait for the user to start typing, and then load only what they need.

Lazy Loading Combo Boxes

Father's Day is just around the corner.  

Let's pretend you have the perfect gift idea for dear, old dad.  You know you can get what you want at Walmart, but you're not sure where the item is in the store. As you walk through the front doors, the greeter says hello and asks if he can help you find anything.

"Why, yes," you say, "I know exactly what I need..."

But before you can finish your sentence, the super-helpful greeter provides you with all the information you could possibly need.

"AA batteries are in Aisle 7.

"AAA batteries are in Aisle 7.

"Aardvark lunchboxes are in Aisle 24.

"Aaron Rodgers action figures are in Aisle 16.

"ABBA CDs are in Aisle 13."

"Wait!" you interject.  "I just need to know where..."

"ABC flash cards are in Aisle 11.

"Abdominal exercise equipment is in Aisle 19."

"...I can find..."

"Abercrombie & Fitch cologne is in Aisle 24."

"...animal-inspired leisure footwear–  Wait a minute?  Did you say the Abercrombie & Fitch cologne is in Aisle 24 with the Aardvark lunchboxes? – You know what, never mind.  WHERE ARE THE SLIPPERS!"

"Above ground pools are in Aisle 1."

Resigned to defeat, you pull out a Snickers candy bar.  You're not going anywhere for awhile.

When you load a combo box with tens of thousands of records, this is what you are doing to your users.  No user in their right mind is going to start scrolling through those options one at a time to find what they are looking for.

A better approach is to metaphorically wait for the user to tell you which aisle their item is in.  Practically speaking, this means waiting for your user to enter one or more characters into the combo box before you start presenting them with options.

You can then use these characters to filter the list of items in the dropdown box to a much more manageable number.  As an added bonus, you won't make your user wait for all the items they don't care about to be loaded into the combo box.

"Zantac is in Aisle 3."

...zzz... ...zzz...

"Zebra slippers are in Aisle 1.

"Ziploc bags are in Aisle 24."

"Whoa, whoa, whoa.  Back up.  Where did you say the zebra slippers are?"

Are they, though?

You see, a lazy greeter waits for the customer to tell him what they're looking for.  And only then does he tell them where to find it.  That saves the customer a lot of time.  Sure it's not as impressive as memorizing (in alphabetical order!) the name and location of thousands of items.  But it is more helpful.

So, the next time you think about preloading a combo box with tens of thousands of records, I want you to do something for me.  I want you to stop.  And I want you to think, "Zebra slippers."

P.S.  If you want to learn the code you need to implement this technique, join me on Friday, June 18, 2021, at 9 AM EDT for the Eastern Time Access User Group monthly webinar.

UPDATE [2021-07-31]: If you missed the webinar, you can still get the code to implement this technique here:

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.

Photo by Fabio Bracht on Unsplash

All original code samples by Mike Wolfe are licensed under CC BY 4.0