Dynamic Filtering 2
By Richard Rost
21 days ago
Dynamic Filtering to Speed Up Combo Boxes, Part 2
In this Microsoft Access tutorial, we'll continue working on dynamic filtering for combo boxes to improve loading speed and user experience. I'll show you how to update combo boxes automatically as you move from record to record using the form's Uncurrent event, avoid redundant code by creating a dedicated subroutine, and ensure combo boxes refresh correctly. We'll also learn how to synchronize multiple combo boxes so that changing one updates the other, and how to add a hidden column for easier value tracking. 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, dynamic filtering of combo boxes, combo box performance, unique last name query, customer orders form, uncurrent event, form load event, subroutine optimization, rowsource update, requery combo box, after update event, string concatenation, double quotes in SQL, hidden column combo box, column widths, synchronized combo boxes, alphabetical filter, network performance, SQL Server combo box
Transcript
Today's part two of my dynamic filtering of combo boxes so they load faster and all that good stuff. Go watch part one and then come on back.
So yesterday we made our last name query where we have a unique list of last names and then we've got our customers and our orders. We've got the combo box here that doesn't really do anything yet, and then we set it so the customer combo box only loads one record, the record that you're on.
Now we need to make it so that this also updates if you allow yourself to move from record to record. Sometimes you can come straight into the order form if you allow this in your database. If you move from record to record, you have to make sure that you refresh this box too.
In addition to loading this list of customers when the form loads, we need to put it in what event? The uncurrent event. If you're not familiar with that event, I have a video on it. I'll put a link down below.
Let's go into the property or the code for the form. We've got this in form load. I want to put the same code in the uncurrent event. Do we copy and paste this block of code? No, we don't do that. If it's more than just one, maybe two lines, you don't do that. You make it its own sub.
Up here, we're going to say private sub. Let's call this load customer combo. Then we take all of this stuff and we put it up in there.
Now inside of the form load event, we just say load customer combo. We can also do that in the uncurrent event and say load customer combo. So both of those events will run that code.
Save it. Technically, you don't really even need it in the form load event because the form current event loads after form load. But I like to do this just in case in the future I decide to change this. This one will still be there to run. Does it hurt if it runs twice? In this case, no, not really.
Now, if I come into here and move from record to record, you can see that that is not updated. What happened? Oh, it's in there. It's just not refreshing.
We have to also do a refresh. Usually, just changing the rowsource of a combo box causes the list to update, but it doesn't always. The form current event doesn't make it update. So we're going to say customer combo.requery there. That will force the update.
We move from record to record, and there it goes. Now it's updated.
So we've got that there. Now, let's say we want to change this so from Neil Peart, it goes to someone else. I can't pick anybody else here because we only loaded that record. So that's where this guy is going to come into effect.
After this shorter list, if you pick someone, like I've picked Rost, I then want this box to show the Rosts.
Let's go back in here. Open this guy up. Go to events. Go into the after update event.
If I change this filter box, then I want to see the list of that customer. In the last name combo, I'm going to say when this guy's changed:
customer combo.Rowsource = select
Same select statement as before. Basically:
customer ID, last name & " " & first name, from customer T where last name equals...
Then we have to put an open quote so that's a double quote, close the string, and last name combo and quotes. We have to put another set of double quotes inside the string.
If this confuses you, that's okay. It confuses everybody. I have several videos on it. Look up my string concatenation videos and my double double quotes videos. I'll put links to those down below.
Now that we've updated the rowsource, we're going to move the cursor to it. So we're going to move to that combo box and open it up for the user as a courtesy so that they can see the options.
customer combo.setfocus
customer combo.dropdown
Save it. Debug compile. Come back out here. Close it. Open it.
This is Mr. Spock's order, but I'm going to drop this down. I'm going to pick Kirk. Oh, look at that. Look at what we have here. I see all the Kirks in here and I can pick James Kirk. I can go to a different record.
Now this isn't going to change up top here yet. We're going to get to that in a minute. But you can see how it's still got all those in there.
If I want to drop this down now and change this to a Rost, I now see all the Rosts in there. Or I can drop this down and pick Peart and go back to Neil Peart.
It would be nice if when we move from record to record or when we change this guy, this updates as well so that it doesn't confuse the user. There are a couple of ways you could do it.
When you pick the thing first after the after update, it's going to be correct up there because if I come in here and go look for it, it's going to be correct.
The problem is when you move to a different record. So in our code that updates this guy, we're going to also update this guy.
Now you could do a DLookup and look up the last name of this customer, but again, we're trying to avoid extra lookups. So what I'd like to do is sneak the last name by itself into this combo box as another column, a hidden column.
Open up the properties for this guy. Go to format. We're going to make column count three and the column widths, let's change. I don't like that 1.7. So let's make this 2, then a semicolon and a 0, so we get 0;2;0.
The first one, column 0, is the hidden ID. Column 1, the second column (I know it's confusing), is 2 inches. Then we're going to have a third column, column 2, that's also 0 inches. Change this list width here also to 2 to match this total width.
Fill that guy with an extra column. So come into here, right up top here. After last name, first name, we're going to go comma last name. So now we're loading the last name into the combo box as well.
Now we can say right here:
last name combo = customer combo.column(2)
It's the third column at 0-based index so 0, 1, 2. Don't forget up here to now say:
last name combo = ""
Setting the value to blank.
Save it. Debug compile once in a while. Yes, it's on a t-shirt. Go get one.
Close it. Open it. Let's go into orders. The filter is updated.
Let's turn the filter off down here. That goes to Kirk, and then me, and then Kirk again, and then Reynolds. You can see how the boxes are updating automatically.
If you want to change this one from Mr. Spock, get changed here because it's just Mr. Spock on that box, but drop this down. There they are. You can now pick Alex.
They are staying in tandem and they're working together just like synchronized swimmers.
There are only two of them, so literally the minimum you need to have synchronized swimmers is two.
If after this your box is still loading slow, you can add another box or some way to filter this beyond that, maybe do it alphabetically, use an A through Z box, and have it so that people have a last name that begins with P or whatever.
This should be a starting point for you to teach you how to do this, how to take this box and not load all those records every time you load this form. If you're working with Access over a network or even if you have SQL Server, this will definitely save you some time and load up your combo boxes properly.
That is going to be your TechHelp video 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 benefit of dynamically filtering combo boxes as discussed in the video?
A. It allows users to enter data more quickly by skipping filters.
B. It ensures that combo boxes load faster by reducing the number of records loaded.
C. It guarantees that no duplicate records are shown in the combo box.
D. It allows users to edit all records directly in the combo box.
Q2. Which event should you use in addition to Form Load to ensure the combo box updates when navigating between records?
A. After Update event
B. Uncurrent event
C. Timer event
D. Before Insert event
Q3. Why is it not recommended to copy and paste blocks of code into multiple events?
A. It increases typing errors in your code.
B. It makes the form run slower in Access.
C. It makes maintenance harder and introduces potential for inconsistent behavior.
D. It limits the number of events you can use.
Q4. What is the best practice shown in the video for handling code that is used in more than one event?
A. Copy and paste the code into each event where needed.
B. Write completely different code for each event.
C. Place the shared logic in a separate subroutine and call it from the necessary events.
D. Only put the code in the event that runs first.
Q5. What does calling the Requery method on a combo box accomplish in this context?
A. It deletes all items from the combo box.
B. It forces the combo box to update its list of available records.
C. It removes duplicate records from the combo box.
D. It saves the current record to the database.
Q6. When filtering the customer combo box based on a selected last name, what is being changed in the code?
A. The control source of the combo box
B. The rowsource property of the combo box
C. The tab order of the combo box
D. The default value of the combo box
Q7. What programming technique is needed when inserting string values containing quotes in SQL statements for row sources?
A. Parameter queries
B. Nested loops
C. String concatenation with multiple double quotes
D. Use of brackets to escape strings
Q8. After programmatically updating the combo box's rowsource, what additional actions does the code take for a better user experience?
A. Move focus to another form automatically
B. Move focus to the combo box and automatically drop it down for selection
C. Close and reopen the whole form
D. Disable the combo box until filtering is finished
Q9. What potential issue occurs if you only update the customer combo box and not the filter combo box after moving records?
A. The combo box may show a blank list
B. The filter may not reflect the current record's last name, causing confusion
C. The form may crash
D. The database may become corrupted
Q10. How is the problem of needing to display the last name in both combo boxes solved without performing an extra lookup?
A. By storing all last names in a hidden table
B. By joining another table in the query
C. By adding last name as a hidden column in the customer combo box's rowsource
D. By refreshing the form after every record change
Q11. What is the correct way to reference the third column (last name) in the customer combo box in VBA code?
A. customer_combo.column(3)
B. customer_combo.column(2)
C. customer_combo.column("LastName")
D. customer_combo.value(2)
Q12. What is the effect of setting column widths to 0;2;0 in the combo box properties?
A. The combo box becomes read-only
B. The first and third columns are hidden, second column is visible at 2 inches
C. All columns have the same width
D. All columns are visible
Q13. According to the video, what should you do if your combo box is still loading slowly after implementing these techniques?
A. Remove all filters
B. Add an extra filter such as an A-Z filter to reduce the list further
C. Switch to a different form
D. Increase the column width of the combo box
Q14. What is a key reason why dynamically filtering combo boxes is especially helpful when working with Access over a network or with SQL Server?
A. It enables the use of macros instead of VBA
B. It reduces the amount of data transferred and speeds up loading
C. It allows automatic backup of records
D. It makes relationships between tables unnecessary
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-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 continues our dynamic combo box filtering series, focusing on how to optimize the way combo boxes load customer data, particularly when managing larger lists for better performance. If you missed part one, I recommend reviewing it before continuing here.
Previously, we set up a query to show unique last names and created forms for customers and orders. The first combo box now presents last name options but does not perform any dynamic filtering yet. We also configured the customer combo box so it only loads the currently viewed customer's record instead of the entire customer list, which significantly improves form load times.
Now, we need to make sure the filtering logic updates properly even as the user navigates between records. In Access, this is handled by responding not only when the form initially loads but also every time the current record changes. The best event for this is the Uncurrent event, which triggers whenever the user moves to a different record.
To make our code more efficient, we avoid simply copying and pasting the same block of code in multiple places. Instead, I like to create a separate subroutine. I'll call this subroutine 'load customer combo,' and place all of our combo box loading logic there. Now we just call this subroutine both from the Form Load and Uncurrent events. Typically, the Uncurrent event will handle things after Form Load anyway, but I prefer to have both triggers just in case we need them later on. It does not hurt anything if it runs twice in this scenario.
With this setup, we should see the customer combo box update its list every time we move from one record to another. However, sometimes simply changing the combo box's rowsource doesn't immediately update what the user sees. In practice, you often need to explicitly refresh the combo box's contents with a requery statement inside the code. This forces the options list to update right away when you move between records. Once this is in place, as you navigate through the records, the available customer options update as expected.
Now, let's look at making the combo box interactive when you want to change to a different customer. Say we want to switch from Neil Peart to another customer, but the combo box is restricted to just the current record. This is where our filtering combo box for last names becomes useful. After selecting a last name, we want the customer combo box to show only customers matching that last name.
The way to do this is by changing the combo box's rowsource property to include a SQL query filtered for the selected last name. Constructing this string in VBA involves careful use of string concatenation and quotes, especially when inserting user-selected values into the SQL string. If you find string concatenation in VBA confusing, do not worry—many people do. I have videos dedicated to string concatenation and managing double quotes in VBA, which you can check out for additional help.
After updating the rowsource, I like to use setfocus and dropdown on the customer combo box. This is a small touch that automatically puts the cursor in the combo box and drops down its list, making it easier for the user to select from the filtered results.
Now, when you pick a last name, the customer combo box immediately displays all customers with that last name. You can switch between records or last names, and the choices update accordingly. However, the last name filter combo box at the top does not automatically reflect the new choice when a record changes.
Ideally, when navigation or selections happen, both combo boxes would update together so the interface remains in sync and clear to the user. There are a couple of ways to achieve this. One solution is to use DLookup to find and display the correct last name, but since our goal is to minimize unnecessary database lookups, I prefer a more efficient method.
A better approach is to add an extra (hidden) column in the customer combo box to store the last name. In the combo box properties, you adjust the column count and widths to include this extra column, setting its width to zero so it is hidden. This allows you to refer to the last name easily in code whenever needed, and you can update the last name filter box directly by assigning it the value from the hidden column. Don't forget to clear the filter box value appropriately in your loading subroutine to keep the synchronization tidy.
Once the extra column is in place and the updates are coded, you will notice that both combo boxes stay in sync no matter how you navigate or filter through the records. The selections update as expected, providing a smooth, synchronized user experience. If you still experience slow combo box loads after this setup, you can consider adding further filtering options, such as additional boxes to filter by initial letter or other criteria. This is particularly helpful if you are running Access over a network or connecting to SQL Server, where performance gains from targeted queries become even more important.
This tutorial should equip you with the knowledge to avoid loading unnecessary records every time the form opens, improving speed and usability for your users. For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Handling dynamic filtering of Access combo boxes
Using the Uncurrent event to refresh combo boxes
Creating a reusable subroutine to load combo data
Assigning combo box rowsource via SQL statements
Using Requery to refresh combo box contents
Implementing After Update event for dynamic filtering
Setting combo box focus and dropdown programmatically
Adding hidden columns to combo box for extra data
Configuring combo box column count and widths
Synchronizing related combo box controls on a form
Populating combo box with a SQL select statement
Updating one combo box based on another's selection
Article
If you are working in Microsoft Access and have forms with combo boxes that pull data from large tables, you may notice performance issues as your database grows. Loading thousands of records into a combo box every time a form or record loads can be slow and inefficient. A more dynamic approach is to filter combo box data so that only the necessary records are loaded when needed, rather than all at once. Here, I will guide you through building a dynamic, synchronized combo box system for more responsive Access forms.
Imagine you have a table of customers and a set of orders. A typical order entry form might allow you to select a customer, perhaps filtered by last name. You might start with two combo boxes: one for last names, and another for customers. The first step is to create a query that provides a unique list of last names. This query feeds your Last Name combo box, letting users narrow down their selection before the Customer combo box loads the full customer list filtered by that last name.
Initially, you might have set up your Customer combo box to only load the customer corresponding to the current record. However, users may want to move from record to record on the form, and it is important to update the combo boxes dynamically as navigation occurs. This means that in addition to loading the filtered list of customers when the form opens, you should also reload it whenever the current record changes. In Access, the Uncurrent event is ideal for this. It fires every time you move from one record to another, so it is perfect for refreshing your Customer combo box.
Rather than copying code into both the Form_Load and Form_Current (or Form_Uncurrent) events, best practice is to write a private subroutine for loading the Customer combo box and call it from both events. This keeps your code clean and maintenance easier. For example, create a procedure at the top of your form module like this:
private sub LoadCustomerCombo()
' Your existing combo box loading code goes here
end sub
Then, in both the Form_Load and Form_Current events, simply call LoadCustomerCombo. While technically Form_Current will always run after Form_Load, meaning you could get away with one, it is harmless if the initialization code runs twice. In some scenarios, you might modify the event triggers later, so it is safer to include both.
Sometimes, after updating the row source of a combo box, it does not refresh the visible list automatically, especially in some event sequences. To force an update and make sure the options are immediately available, call the Requery method on the combo box:
customer_combo.Requery
When you keep these pieces in place and you move from record to record, the Customer combo box will always reflect the customer for that record.
Now, you probably want to allow users to pick a different customer from the combo box, but currently, the box only loads the current record. The solution is to tie the Customer combo box to the Last Name combo box. When the user picks a last name, you filter the Customer combo source to show all customers with that last name. This is done in the AfterUpdate event of the Last Name combo box.
Here is a VBA example of how you might construct the SQL for the Customer combo box rowsource in the Last Name AfterUpdate event:
customer_combo.RowSource = "SELECT CustomerID, LastName & ' ' & FirstName FROM CustomerT WHERE LastName = """ & last_name_combo & """"
Notice the use of quote marks in the criteria. Since VBA and SQL strings use quotes, you need double quotes to wrap the criteria, and then more quote marks within the string for SQL. This can get tricky, but if you follow the pattern and test your results, you will get the hang of it.
After updating the rowsource, it is a nice touch to set focus to the Customer combo box and pop it open for the user with:
customer_combo.SetFocus
customer_combo.Dropdown
This moves the cursor and expands the dropdown, making it clear to the user that they can now select a customer from a filtered list.
As users move around records or pick different last names, you want both combo boxes to stay synchronized. Without additional work, moving to a different record updates only the Customer box, not the Last Name filter. One way to fix this without extra slow DLookup queries is by including the Last Name as a hidden column in the Customer combo box. Increase the Column Count property to three, with widths something like 0;2;0. This means the first column (the ID) is hidden, the second column (customer name) is visible, and the third column (last name) is hidden but available for code to reference. Set your RowSource SQL like this:
SELECT CustomerID, LastName & " " & FirstName, LastName FROM CustomerT WHERE ...
Now, whenever you move records, you can set the Last Name combo to match the customer by writing something like:
last_name_combo = customer_combo.Column(2)
Remember that column indexes are zero-based, so (2) refers to the third column.
When you reset or clear the filter, you may also want to clear the Last Name combo by setting:
last_name_combo = ""
With these additions, both combo boxes will update in tandem whether the user moves through records or filters by last name. Your form will load much faster because it only queries the limited set of records needed for the current context, rather than thousands at once. If you still need faster loading or even more granular filtering, you can add further layers, such as an A-Z selector or filtering by first letter.
In summary, building dynamic, synchronized combo boxes in Access is a great way to streamline form performance, especially across a network or with a backend like SQL Server. Keeping your code modular and maintaining synchronization between controls will help prevent confusion and make your forms easy to use and maintain.
|
