Row Limit 2
By Richard Rost
3 years ago
Fix Combo Box Row Limit in Access. Part 2
This is Part 2 of 2. In this Microsoft Access tutorial, you will learn how to overcome the combo box row limit and optimize the loading speed for large datasets. Discover techniques to display and Access all your records without being cut off, along with strategies to enhance the performance of combo boxes, allowing for quicker data retrieval across a network.
Members
There is no extended cut, but here is the database 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 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Overcoming combo box limit, display all records, shorten loading delays, optimize combo box, faster loading, large dataset handling, reduce loading time, increasing row limit, optimize for large datasets, improve performance, Access query optimization, filter records
Subscribe to Row Limit 2
Get notifications when this page is updated
Intro In this video, we continue working with Microsoft Access combo box row limits by adding programming to the OnChange event to filter records dynamically based on user input. I will show you how to require a minimum number of typed characters before filtering, reset the combo box when the filter condition is not met, display a visual loading indicator with DoCmd.Hourglass, and improve performance by removing sorting from the combo box. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part two of my combo box role limit series, part two of two - just two parts. If you haven't watched part one yet, go watch it. You'll find a link down below and then come on back.
We now join the program already in progress.
All right, so what we can do is - and this is where the programming part of the lesson comes in for this combo box - we're going to go to Events. We're going to find the OnChange event. OnChange fires whenever you change the data in the box, even if you just type a character.
All right, dot dot dot, that'll bring up the code editor.
Now, in here we're going to say - and I cover this in the other video - it's going to be customerCombo.RowSource equals "SELECT CustomerID, LF". Go to the next line. "FROM customerLFQ" (that's our query). "WHERE" - here's where you put the filter part in - "LF LIKE" and it's going to be double double quote, asterisk, quote to close that part of the string, and "customerCombo.Text". Remember, it's .Text because you want the actual text in the combo box that the user has typed in, not the Value of the combo box itself, which normally is an ID number. So you want the text in the box. Then open up the quotes again, asterisk, double quotes, space, quote. I know, lots of double double quotes in there. I have a whole video on that too.
For those of you who don't know, this becomes a double quote inside the string.
Then our ORDER BY. LF. Summary, call and close it up.
So that's going to query the combo box every time you type a letter in there and only show records that have that in there.
Then we'll do a customerCombo.DropDown to open up the box form.
Now, I don't necessarily want this firing every time they type in a character unless they've typed in at least three characters. If it's just one character, you're going to get all the a's or all the i's or whatever it is. That's not enough. So for this to fire, I want it to be after they've typed in at least three characters, or whatever number you specify.
Let's tab all this in and indent it a little bit. We're going to come up top here. We're going to say:
If the length of the customerCombo.Text is greater than two, meaning they've typed in at least three. If you like, you can say 'greater than or equal to three', whichever you want. Then it'll do this stuff.
Else, we're going to blank it: customerCombo.RowSource equals nothing. We're going to just have nothing in the box. Or if you prefer, you can have it equal to just the customer on the customer form if you want to. "SELECT CustomerID, LF" (same thing) "FROM customerLFQ" where "CustomerID equals Forms!CustomerF!CustomerID". What did I forget? Oh, this little guy.
Here we don't need to drop it down, there's just one record.
What I'm going to do to make things faster is I'm going to take this and put this in the regular RowSource for the combo box. So right here, we're going to do the same thing: "FROM customerLFQ WHERE CustomerID = Forms!CustomerF!CustomerID", just like that.
In fact, we'll just leave it like that. That's fine. I'll just say you can go directly to the table and concatenate the stuff yourself, but now this is good.
All right, hit OK. Let's close it, save it, open it.
Orders - okay.
Notice how fast that opened, by the way. It's only pulling one record now, see? But if I want to change it to somebody else, I can type in, let's say, 000. Drop it down now and look, there's all the other 000s in there, see?
Now, from the query, since we're doing the filtering here, we can get rid of that criteria out of here for the IsActive. Let's just get rid of that because now the combo box is handling filtering all those records. So with orders, it's nice, it loads up nice and fast now because it's just showing you him. But if you want to change it, I want to type in 345, and it's going to load, but it's taking its sweet time.
Let's see here. Let's go to 000 again, what do we get? Oh, I just messed up. I just realized what I did. I'll hit Escape a few times. I'm leaving this mistake in the video. Look what I just did. This is a learning moment for both of us.
What just happened here? I took that criteria out of there, but I didn't take this one out. So now, no matter what, this query is only going to return this record. Be careful of stuff like that. Delete this. So basically now, customerLFQ is back to where it began, but it should still load up much faster because the combo box is only pulling in that one record.
Now, if I come in here and type in 123, see, it's working. It's just there's a delay there. So, I don't like that visual delay. Let me hit Escape again.
Let's come in here. Let's manually turn on the hourglass. It used to be an hourglass. Remember way back in, like, Windows 95? It was an hourglass, now it's a spinny circle. But in Access, it's called the hourglass.
So right now, immediately, we're going to say: DoCmd.Hourglass True.
And don't forget to turn it back off again. Otherwise, you'll see the spinny hourglass forever. I'm going to do it right before the drop down: False.
There we go.
Now, let's see what it looks like. Open it up. Okay, I've only got the one customer showing up in there. I'm going to type in 123. Where's my hourglass? Okay, it's still a short delay, but at least you get the hourglass now. You're only seeing records in here that have that 123.
The hourglass isn't coming up as fast as I'd like it to. It's working, it's just slow.
Now, another thing you can do, and this seems completely counterintuitive, but if you've got a combo box that's still taking forever to load, you can unsort it and it will load much faster. I know most combo boxes, you want to see things sorted, but if you turn off the sorting, it'll run faster. Let's come into here, design view, and make sure this is not sorted here. If you run it, you'll see immediately it loads up a lot faster.
Save that. Also, in the combo box itself, let's turn off the sorting in here, so we need to take out this ORDER BY line right there. In fact, let's just remove it.
Now that the box is not sorted, it's going to load a lot faster too.
Orders - all right, let's put in 123. And it's a little bit faster. Not completely, but better than it was.
You have to search a little bit more, but if you're typing in stuff like that, then it'll run faster.
I think there's all kinds of tricks you can play. If you really want, you can filter this before you even get there. Like if you've got, on your main menu, you could put a couple of different criteria on here - the state, whatever other criteria you want, sales, like I mentioned before. Then before you even get to this point, that box will be filtered down.
If you like learning this stuff, come check out my website. I have tons and tons of different developer lessons available. I'll put a link down below you can click on.
That is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I'll see you next time.Quiz Q1. What does the OnChange event in a combo box do in Access?
A. It triggers code whenever the data in the combo box changes, such as when a user types a character.
B. It only fires when a selection is confirmed from the drop-down list.
C. It is used exclusively for initializing combo boxes on form load.
D. It automatically filters records without any need for code.
Q2. Why is customerCombo.Text used instead of customerCombo.Value when filtering the RowSource?
A. .Text returns the text entered by the user in the combo box, while .Value typically holds an ID or selected value.
B. .Value does not work with SQL queries in Access.
C. .Text computes the length of entered data more efficiently.
D. .Text allows for numeric comparisons only.
Q3. Why is it recommended to wait until at least three characters are entered before filtering the combo box?
A. Filtering with fewer characters can return too many results, making the search inefficient.
B. The combo box cannot process strings shorter than three characters.
C. Access cannot filter string values less than three characters.
D. It is required by default in Access properties.
Q4. What happens if the length of customerCombo.Text is not greater than two?
A. The combo box RowSource is set to display either nothing or just the current customer's record.
B. An error message is displayed to the user.
C. All customer records are shown by default.
D. The combo box disables itself.
Q5. How can you make the combo box load faster, especially for large data sets?
A. By removing the sorting (ORDER BY clause) from the RowSource.
B. By increasing the buffer size of the combo box.
C. By adding more criteria to the query.
D. By disabling the combo box events.
Q6. What visual feedback does Access provide to indicate processing is taking place during a delay?
A. The hourglass or spinning circle cursor.
B. The form background turns red.
C. A pop-up message appears.
D. A progress bar appears at the bottom of the window.
Q7. What is the purpose of setting DoCmd.Hourglass True and then False in the code?
A. To visually show users the system is working during processing and then return to normal afterwards.
B. To pause and resume code execution based on user input.
C. To reset the combo box values.
D. To change the color of the combo box.
Q8. If a combo box still loads slowly after filtering, what is another recommended technique?
A. Filter or limit records even before loading the form using additional criteria on the main menu or elsewhere.
B. Increase the form timeout delay.
C. Replace the combo box with a list box.
D. Use a macro instead of VBA code.
Q9. What common mistake did the instructor leave in the video to use as a learning moment?
A. Forgetting to remove existing query criteria, causing the combo box to always show only one record.
B. Deleting the wrong table.
C. Removing the combo box control from the form.
D. Applying the filter to the wrong field.
Q10. What should you remember to do after using DoCmd.Hourglass True?
A. Always set DoCmd.Hourglass False after processing, to avoid leaving the busy indicator on.
B. Delete the combo box.
C. Requery the parent form.
D. Save the database immediately.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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 part two of my combo box role limit series in Microsoft Access. If you missed the first part, I recommend reviewing it before continuing so you have a full background on the topic.
In this lesson, I introduce the programming necessary to enhance our combo box's behavior. First, we work with the OnChange event of the combo box, which triggers every time the data inside the box is altered, even by typing a single character.
Here, I show how to dynamically set the RowSource property of the combo box using a SQL statement that selects CustomerID and LF from our customerLFQ query. We add a WHERE clause to filter the results using the LIKE operator, capturing whatever text the user is typing by referencing the Text property of the combo box. This ensures we match and display only relevant records. It is important here to use the Text property instead of Value, since Value generally holds the selected ID, whereas Text holds the current user input.
Every time a character is typed, the combo box updates its list to show only the records containing the entered string. We then open the drop-down list programmatically so users see the filtered choices appear as they type.
However, I recommend only enabling this filtering after the user has entered at least three characters. Filtering for just one or two letters tends to return far too many results, which may negatively affect performance and usability. We accomplish this through a simple length check on the combo box's Text property. When fewer than three characters are entered, I set the RowSource to show either nothing or just the currently selected record for that customer. Otherwise, the filtered RowSource is applied.
To further streamline the process, I suggest storing the single-record RowSource directly in the combo box's default RowSource property. This means, by default, the combo box loads only the relevant customer record – a change that significantly improves the form's loading speed.
As we observe in testing, this setup now allows for much quicker opening of the orders form, since only a single record is pulled at first. When you need to switch customers, you can type at least three characters, and the combo box will display all matches.
After updating the combo box filtering, you can safely remove the IsActive criteria from the underlying query, as it is no longer necessary. The combo box's filter now determines what records are displayed, and with only one record being shown at startup, performance remains fast.
If you notice a delay while filtering after several characters are typed, you can add an hourglass cursor to provide visual feedback. This involves briefly setting the hourglass (or spinning circle) on while the lookup occurs and turning it off once the results populate.
One more tip for improving performance: removing sorting from the combo box's RowSource and the underlying query can result in much faster loading, especially with large datasets. While sorted results are often preferable, unsorted lists can dramatically reduce load times if responsiveness is your top priority.
For even greater efficiency, you can filter the dataset on your main menu or with additional criteria before the form is even loaded. Limiting the data before the user begins searching can lead to even better performance.
If you find these tutorials helpful, I invite you to check out my website, where you'll find many more lessons for Access developers. There is a complete video tutorial covering this topic with detailed, step-by-step instructions available at the link below.
Live long and prosper, my friends.Topic List Using the OnChange event for combo box filtering
Dynamically updating RowSource with SQL in VBA
Filtering combo box based on user input text
Requiring minimum character count before filtering
Clearing RowSource for insufficient input length
Setting RowSource to a single record using form reference
Improving combo box performance by limiting records
Removing redundant criteria from queries
Using DoCmd.Hourglass to show a loading indicator
Disabling sorting to speed up combo box loading |
