Dynamic Filtering
By Richard Rost
25 days ago
Dynamic Filtering to Speed Up Combo Box Loading In this Microsoft Access tutorial, I will show you how to use dynamic filtering to speed up loading combo boxes in your database, which is especially helpful when working with large datasets over a network or internet connection. We'll learn how to create a filter combo box using an aggregate query to display unique last names, set up your order form to only load the necessary record, and optimize your combo box row source for better performance. This video is great if you're experiencing slow form loads due to large customer tables. The step-by-step process will improve your combo box efficiency. Cassidy from Manchester, New Hampshire (a Platinum Member) asks: I built my company's Access database about 10 years ago when we only had a few hundred customers and a couple of users. Now we have well over a 100,000 customers and around 10 to 15 people in the system at the same time. Some of our forms, especially the ones with customer combo boxes, are getting really slow to load. Do you have any tips to speed this up without redesigning the whole database? MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, dynamic filtering, load combo boxes faster, large data sets, network optimization, slow combo box, filter by last name, aggregate query, unique last names, filter box, cascading combo boxes, order form filter, VBA combo box, On Load event, row source optimization, customer filtering
Intro
In this video, I will show you how to use dynamic filtering to speed up combo box loading in your Microsoft Access databases, especially when you have a large number of records and multiple users on a network. We will talk about why loading all records into a combo box can cause performance issues, and I will demonstrate how to set up a filter combo box—such as by last name—to help users narrow down results before loading them. You will also learn how to display just the active record in the combo box to make your forms open faster.
Transcript
Today I'm going to show you how to use dynamic filtering to load combo boxes faster in your Microsoft Access Databases. This is a great tip for loading lots of data over a network or especially an internet connection.
Today's question comes from Cassidy in Manchester, New Hampshire, one of my platinum members. Cassidy says, "I built my company's Access Database about 10 years ago when we only had a few hundred customers and a couple of users. Now we have well over 100,000 customers and around 10 to 15 people in the system at the same time. Some of our forms, especially the ones with customer combo boxes, are getting really slow to load. Do you have any tips to speed this up without redesigning the whole database?"
Well, yes Cassidy, this is a problem you are going to run into. If you've got forms with combo boxes in them and you are loading up all those records in that combo box, that's a lot of network traffic. Even if you upgrade to something like SQL Server, you are still dealing with all that network traffic coming down over the line to load that combo box.
The trick is to only load the data that you need into the combo box. It's like in Spaceballs when Lone Star says, "Bring only what you need to survive." It's my industrial strength hair dryer and I can't live without it. So let's see how that works.
Here's a copy of my TechHelp free template. You can grab a copy of this on my website if you want to. You'll find links down below. In here we have customers, and in our order form, this combo box where you pick the customer loads all of the customers.
Now it's not a problem for me, because I'm the only user in the database and it's local on my hard drive and there's, what, 33 customers in it? No big deal. But if this was sitting on a network and there were 300,000 customers in my table, this would definitely take a while to load.
So what you want to do is only preload what you need in there. Now, there are a couple of ways you can do it. First of all, what I like to do is if there's already a customer for this record, just load that record into the combo box. There is no reason to load them all. You already picked a customer.
Then what we'll do is we'll add a filter box before this one that lets you whittle down that set of data. For example, you could use last name. You might have 100,000 customers, but amongst those you might only have a few tens of thousands of last names. Depending on your data, you could whittle it down even more with a special set that's got like A through M and then N through Z and break it down that way. However you want to break it down, this technique works for a variety of different sources. You just have to pick what works best for you.
You could break it down by zip code first, type in the zip code, and filter the box. I'm going to show you how to do it with last name. Then last name will filter it and just show those people with that last name. You can use the same technique for all kinds of different filtering methods.
Before we begin though, this is a developer level video. What does that mean? Well, that means if you've never done any VBA programming before, do not worry. It's not hard. It's not rocket science. Watch this video. It's my intro to VBA. It's about 20 minutes long and it will teach you everything you need to know to get started.
Go watch this video on aggregate queries. This is where we can generate a query that gives us just a unique list of last names. Also, optionally go watch this video on cascading combo boxes, which is kind of what we're going to be doing today. This will let you pick a state and then it will show you just the cities from that state. Same kind of technique we're going to be using today. Today is a little more advanced though.
The first thing we need to do is make sure we have some people in here that have the same last names. So I'm going to take my last name, Rost, and we're going to make a DNA Rost and a William Rost and a Tasha Rost and a Benjamin, and let's do Kirk too. We'll do James Kirk. We have John Luke Kirk. We have Reginald Kirk and Julian Kirk. Alright, so we've got some different last names in here.
Now what we're going to do is we're going to make an aggregate query to give a listing of just the unique last names. So let's go to Create and then Query Design, and I'm going to bring in the customer table. From the customer table, we only need the last name field. We're going to hit the Totals button to make this an aggregate query and make sure it's set to Group By. Save this as my LastNameQ or CustomerLastNameQ, if you want to. That's fine.
When I run this, you can see I've got a list of only unique last names. There's only one Kirk and there's only one Rost right there, even though we know there are duplicates.
Now we'll use this to create a last name filter box on our order form.
I'm going to shrink that back up again. Let's go into here. Here's my order form. Design view. I'm going to slide this stuff down. And right up here, we're going to add a combo box that's got just those last names in it.
So we're going to go Combo Box, drop it right there. Get the values from a table or query. We're going to Queries and that LastNameQ that we just created. Next. There's only one field in it, that's fine. Next. Sort it by last name. Next. That's what it's going to look like. Sure, fine, great. Next. We're going to remember that value for later use. We're not actually storing the last name in the order at all. This is just for display and to help us whittle down the other combo box. Next. What label would you like? How about last name filter? Whatever you want to call it.
Let's make this format paint over that so it looks the same. We'll pretty it up later. Now this guy, I'm going to make it green just so the user visually knows it's a little bit different. Let's change this guy's name. Give it a good name. We don't want Combo15. Let's call it the LastNameCombo. Save it. Then we can slide this guy back up if we want.
So right now let's see what we have. Save this. Close it. Open it back up again. Just the unique last names. Beautiful.
Now, what we want to do is when this form loads up, we do not want to load all 300,000 customers in here. So we're going to control what happens when this thing loads up. The first thing we're going to do is we're going to get rid of its row source. I'm going to cut that out so I can take a peek at it real quick. Cut it out. Snip it out. Delete it.
Let's put it over in Notepad. Let me bring over my Notepad. Now this guy, first of all, is pulling off of a query CustomerLFQ and all that does is it puts last name and first name together in the same field. But we're going to bypass that query altogether. So we're going to get rid of this and just make it CustomerID.
We're not going to use LF. We're going to use last name and quote, comma, quote like that, and first name, which is basically what that CustomerLFQ does. But I did the query for the beginners to teach them how to use a query to put two fields together. We're a little more advanced now, so we don't need to use a query for something simple like that.
So this is just now from customerT. Then it's going to be ORDER BY and then last name, comma, first name. So that's our new row source. But we're not going to put it in the combo box. I do not want to load all those records up. We're just saving this for now. Let's just set it aside. We have it handy.
So now if I save this, close it, and open it back up again, there's nothing in here. That's ok. Now, if this record already has a customer in it, we're just going to load just that customer in this combo box.
Where are we going to do that? We're going to do that in the On Load event. Go to the form's properties, go to Events, go to On Load or On Open. I like On Load. Open it up in our code builder.
Here we're going to say: if IsNull(CustomerID) then we don't have a customer. So CustomerCombo (sorry, CustomerCombo is the name of that box) .RowSource equals blank. No, nobody shows up. Otherwise, we have a customer. Let's load just that customer in this combo box - much, much faster.
So CustomerCombo.RowSource equals - guess what - I had to save it over here. In fact, we don't need the ORDER BY on the end either, because you don't sort one record. Just grab this. Drop that in there. But be careful because these are quotes inside of a string, so they have to become double quotes.
So select CustomerID and then last name, comma, first name, that's our second field for the second column. Then we're going to say FROM customerT WHERE the CustomerID equals the CustomerID on the form.
Save it. Debug, compile once in a while. Close it. Open it.
So I got that customer in there. Drop that down, I see just that customer. Let's go to somebody else, make sure it's working. Let's go to Jimmy Kirk. He's got an order. There he is. He shows up.
The point at this point is, you might be going, why do we bother doing that? Because now, when you load up that order, it doesn't load all 300,000 customers into that combo box, just the one you picked. Just the person whose order that is gets loaded into that combo box, saving you a whole lot of time.
But how do you change it? How do you change that guy? That's where this guy comes into play. What we're going to do next is we're going to say, if you want to change this guy, you have to use this thing first, filter it down to Greenleaf, and then this will control what loads into here. We'll load just the Greenleafs in there. If I change this to Rost, we'll load just the Rosts in this box and you can pick one.
We'll do all of that in the next class. I'd say tomorrow, but today's Thursday, the 20th, so tomorrow is going to be Friday, which is Quick Queries Friday. We always do Quick Queries on Friday. The next video is going to be the 24th, on Monday, which is Monday, November 24, 2025. So tune in then. I'm not going to change the whole slide people.
But if you're a member, you can watch it right now because I'm going to record it right now. It's one of the benefits of being a member. Of course, if you're watching this at any point in the future, look down below for the link and you'll probably find the link to part two down there.
That's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you Monday for part two.
Quiz
Q1. What is the main problem Cassidy is experiencing with Microsoft Access combo boxes?
A. The combo boxes do not display customer names correctly
B. Combo boxes are slow to load because too many records are being loaded at once
C. The database does not support more than two users at a time
D. Customers cannot be added to the combo box
Q2. What is the key technique suggested to load combo boxes faster in Access?
A. Increase the RAM on the server
B. Load only the data you need into the combo box using dynamic filtering
C. Switch to a different database system entirely
D. Store all customer data in a separate file
Q3. Why is loading all customer records into a combo box a problem in a networked database?
A. Combo boxes have a maximum limit on record count
B. It increases network traffic and makes forms load slowly
C. Access does not allow loading multiple records
D. Combo boxes can only display up to 100 customers
Q4. What is the purpose of creating an aggregate (Totals) query in this tutorial?
A. To summarize total sales for each customer
B. To list each customer's order count
C. To get a unique list of last names for filtering
D. To delete duplicate customer records
Q5. In the example, which field does the filter combo box (LastNameCombo) use?
A. CustomerID
B. Zip code
C. First name
D. Last name
Q6. What happens when a user opens an order form and a customer is already selected for that order?
A. The combo box is disabled
B. All customers are loaded into the combo box
C. Only the selected customer is loaded into the combo box
D. The combo box shows an error message
Q7. What form event is used to control what records the combo box loads when the form opens?
A. After Update
B. On Click
C. On Load
D. Before Delete
Q8. What is the benefit of only loading the selected customer into the combo box when viewing an order?
A. It prevents users from changing the customer
B. It reduces the load time and network traffic
C. It allows multiple users to access the same combo box
D. It sorts customers alphabetically
Q9. Which technique is used to let users change the customer associated with an order?
A. Cascade delete customers
B. Edit the table directly
C. Use a filter combo box to reload the main combo box with filtered values
D. Manually refresh the whole form
Q10. What should you do if you have never worked with VBA programming before, according to the tutorial?
A. Skip this tutorial entirely
B. Watch an introductory video on VBA to get started
C. Only use macros
D. Hire a professional developer
Q11. Why is using a query to concatenate last name and first name described as something for beginners?
A. Queries are required for advanced users
B. Queries cannot be modified later
C. Advanced users can concatenate fields directly in the SQL statement
D. Using queries is always less efficient than VBA
Q12. What is the main concept behind breaking down the data before loading it into the combo box?
A. To ensure alphabetical order
B. To limit the set of records and reduce data load
C. To enforce data entry validation
D. To allow more complicated queries in the combo box
Answers: 1-B; 2-B; 3-B; 4-C; 5-D; 6-C; 7-C; 8-B; 9-C; 10-B; 11-C; 12-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on using dynamic filtering to load combo boxes more efficiently in your Microsoft Access databases. This technique is especially beneficial when you are working with large volumes of data over a network or an internet connection.
Recently, I received a question from a user who built an Access database for their company about a decade ago, back when they only had a few hundred customers and just a couple of users. Over time, the system has grown to include over 100,000 customers and 10 to 15 people using it simultaneously. As a result, certain forms, particularly those with customer combo boxes, have started loading very slowly. The user asked for tips on improving performance without a complete database redesign.
This is a common issue as your data grows. Loading every customer record into a combo box creates a massive amount of network traffic. Even upgrading to a backend like SQL Server does not solve the problem entirely, as all that data still needs to travel over the network every time a combo box loads.
The key here is to only load the data you need for your combo box. Think of it as packing for a trip - you want to bring only what you need. Instead of having every customer available in the combo box immediately, you should limit it to just the relevant records.
For example, in my TechHelp free template, the customer combo box on the order form loads all customers. If I only have 33 customers on my local machine, this is not a big deal. But if you are on a network with hundreds of thousands of customers, this approach will cause serious performance issues.
To address this, you should preload only what is necessary. If the current record already has a customer selected, just load that single customer into the combo box rather than the entire customer list. There's no need to load all records; the selected customer's information is already there.
To further improve the selection process, add a filter, such as a search or filter combo box, to help the user narrow down the choices. Filtering by last name is a popular option. Even in a list of 100,000 customers, the number of unique last names is much smaller, making filtering more manageable. Alternatively, you could filter by zip code or even split the filter into ranges, such as A–M and N–Z, depending on what makes sense for your data.
The filtering method is flexible and can be tailored to your needs. For this demonstration, I use last name as the filter criterion. Once the list is filtered, the second combo box displays only those customers who match the chosen criterion, such as sharing the same last name.
Before getting started, note that this is a developer-level topic. If you have no experience with VBA, do not worry. There are introductory resources available, including video lessons covering the basics of VBA, aggregate queries for unique value lists, and cascading combo boxes. Feel free to review those if you need a foundation first.
The first step is to ensure you have sample data with duplicated last names, since the filtering will be based on that field. I added several customers sharing the name Rost and Kirk to my table.
Next, create an aggregate query that retrieves just the unique last names from your customer table. Design a new query, bring in the customer table, and add only the last name field. Turn on the Totals function and set it to Group By to achieve uniqueness. Save this as something like LastNameQ. When you run the query, you will see only one instance of each last name, regardless of how many customers share it.
With this query created, return to your order form in design view and add a new combo box that uses this unique last name query as its row source. This combo box will later act as your filter. Name it something meaningful, such as LastNameCombo, and style it distinctly so that users recognize its purpose.
At this point, test the form to make sure the filter combo box correctly displays only the unique last names.
Now, turn your attention to the customer combo box. Instead of loading all customer records by default, clear its row source. Save the original SQL for reference, then strip it down to select only the CustomerID and a concatenated last name, first name. This is all you need for individual record display.
With the row source removed, when the form loads, the combo box will initially be empty. In the form's On Load event (or On Open if you prefer), add the logic so that if there is a selected customer for the record, only that customer is loaded for display in the combo box. If not, the combo box remains empty. This is significantly faster as you are only retrieving one record instead of the entire customer list from the server.
This approach might seem unnecessary at first, but it provides a huge performance boost. Instead of loading potentially hundreds of thousands of customer records every time you open a form, you are loading only the single relevant record.
Of course, you still want users to be able to change the selected customer. This is where the filter combo box comes into play. To change the customer, the user first selects a filter value (like last name), and then the second combo box will load just the customers matching that filter. This drastically reduces the number of records loaded and makes the process much faster and more user-friendly.
Setting up that filtering logic for when users want to pick a different customer is covered in part two of this series. If you are a member, you can access the next video immediately; otherwise, keep an eye out for its release, and you will find the link available on my website.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Dynamic filtering for combo boxes in Access
Reducing dataset size to speed up combo box loading
Creating an aggregate query for unique last names
Adding a last name filter combo box to a form
Customizing the RowSource property for combo boxes
Loading only the current customer in a combo box on form load
Using VBA to control combo box data loading
Improving form performance with targeted data loading
Article
If you are working with Microsoft Access databases that have grown significantly in size, especially when accessed over a network or an internet connection, you may notice that forms with combo boxes pulling thousands of records can become painfully slow to load. A common scenario is a customer database that started small but now holds hundreds of thousands of records. Loading every single customer into a combo box doesn't just stress your Access application; it also places a heavy burden on network traffic and overall performance.
The solution is dynamic filtering, which means you only load the necessary data into combo boxes, not the entire dataset. Instead of offering every customer every time you open a form, you display only the relevant records, bringing a huge speed improvement. Let me show you how you can do this with a practical example.
Suppose you have an order form where users have to select a customer from a combo box. With 100,000 or more customers, it's not efficient to load all customer names into the combo box. Instead, you want to load only the customer associated with the current order record. Then, if a user wants to pick a different customer, you'll provide an extra combo box that lets them filter the list, for example, by last name. This way, the main combo box will load only the customers matching the selected last name, dramatically reducing the amount of data being pulled down at any one time.
First, you'll want to create a way to filter by last name. Start by creating an aggregate query that returns only unique last names from your customers table. In Access, go to the Create menu and choose Query Design. Add your customer table, then add just the "LastName" field. Next, click the Totals button in the query design ribbon to make this an aggregate query. Make sure the "Group By" option is selected for the LastName field. Save this query as "CustomerLastNameQ." When you run this query, it'll give you a list of all unique last names.
Now, return to your order form in design view. Add a combo box at the top labeled something like "Last Name Filter" and hook its row source up to your "CustomerLastNameQ" query. When you test this combo box, you'll see it shows a unique, sorted list of last names from your customer table. Rename this combo box to something meaningful, like "LastNameCombo," so it's easy to reference later.
With the filter combo box in place, our next step is to prevent the main customer combo box from loading all customers by default. In design view, select the main customer combo box and remove its row source entirely. If you wish, copy the SQL statement for the row source somewhere safe for reference; you'll modify it to suit your needs. For efficiency, it's best that the main combo box only loads one customer: the customer associated with the current order record.
To accomplish this, add code to the "On Load" event of your order form. Open the form's property sheet, go to the Events tab, and choose the Code Builder for the On Load event. Enter the following VBA code, making sure to use your actual combo box and field names:
If IsNull(Me.CustomerID) Then
Me.CustomerCombo.RowSource = ""
Else
Me.CustomerCombo.RowSource = "SELECT CustomerID, [LastName] & ', ' & [FirstName] FROM customerT WHERE CustomerID=" & Me.CustomerID
End If
This code checks if the current record has a customer selected. If not, the combo is empty. If there is a customer, it sets the row source of the combo box to only show that specific customer. This way, opening an order does not pull down the full list of all customers, only the one needed for that order.
But how can a user change the customer for an order now? That's where your last name filter combo box comes in. In the next steps (which would typically be handled in a follow-up), you would tie the main customer combo box to the filter so that when a user selects a last name from the filter, only the customers with that last name are loaded into the customer combo box. This can be done in the AfterUpdate event of the filter combo box, updating the row source of the main combo box accordingly.
For now, with just the initial changes discussed here, you will have saved huge amounts of loading time and network traffic, especially on large databases. Instead of the combo box trying to retrieve and display every possible customer whenever the form loads, it now only pulls what is absolutely necessary. This method keeps your solutions responsive and much more scalable as your data volumes grow.
By applying dynamic filtering to combo boxes in Access, you make your forms load much faster, offer a smoother user experience, and ensure your application performs well even as your business and databases expand.
|
