Smart Combo Boxes 2
By Richard Rost
25 days ago
Build Faster, Smarter Combo Boxes in Access, Part 2
In this video, we continue working with smart combo boxes in Microsoft Access by connecting our helper functions to form events, so the combo box automatically switches between showing active customers for new records and the current customer for existing ones. You will learn how to add buttons that let users toggle between viewing all customers or just active customers, enhancing both performance and usability in your database forms. This is part 2.
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
Links
Recommended Courses
Keywords
TechHelp Access, Smart Combo Boxes, row source context, form events, toggle active customers, toggle all customers, dynamic combo box, filter by active, filter by current record, form current event, load performance, setfocus, dropdown, anchoring, format painter, customer selection, dynamic filtering, advanced combo box
Intro In this video, we continue working with smart combo boxes in Microsoft Access by connecting our helper functions to form events, so the combo box automatically switches between showing active customers for new records and the current customer for existing ones. You will learn how to add buttons that let users toggle between viewing all customers or just active customers, enhancing both performance and usability in your database forms. This is part 2.Transcript This is part two of my Smart Combo Boxes series.
In part one, we talked about the idea behind Smart Combo Boxes, changing a Combo Box's row source based on the context so you are not always loading every customer. We set up the helper routines to show only the current record, active records, or all records, depending on what the user actually needs to do.
Today we are going to finish wiring the stuff together by hooking that logic into the form events, so it switches automatically between new and existing records. Then we will add simple buttons so that the users can toggle between active customers and all customers on demand.
Previously, we talked about why Combo Boxes can make your Microsoft Access forms look slow, feel slow, especially when they're loading thousands of records you do not actually need. We looked at the idea of building smarter Combo Boxes by changing the row source based on the context, showing only the current record when you are viewing an existing order, showing the active records when you are creating a new one, and keeping the option to load everything only when the user really needs it.
In the last class we set up our helper functions. Let me bring this back in here. There we go. We have our functions to set the Combo Box for all customers, active customers, or just the current customer.
Today we are going to finish wiring it all together by hooking that logic into the form, automatically switching based on whether you are on a new record or an existing one, and adding the buttons to let the users flip between active and all records on demand.
We have these written, and we have nothing in our Combo Box right now because we deleted the existing record source - the default one - because there is no sense in loading up the form and then loading 5,000 records in here, and then having it switch to just two. So right now you will see that guy is empty.
We are going to go into the form current event so that we can decide which one of these to display.
There are a couple of ways to get there. I like to just come in here and go to form. Now, it brings you to the form load event; that's the default event. I do not like that, but I'm just going to pick current. You can also go in through the form properties, whichever way you want. Then I'm going to delete that empty form load that we do not need. Does it hurt anything by sitting there? No, not really.
So what's the logic? Well, the logic is: if I'm on a blank new record, if me.newrecord, in fact, then what do I want to show? Well, this means I'm adding a new record, and I want to give the user the option to pick from a list of customers. I do not need all the customers because new records are not going to go for inactive customers. So just show me the list of active customers - show active customers.
Else, I'm on an existing record, an existing order, so just show the current customer - just the customer for that order because we already have the customer ID.
Save it, debug compile once in a while, close it, close it. I'm going to close that, too, open it back up, go to Orders, and there's me. Beautiful. Let's go to James Kirk, open it up. There you go. Even though he's not active, it still shows his record because the where condition is customer ID based.
Now, what happens if I have to change this guy? I have to change this order. Well, we're going to make some buttons. So down below here, I can show all customers or active customers - all in case you want to switch it to, let's say this order is a year old, you need to change it, you made a mistake back then. So we have to give the user the ability to select both of those.
Let's make a couple of buttons. I'm going to slide this down.
In fact, one thing I like to do with these subforms - I did not always do this in the initial videos - I like to click arrange, anchoring, and then stretch down and across on subforms like this because then they can get bigger and smaller if you have a lot of items.
We're going to do this. We're going to actually shrink this up a little bit, just like that, and then slide this up. What that does is watch. See, we can go... that's the stretch there. I have a whole separate video on anchoring. Go watch that if you want to learn how that works.
I'm just going to copy this button here, copy, paste, paste, slide this over here like this. This will be my all button. I'm going to double-click on that corner; that will shrink that button up. Maybe make it a little bit smaller. You can change the font; you can do all kinds of stuff. Make it look pretty. I put nine. I like to make these buttons with really small text so that I cannot read them.
Let's slide this one over here; this one is going to be my active button. I use the format painter from this guy. I always keep the format painter on my quick launch toolbar in every application because I'm always constantly using it. Do the same thing there. Make that smaller.
Let's give these buttons good names. Double-click; this will be the all button. This will be the active button.
Now we've already done the hard work. We already have those subs written. So I'm going to right click, build event.
In here, what do we have to do? Show all customers. I noticed when I hit enter, it did not capitalize. I did not get my Pascal case, my camel case, whatever you want to call it. That's my visual cue that I typed it in. Just re-enter, see, there we go. I love that. I really do not like languages where the variable names are case sensitive. I'm so used to this. It's a great visual cue that you did not type something in. I used to program in C, C++ all the time, and those are case sensitive languages. So 'this' is different from 'This', which I think is dumb, but that's just me.
In addition to that, let's give the user a little help. We know they're going to want to pick a customer, so let's do customercombo.setfocus and customercombo.dropdown. Let's open up the box for them. Yes, I have separate videos on both of those topics. Check them out.
Let's do the same thing with the other button, the active button. Click build event. I'm just going to copy this stuff and paste it in here. I always say I do not like to have a lot of duplicated code, but stuff like this is pretty simple, so I'm not too worried about this. Now we're going to change this to active customers. Sometimes I will type in characters all in caps like that just to make sure I can see it switch like that.
Save it, debug compile once in a while, close it, close it, open it.
Alright, got James Kirk. I need to change this and put the wrong customer in. Is it an active customer? I think so. I'm going to click active. Look at that, it drops down the box and shows me the active customers. Look at that. Or how about all? There we go.
Notice when I clicked active, James Kirk actually disappeared because he's not in the list. What you could actually do to make it so that does not confuse your user is add him in as well when they click active. So if there is a customer ID, and to do that, we'll just adjust the SQL a little bit. So active customers where isactive equals true. Then we'll just throw in here 'or customerid = customerid' and a space. Do not forget that space. Everybody forgets the space. Now at least this way, if there is an ID, if I drop this down, if I go to active, now he's in that list as well. So you are seeing active plus the current user. That's a nice little enhancement. Little things like that can really confuse your users. They click the button and their record disappears, and they wonder what happened. That's why.
You can pick someone from all, or active. Pretty cool. I like it. Now your boxes are loading a whole lot faster than they were before. If you were loading all, you know, all 33. Let's pretend we got 50-some thousand. In my database, I think I've hit the 60,000 mark of customers since I began doing this in 2002, and they're not all active customers. I wish, but over the last 20-some years. I had to do this in my database a while back because I noticed that pulling across the network, even with SQL Server, it will run slow if you pull everybody in there, so keep that in mind. That's just another tip to speed things up.
If you want to learn more about speeding up your combo boxes, check out some other videos. You have this one on dynamic filtering. This is one where you would put in a last name filter and it will then filter this guy. You could do this with what I just showed you today too. For your all or your active search, you can also have a last name filter. Do them together.
As I mentioned earlier, here is that video on set focus and I have one on dropdown - those two things we did earlier. If you search for a particular topic in Microsoft Access and one of my videos does not show up on Google or YouTube, I want to know about it. Let me know and I will make a video.
If you liked what we did here with the smart combo boxes and controlling what loads and what does not, you will probably really enjoy my Access Developer 37 course. In that class, we go way deeper into advanced list box and combo box techniques, including letting users dynamically choose which fields they want to see at runtime. We work with recordsets, multi-select list boxes, table depth, query depth, and we build forms that can adapt to any table or query without hard coding field lists. It's all about building flexible, high performance interfaces that are easier to maintain and scale, especially in real world databases with lots of data. So check it out and I will put a link down below.
That's going to do it. That is your TechHelp video for today brought to you by AccessLearningZone.com. We looked at how smart combo boxes can dramatically improve both performance and usability by loading only the records you need, switching automatically based on context, and giving users simple controls to see active or all records when necessary.
If this helped you, post a comment down below and let me know what you thought of today's video and how you are going to use these techniques in your own databases.
That's all for today. I hope you learned something. Live long and prosper my friends. I'll see you next time.Quiz Q1. What is the main concept behind "Smart Combo Boxes" as discussed in this video?
A. Loading every single record into the Combo Box regardless of context
B. Changing the Combo Box's row source based on the current context to load only necessary records
C. Sorting Combo Box entries alphabetically every time
D. Automatically deleting inactive records from the Combo Box
Q2. Why is it a bad idea to always load all customer records in a Combo Box in Microsoft Access forms?
A. It increases the file size drastically
B. It makes the forms look and feel slow, especially with thousands of records
C. It results in data corruption frequently
D. It prevents Combo Boxes from being sorted
Q3. In the Smart Combo Box design, what does the Combo Box show when you are adding a new record?
A. All customers, including inactive ones
B. Only the active customers
C. Only the current customer associated with the order
D. No entries at all
Q4. When viewing an EXISTING order, which records should the Combo Box display?
A. All customers in the database
B. Only active customers
C. Only the current customer for that order
D. Customers from the previous order
Q5. What event in the form is used to determine whether to show active customers or just the current customer in the Combo Box?
A. Form load event
B. Form current event
C. Button click event
D. After Update event
Q6. What user interface enhancement was suggested to allow users to manually switch between active and all customers?
A. Adjusting the row source automatically with a timer
B. Adding buttons to let the user select between active and all customers
C. Using keyboard shortcuts only
D. Removing the Combo Box completely
Q7. What additional feature was added to ensure users do not get confused when the current customer is inactive but needs to appear in the list?
A. An error message stating the record is missing
B. Including the current customer in the active list by adjusting the SQL
C. Sorting the list to place inactive customers at the top
D. Preventing edits to existing orders
Q8. What Microsoft Access feature was recommended to make subforms and controls resizable as the form is resized?
A. Automatic anchoring and stretching
B. Locking the controls
C. Hiding subforms
D. Using fixed-width forms only
Q9. When programming the button to show all customers, what user-friendly step was added after updating the Combo Box's row source?
A. Displaying a message box with instructions
B. Setting focus to the Combo Box and opening the dropdown automatically
C. Closing the form automatically
D. Highlighting all customers in red
Q10. What advantage does loading filtered or context-based Combo Box rows provide in a large database?
A. Makes the database smaller in size
B. Improves performance and usability by only loading necessary records
C. Allows more users to log in simultaneously
D. Increases the security of the database
Q11. What should you do after making changes to your code, as recommended in the video?
A. Only test it once
B. Immediately run compact and repair
C. Save and debug compile your code periodically
D. Skip saving until all changes are complete
Q12. What was a suggested video for learning more about combo boxes in Access?
A. A video on exporting reports to Excel
B. A video on dynamic filtering in combo boxes
C. A video on creating pivot tables
D. A video on using Data Macros
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-A; 9-B; 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 video from Access Learning Zone is part two of my Smart Combo Boxes series.
In the previous lesson, I introduced the concept of Smart Combo Boxes in Microsoft Access. The idea is to improve form performance by updating the Combo Box's row source based on the context, so you are not always loading every customer in your database. We set up helper routines to display only the current record, active records, or all records as needed, depending on what the user is doing.
In today's session, I will show you how to bring everything together by connecting that smart logic to form events, allowing the Combo Box to switch automatically between showing information for new and existing records. I'm also going to add a couple of buttons to let users toggle between viewing active customers or all customers whenever they need to.
To remind you, loading a massive list of customers every time the form opens can really slow things down. Smart Combo Boxes address this by showing only what is necessary: the current customer for existing orders, the active ones for new records, and allowing access to the full list only when truly needed.
We have already built the helper routines to set the Combo Box for all customers, active customers, or just the current one. Right now, the Combo Box is empty because I removed the default row source. There is no need to load thousands of records at startup just to switch to a narrower list after the fact.
To complete the setup, I am going to place this logic into the form's current event. This way, Access automatically determines which customers to show based on whether you're working with a new record or editing an existing one. When the current record is new, I have it display the list of active customers, since it's unlikely you'd assign an order to an inactive one. When reviewing an existing order, it only shows the customer for that order.
After updating the code, I compile and test it. Opening up the Orders form, you can see that if I bring up a specific customer, like James Kirk, his record shows up even if he's currently inactive, because we're filtering based on the customer ID.
But sometimes, you need to change the customer for an old order or perhaps correct a mistake. To allow that, I add two buttons: one to show all customers, and another to show only active ones. I adjust the form's layout to make space, use the anchoring feature to make the subform resize nicely, and copy and style the new buttons. It's helpful to give these buttons descriptive names, like "All" and "Active," for clarity.
Since we already have the routines written for loading different sets of customers, wiring up the button clicks is straightforward. For usability, each button also sets focus to the Combo Box and opens the dropdown automatically, making it easier for users to get right to work. I walk you through how I prefer to do this, including a few tips for using the VBA editor and how Access visually cues you on correctly named routines.
When testing, you'll notice that clicking "Active" sometimes causes the current inactive customer to disappear from the list. To avoid confusing users if they are revising old records, I update the list to always include the current customer, even when filtering by active status. This little touch helps prevent users from thinking their selection vanished.
With these improvements, the Combo Box now loads much faster, since users are not pulling in thousands of records unnecessarily. In large, real-world databases, this kind of setup can really improve performance, especially across networks or when using SQL Server.
If you are interested in exploring even more ways to optimize Combo Boxes, I have additional tutorials covering topics like dynamic filtering, where you can filter by fields such as last name, as well as videos on specific techniques like programmatically setting focus or dropping down Combo Box lists.
For those wanting to take it even further, my Access Developer 37 course covers much more advanced Combo Box and List Box techniques. You will learn how to let users pick which fields to display at runtime, how to work with recordsets, multi-select boxes, and how to build flexible forms that adapt to any table or query. This course is perfect for anyone looking to create fast, customizable, and scalable interfaces for large databases.
That wraps up today's TechHelp video. We focused on how Smart Combo Boxes can greatly improve the performance and usability of your Access forms by loading only the records your users actually need and providing simple controls for them to manage what they see.
If you found this helpful, I encourage you to leave a comment with your thoughts or how you plan to apply these techniques 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 Hooking combo box logic into form events
Automatically switching combo boxes for new vs existing records
Adding buttons to toggle between active and all customers
Naming and formatting command buttons in Access forms
Using setfocus and dropdown for combo box interaction
Ensuring current record remains visible in filtered lists
Modifying SQL to include current customer in active list
Resizing and anchoring subforms for dynamic layouts
Copying and customizing event code for multiple buttonsArticle In this tutorial, we will walk through how to create smart combo boxes in Microsoft Access, making your forms faster and easier to use by limiting the number of records loaded into a combo box based on the current context. Rather than always pulling every customer into the drop-down, you can control which records the user sees depending on whether they're editing an existing order, creating a new one, or specifically need access to all customers, including inactive ones.
Let's first cover why this is important. Large combo boxes can make forms run slowly, especially if you're routinely loading thousands of rows. Most of the time, users only need to see a subset of the records—like just active customers when creating a new order, or simply the one customer tied to the order they're currently viewing. By making the combo box adjust dynamically, your forms will load faster and be more efficient.
You should already have a few helper routines set up. Let's assume you have three procedures: one to set the combo box to show all customers, one for just active customers, and one for only the current customer based on the order. For example:
Sub ShowAllCustomers()
Me.CustomerCombo.RowSource = "SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName"
End Sub
Sub ShowActiveCustomers()
Me.CustomerCombo.RowSource = "SELECT CustomerID, CustomerName FROM Customers WHERE IsActive = True ORDER BY CustomerName"
End Sub
Sub ShowCurrentCustomer()
Me.CustomerCombo.RowSource = "SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID = " & Me.CustomerID
End Sub
Now, we want to wire these up to the form events so the combo box switches automatically based on the record you're on. Go to your form's event handling, and choose the Form_Current event. In Access, the easiest way to get there is to open your form in design view, select the form, and open the property sheet. Under the Event tab, find On Current and add code. You do not need anything in Form_Load for this scenario.
Inside the Form_Current event procedure, add logic to check whether the user is on a new blank record or editing an existing one. Here is how you might set it up:
Private Sub Form_Current()
If Me.NewRecord Then
ShowActiveCustomers
Else
ShowCurrentCustomer
End If
End Sub
What happens here is simple. When the form navigates to a new record, the combo box will show only active customers, as you'd typically only create new orders for them. When looking at an existing order, the combo box shows just the customer linked to that order, allowing you to see or change it without sifting through a large list.
With this in place, you'll notice that the combo box no longer automatically fills up with thousands of customers when the form opens. At first, it may appear empty if you have not yet provided a context. That is fine and intentional.
Next, you might want to let users switch between viewing all customers and just the active ones—even while editing an existing order. To do this, you can add two small command buttons below the combo box: one labeled "All" and the other "Active".
To build these buttons, go into design view, use the Button control, and draw two buttons near your combo box. Name one cmdShowAll and the other cmdShowActive, or similar. Set their captions accordingly so users know which is which.
For each button, right click and choose Build Event to open the VBA code window for the Click event. In the All button's Click event, simply call the ShowAllCustomers procedure, give the combo box focus, and then drop down the list for the user automatically:
Private Sub cmdShowAll_Click()
ShowAllCustomers
Me.CustomerCombo.SetFocus
Me.CustomerCombo.Dropdown
End Sub
For the Active button:
Private Sub cmdShowActive_Click()
ShowActiveCustomers
Me.CustomerCombo.SetFocus
Me.CustomerCombo.Dropdown
End Sub
This way, when users click either button, the combo box will immediately update with the relevant list, focus will jump to the box, and the list will drop down for immediate selection.
There is an important detail to consider here: what if a user is editing an order for an inactive customer, and they click the Active button? The current customer will no longer be in the list, which can be confusing. To prevent this, you should modify your ShowActiveCustomers procedure so it also includes the currently selected customer, even if they are inactive.
Here's how you can do that:
Sub ShowActiveCustomers()
Dim sql As String
If Not IsNull(Me.CustomerID) Then
sql = "SELECT CustomerID, CustomerName FROM Customers WHERE IsActive = True OR CustomerID = " & Me.CustomerID & " ORDER BY CustomerName"
Else
sql = "SELECT CustomerID, CustomerName FROM Customers WHERE IsActive = True ORDER BY CustomerName"
End If
Me.CustomerCombo.RowSource = sql
End Sub
This enhancement means that if you're currently editing an order for an inactive customer, they'll still appear in the drop-down even when users pick Active, so they never lose sight of the present customer on screen.
If you want to help the user visually, you can also format, anchor, and resize buttons and other controls as needed in design view so your form adapts to different data sizes and looks polished. Anchoring is useful if your form will be resized; for subforms, use the Arrange > Anchoring > Stretch Down and Across options.
By dynamically filtering your combo boxes like this, you keep your forms performing well, even when your database grows to tens of thousands of records over the years. You also make the process clearer for your users, who only see relevant choices at the right time.
This is the foundation of smart combo boxes: using form events and helper routines to show only what's needed, and giving users simple controls to see more if necessary. You can further extend this by combining it with features like search filters by last name or dynamically showing fields, but even this basic setup will have a big impact on performance and usability in your Access applications.
Now you have a combo box that starts out showing only the current customer on existing records, lists only active customers on new records, and lets users flip to all customers or back to active with the click of a button. With these techniques, your Access forms will load faster, remain responsive, and make everyday tasks simpler for your users. |
