Smart Combo Boxes
By Richard Rost
16 days ago
Build Faster, Smarter Microsoft Access Combo Boxes
In this video, we will talk about how to build smart combo boxes in Microsoft Access that improve form performance by only loading the records you actually need. You will learn how to adjust a combo box's record source based on context, such as viewing an existing record, adding a new one, or editing, which helps avoid loading thousands of unnecessary records. We'll cover step-by-step how to set up different RowSource conditions in your forms with VBA, using customer data as an example, and show how this technique makes your Access databases faster and more user-friendly.
Joe from Princeton, Minnesota (a Platinum Member) says: [My favorite Access trick is] Using the NewRecord property to change the SQL RowSource of combo boxes. Example: Only active employees should show up for new records but you have to see them all for existing records (who entered this order). Example: Only active shipping companies should show up for new invoices but you have to see all for existing records.
Members
There 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!
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, smart combo box, fast combo box, filter combo box data, dynamic RowSource, show active customers, customer combo box performance, Me.NewRecord property, On Current event, subroutines, change RowSource VBA, filtering customers, network database speed, TechHelp free template
Intro In this video, we will talk about how to build smart combo boxes in Microsoft Access that improve form performance by only loading the records you actually need. You will learn how to adjust a combo box's record source based on context, such as viewing an existing record, adding a new one, or editing, which helps avoid loading thousands of unnecessary records. We'll cover step-by-step how to set up different RowSource conditions in your forms with VBA, using customer data as an example, and show how this technique makes your Access databases faster and more user-friendly.Transcript If your Microsoft Access forms feel slow and clunky, chances are your combo boxes are loading way more data than they ever need to.
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today we're going to talk about building smart combo boxes in Microsoft Access that load faster by only pulling in the records you actually need.
Instead of loading thousands of customers every time a form opens, we'll look at how to change a combo box's record source based on the context, whether you're viewing an existing record, creating a new one, or editing an old one, and how that can dramatically improve both the performance and usability of your forms.
Once in a while I post these little conversation starters in my blog, in the captain's log, or on social media, just something to get a conversation started about something Access-related. Recently I posted one that said, "What is your favorite trick or shortcut in Access that saves you time?"
So once in a while I post these little conversation starters on my website, on social media, YouTube, whatever, just to get people talking. Like recently I did one that says, "What's your favorite trick or shortcut in Access that saves you time?" I got a ton of responses. I'll go over some of the other ones in a Quick Queries. In fact, I think I did one in a previous Quick Queries, but this one's really good.
This is Joe, one of my Platinum members. Joe said, "Using New Record to change the SQL RowSource of a combo box." For example, show only active employees for new records, but you have to see them all for existing records so you know who entered the order, for example. Or, active shipping companies would show up for new invoices, but you have to see all the ones for existing records.
That's a great idea. In fact, I'm going to take it one step further.
All right, let's say we have our customers and we've got our orders here. This customer combo box is on the order form. When we open up an existing order, if you just want to view the order, all we really need to load into this box is the current customer who's the customer for that order. That will save tons of time not loading thousands of records into this combo box, especially if you've got 100,000 customers. Why load them all in?
Now, if you go to a new record and you want to add a new order, then we'll load in all the active customers. Here, I just made me and John Newk active. But we'll give the user the option to see all of them, and it'll load the box with all of them.
See? Or we can switch back to active. And if you do load up one for an existing customer and you do decide you want to change that, you just click the active and you can pick a different active customer, or you can click the all and pick all of them.
This will save load times, especially if you're running your database over a network or the internet and you don't want to load this combo box. If you've got three or four different combo boxes on a form and you're pulling thousands of records across that, it could slow things down.
So let me show you how all this works.
But first, some prerequisites.
First, this is a developer level lesson. If you've never done any programming with VBA before, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes.
I'm going to use my TechHelp free template, which is the database that I use for most of my videos. Go watch this invoicing video if you want to see how that was built. There are actually three videos. There's the blank template. There's the contact management. The third one is invoicing, where I show how I build the order form and all that stuff. Go watch this so you know what the database is.
Go watch my Fast Tip video on the Me.NewRecord property, which we're going to look at in just a minute. You should know what the On Current event is. It's the event that runs when you move from record to record or when you first open a form. You'll need to know how that works, and we're going to make a few subroutines and some other little things, but I'm going to walk you through it step by step.
These are all free videos. They're on my YouTube channel. They're on my website. Go watch those and then come back.
All right. So here I am in my TechHelp free template. This is a free database you can get off my website, but you know that already because you just watched my invoicing video.
First thing I'm going to do is go into my customer table and we're going to mark a bunch of these customers not active. I'm going to keep me and maybe Will Ryker and Picard, of course, and we'll just turn a bunch of these other ones off. So we have a whole bunch of people that are not active.
All right, we've got three active customers. That's it. Oh, we'll leave Frodo in there too. Well, just Frodo. We'll turn these ones off. I want to show you that the combo box will be basically empty. There are like four people in it.
Now let's take a look at that RowSource for the customer combo box in here. Let's take a peek, open her up. Go to Data. There it is right there. I'm going to zoom in, Shift F2. Whoa, my zoom window got really, really big. Hang on. Let me resize this.
Okay, there we go. This uses the Customer LFQ, which is the little Last First Query. We can clean this up since we're only using one query in here. We can get rid of that. Get rid of the brackets since we have no spaces. Get rid of all this. We're just going to make this much, much easier to read.
Okay, that's the simplest form of customer LF, which is last name, comma, first name from that query, order by LF. Now I'm going to take this right here. I'm going to cut it out because we're going to remove it from here and then hit OK. Why bother loading anything in the RowSource when the form loads if we're just going to force something else in there ourselves? So the form is going to load. This will be empty, which is good. You don't want something in here like the whole table or the whole giant query. It's going to load that first and then you're going to reload it with something else.
All right, save it.
Now, there are going to be three states that this combo box is going to be in right there. The current customer only - customer ID has to match. All active customers or all customers. So we need three different ways to adjust this combo box.
I'm going to go in the code for this box. I have a little button up here that just opens up the form module for this particular form that I'm working on. Slide it over here.
Any way you get in here is fine. Or if you have a button here with some other code in it, just right-click, go to Build Event, or you can open up the form properties; there are a million ways to get into here.
This is where I want to be. I want to be in the form module for OrderF. This is code that's inside this form. We're going to make three subroutines to show what we want to show.
The first one, Private Sub, is going to be ShowAllCustomers. This is going to say CustomerCombo.RowSource - that's the RowSource property - equals (now where was our SQL statement?) Oh, it's in my clipboard. There it is.
ShowAllCustomers is going to do this. It's going to show all the customers, which will then load into that box. Beautiful.
Now I need two more. I'm going to copy this, copy, paste. Next, we're going to say ShowActiveCustomers. Now we need a WHERE condition. Down here, I'm going to break this into two lines now. I'm going to say WHERE IsActive equals True, and then ORDER BY. I like to put the ORDER BY on its own line too. I can do it like this when I'm writing stuff. In fact, let's keep it all consistent: I put the FROM on the next line, and the ORDER BY on its own line too. It just makes the query easier to read.
Now, IsActive needs to be in our query. I don't think it is. Let's go put it in there.
Here's the Customer LFQ, design view. Yep, it's not in there. So let's just add IsActive right there. When we run this we see an IsActive field. Beautiful. Save it. Close it. Back to our code now. So this should work.
I need one more. We need to show the current customer. So this will be ShowCurrentCustomer. I forgot to put the FROM down on the next line. Let's do that. There, you can see how much easier that is to read.
For the current customer, our WHERE condition is going to be where the CustomerID equals whoever the CustomerID for this order is. So, CustomerID equals, and then put CustomerID and a space. Don't forget that space. Everyone forgets the space. If you don't have a space after that, your ORDER BY is going to slam right into that guy. So make sure you get that extra space there.
Sometimes what I do if I'm getting lazy is I just do this: I put the spaces in front. That way I make sure I don't forget them. But I'm not being lazy today.
All right. So we have our three conditions: we have active, we have all, and we have current. Now we just have to implement them.
All we're going to do is use a button. The only time you need to see all of them is if you want to change it. Maybe you accidentally put an order in for the wrong customer and you have to switch it to someone who's inactive because it's three years old. So we're going to make a button to do that later.
For now, I either want to see the current customer if there is a CustomerID, if it's an active record, or, if we're on the new record, show active customers so we can pick from the active customer list. We're going to do that in the form Current event. And we're going to do that in tomorrow's video, along with creating those two buttons so we can switch it to all customers or only active customers.
Tune in tomorrow - same bat time, same bat channel. Or you can join as a member and you can watch it right now, because I'm going to record it right now and it'll be up in a few minutes.
Do me a favor while you're here: click on that like and subscribe. It really helps the channel out. Thanks.
So that's going to do it for your TechHelp video for today, brought to you by AccessLearningZone.com. We looked at how loading thousands of records into a combo box can slow your forms down and how using smarter RowSource logic lets you load only what you actually need. Bring only what you need to survive. This is my industrial strength hair dryer and I can't live without it. Where was I?
By changing what the combo box shows based on whether you're viewing an existing record, adding a new one, or editing one, you can make your databases feel faster, cleaner, and easier to use.
In the next part, coming up in part two, we'll finish wiring all this together by implementing the form current logic and adding buttons. We'll let the users switch between active customers and all customers on demand.
As always, leave a comment down below. Let me know what you thought of today's video and tell me how you plan on using smart combo boxes in your databases.
So that's the TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.Quiz Q1. What is the main reason for making combo boxes in Microsoft Access "smarter" as described in the video?
A. To reduce the number of visible records in the database
B. To make forms load faster by only pulling in necessary records
C. To improve the visual appearance of combo boxes
D. To make combo boxes compatible with older versions of Access
Q2. According to the video, which situation justifies loading all customer records into a combo box?
A. Viewing any record on the form
B. Editing an old order, possibly for an inactive customer
C. Adding a new order for an active customer
D. Viewing customer details in a report
Q3. What VBA property is set to change which records appear in the combo box?
A. BoundColumn
B. ControlSource
C. RowSource
D. ValueList
Q4. What event in Access was mentioned as important for handling record changes on a form?
A. OnOpen event
B. AfterUpdate event
C. OnCurrent event
D. OnLoad event
Q5. What was Joe's suggested trick for improving combo box efficiency?
A. Sorting combo box values by first name
B. Using New Record to change the SQL RowSource of a combo box
C. Locking inactive records to prevent selection
D. Hiding combo boxes until needed
Q6. Why is it beneficial to keep the RowSource of a combo box empty when the form first loads?
A. So that users cannot select any values initially
B. To reduce initial load time by not retrieving unnecessary records
C. To force the user to manually enter data
D. To comply with SQL syntax rules in Access
Q7. Which of the following is NOT one of the three states for the customer combo box described in the video?
A. Showing the current customer only
B. Showing all active customers
C. Showing only customers with email addresses
D. Showing all customers
Q8. What field does the video suggest should be added to the query to filter for active customers?
A. EmailAddress
B. LastName
C. IsActive
D. OrderDate
Q9. What is the main performance issue with loading thousands of records into multiple combo boxes, especially when databases run over a network?
A. It can cause data corruption
B. It can slow down the loading of forms significantly
C. It can increase the database file size
D. It can prevent users from making selections
Q10. When should the ShowCurrentCustomer subroutine filter the combo box?
A. When adding a new record only
B. When viewing an existing order
C. When the database is first created
D. When deleting a customer
Q11. What did the presenter emphasize about SQL syntax when concatenating conditions?
A. Omitting spaces has no effect
B. Using spaces at the end of a line is crucial to prevent errors
C. Parentheses must be used in WHERE clauses
D. Semicolons should end all SQL statements
Q12. What action does the presenter recommend if you are not familiar with VBA or Access form events?
A. Read the Microsoft Access user manual cover to cover first
B. Watch his introduction videos on VBA, form templates, and the OnCurrent event before proceeding
C. Skip using combo boxes entirely
D. Only use macro actions, not VBA
Q13. What feature will be implemented in part two of the video series?
A. A report showing all customer purchases
B. A SQL query to automate invoices
C. Form current logic and buttons to switch between active and all customers
D. Exporting combo box data to Excel
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-B; 13-C
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 video from Access Learning Zone is all about improving the performance of your Microsoft Access forms, specifically by optimizing how combo boxes load their data. I am Richard Rost, and today I want to explain how to build combo boxes that only pull in the records you actually need, speeding things up and making your forms much more responsive.
A common problem many people have is that their combo boxes load thousands of records every time a form is opened. For instance, if your customer table has tens of thousands of records, there's no need to load every single one into a combo box when you're just viewing or editing an existing order. By loading only the relevant data for the current context, you can greatly enhance both the speed and the user experience of your database.
To get people thinking about better ways to use Access, I often share little prompts or questions in my blog or on social media. Recently, I asked what shortcuts or tricks people use in Access to save time, and I received a lot of interesting responses. One that really stood out was from Joe, one of my Platinum members, who talked about changing the row source of a combo box depending on whether a new record is being added or an existing record is being edited. For example, you might only want to display active employees for new entries but show all employees (active and inactive) for existing records so that historic orders remain accessible.
I think that's a fantastic idea, and I want to show you how you can take this even further.
Let's take the example of a customer combo box on an order form. When an existing order is opened, there is really no need to load all customers into the combo box. You only need the customer linked to that order. This keeps things quick and efficient, especially if your database has tens or hundreds of thousands of customers.
When you create a new order, that's when you want to load all active customers into the combo box, allowing the user to select from the current, relevant options. I always make sure users can switch between viewing just the active customers or all of them, depending on their needs for that particular case.
This approach is especially beneficial if your database runs over a network or the internet, as loading thousands of records at once can cause significant delays. If you have several combo boxes on a single form, each pulling in large recordsets, the performance can suffer even more.
Before diving into the implementation, there are a few prerequisites. This lesson assumes you're comfortable with VBA at a developer's level. If you're new to VBA, I recommend watching my introductory video, which covers the basics in about 20 minutes. I'm using my free TechHelp template database. If you want to follow along, check out the invoicing video series on my website, where I demonstrate how to build the order form and related components. You should also familiarize yourself with the Me.NewRecord property and the On Current event of a form, since we'll be working with these features. There are step-by-step tutorials for these topics available for free on my site as well, so review those if needed.
Now, in the example database, I start by modifying the customer table to mark many customers as inactive, leaving only a few marked as active. This sets up the scenario where our combo box should only show a small number of active customers instead of the entire customer list.
The next step is to examine the RowSource property of the customer combo box. In the past, this might have simply pulled from a query listing all customers sorted by name, but that causes the unnecessary loading of every record. Instead, I remove the default RowSource and plan to set it dynamically using VBA code, loading only the data necessary for the current context. By having an empty RowSource at first, Access does not load a big data set when the form opens.
There are three main scenarios we need to handle for this combo box:
1. When viewing an existing order, the combo box should only load the customer linked to that order.
2. When adding a new order, the combo box should show just the active customers to select from.
3. There must also be a way to load all customers, active and inactive, in case you need to switch an order to a customer who has since become inactive.
To accomplish this, I write three subroutines in the form's module:
- One routine sets the combo box to display all customers.
- Another filters to show only active customers. For this, I add an IsActive field to the underlying query to ensure we can filter records appropriately.
- The third routine loads just the customer for the current record, based on the CustomerID.
Properly formatting the SQL string for each scenario makes the routines cleaner and easier to maintain. It's important to include spaces at the right places so the SQL statements do not break.
For now, the combo box defaults to showing the current order's customer upon opening an existing record, or the active customers if you are creating a new order. Later, I plan to add buttons so the user can switch between active customers and all customers as needed.
Tomorrow, I will continue by wiring everything together. We'll use the form's On Current event to determine which state the combo box should be in and create the interface buttons that allow users to change views between active and all customers.
Remember, loading only the records you need into a combo box saves bandwidth, keeps forms snappy, and makes things easier for your users. This approach helps ensure your Access databases remain efficient and user-friendly no matter how large your data sets become.
If you found this information helpful, feel free to leave a comment below and let me know how you plan to implement smart combo boxes in your own projects.
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 Identifying performance issues with Access combo boxes
Optimizing combo box RowSource for large tables
Showing only the current record's value in a combo box
Filtering combo box to display only active customers
Filtering combo box to display all customers
Editing combo box SQL RowSource dynamically using VBA
Adding the IsActive field to queries
Creating ShowAllCustomers, ShowActiveCustomers, ShowCurrentCustomer subroutines
Using the form module to manage combo box RowSource
Understanding different combo box states on forms
Using the On Current event for combo box logic adjustments
Benefits of dynamic combo box loading for performanceArticle If you have been working with Microsoft Access and noticed that your forms are slow or less responsive than you would like, a common reason is that your combo boxes may be loading more data than necessary. This often happens when, for instance, you have a combo box set up to display a customer list and you load every single customer every time the form opens, even when you only need to work with one or two records. This can be especially problematic if your customer table is large, containing thousands or even hundreds of thousands of records. When your forms load unnecessary large datasets, it leads to slow performance and a clunky user experience.
To address this problem, you can build what I call smart combo boxes. Instead of pulling every customer every time, the combo box can be set up to load only the records relevant to the current situation. For example, when viewing an existing record, the combo box can load just the customer associated with that order. When adding a new order, only active customers are shown for selection. If needed, you can give users an option to display all customers, including inactive ones, but only on demand. This approach makes your forms load faster and improves usability.
Let me walk you through how you can set up smart combo boxes in Microsoft Access and dramatically improve both performance and user experience.
First, suppose you have a customer table with an "IsActive" field that marks whether each customer is currently active. This is helpful for showing only the relevant customers in certain contexts. Go through your customer table and mark a few as active; the rest can be inactive for demonstration purposes.
Next, look at the combo box on your Order form, which is used to select customers. If you have been using a RowSource property that loads every customer by default, you're going to want to clear that out. You do this by opening the form in design view, selecting the combo box, and deleting the SQL statement from the RowSource property so it starts off empty. You will instead load data into the combo box dynamically based on the context.
To achieve this, you are going to need to write some VBA. The combo box should be able to support three different modes: showing all customers, showing only active customers, or showing just the customer for the current order (which I will refer to as the current customer mode).
Now, open the form module (the VBA editor for your form) and create three subroutines, each setting the RowSource property of the combo box differently according to your needs. Here is an example of how the code looks for each subroutine. Suppose your combo box is named CustomerCombo, and you are using a query called CustomerLFQ that includes CustomerID, LF (last name, first name), and IsActive.
First, for showing all customers:
Private Sub ShowAllCustomers()
CustomerCombo.RowSource = "SELECT CustomerID, LF FROM CustomerLFQ ORDER BY LF;"
End Sub
Next, for showing only active customers:
Private Sub ShowActiveCustomers()
CustomerCombo.RowSource = "SELECT CustomerID, LF FROM CustomerLFQ WHERE IsActive = True ORDER BY LF;"
End Sub
Finally, for showing just the current customer (that is, when viewing an existing order):
Private Sub ShowCurrentCustomer()
CustomerCombo.RowSource = "SELECT CustomerID, LF FROM CustomerLFQ WHERE CustomerID = " & Me.CustomerID & " ORDER BY LF;"
End Sub
Note that for ShowCurrentCustomer, the WHERE clause limits the results to the customer linked to the current order. Me.CustomerID refers to the CustomerID field on your form.
For this code to work, your CustomerLFQ query needs to include the IsActive field (if it does not already), so if necessary, open the query in design view and add the IsActive field.
Once you have these three routines, you need to call them at the appropriate times within your form. Typically, when the form loads or moves to a different record, the On Current event is triggered. In the On Current event, you can check if the form is on a new record by using the Me.NewRecord property. If it is a new record, you would call ShowActiveCustomers so the combo box is populated only with active customers for selection. If you are viewing an existing record, you can display just the current customer with ShowCurrentCustomer. Optionally, you could implement buttons on your form that let the user switch between viewing only active customers and viewing all customers. This is useful if, for example, someone wants to change an order to an inactive customer or review older data.
By using this approach, the combo box only loads the data that is needed for the current context, making your forms much faster, especially in large databases or environments where there may be network latency or slow connections.
If you would like to let the user choose between viewing active customers or all customers, you can add buttons to the form and link their click events to the ShowActiveCustomers and ShowAllCustomers subroutines. This keeps your forms flexible and responsive while preventing unnecessary data from being loaded automatically.
Implementing smart combo boxes in your Microsoft Access applications is a powerful technique. By tailoring the RowSource dynamically in VBA, you target only the data you need, improving both load times and the overall experience for your users. This method is especially important as your database grows, or if your application is shared across a network.
If you follow these steps and begin to apply context-aware loading of data in your combo boxes, you'll find your forms working much more efficiently and users will appreciate the improved speed and simplicity. |
