Row Limit
By Richard Rost
3 years ago
Fix Combo Box Row Limit in Access. Speed Up Loading
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.
Abigail from Edmond, Oklahoma (a Platinum Member) asks: I have about 500,000 customers in my database. For some reason, I can't see all of the records in the table. What do I have to do to get all of the records in there? When I scroll down to the end, the list is cut off. Also, the combo boxes take forever to load (especially since I'm pulling data across a network). How can I speed this up?
Prerequisites
Links
Recommended Courses
Coming Up
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
Get notifications when this page is updated
Intro In this video, we will talk about the combo box row limit in Microsoft Access, including why combo boxes and list boxes are limited to about 65,000 records and what you can do when you have more data, like Abigail's 500,000 customer database. We'll look at strategies to get around this limitation, including filtering your data, using queries to only show active customers or the current customer, and ways to reduce load delays when working with large tables. We'll also discuss techniques like cascading combo boxes and how to set up filters to improve performance.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, we're going to talk about the combo box row limit. Most of you probably never ran into it because it's 65,000 records, but sometimes it happens. We're going to take a look at that, how to get around it, and we'll talk about shortening the load delay, because sometimes those combo boxes with lots of rows in them can take forever to load. So, you ready? Here we go.
Today's question comes from Abigail in Edmond, Oklahoma, one of my Platinum members. Abigail says, "I have about 500,000 customers in my database. For some reason I can't see all of the records in the table. What do I have to do to get all of the records in there? When I scroll down to the end, the list is cut off. Also, the combo box takes forever to load, especially since I'm pulling data across a network. How can I speed this up?"
Well, Abigail, let's first take a look at the row limit, and then the solution for that will probably also speed up the load time. So, let's take a look.
Before we get into it, this is a developer-level video, so the solutions I'm going to show you will mostly require some VBA programming. You can do a little bit of it without that, and I'm going to show you one method, but the best methods, of course, are going to require some programming. If you've never done any programming before and you want to learn, check out my Intro to VBA video. It's about 20 minutes long and it teaches you everything you need to know to get started.
Now, first I need a database that's got lots and lots of records in it. In one of my recent videos, Turning Access Into a Database Server, I actually built a button to do that. It added like 300,000 records to my database. So, you don't have to watch this video if you don't want to, but I'm going to go grab that database to use right now. If you are interested in this series, go watch it, it's pretty good. I'll put a link down below.
Here is that database, and the purpose of this database was to have a front and back end. The back end is on the server and it runs constantly in a loop and it looks for commands. So, you can run queries that will process on the server much faster and then just send you the information that you need.
The whole reason why I grabbed this database is because I've got a customer table that I built that has, and you can see it's taking forever to load now, 300,000 records in it. Combo boxes and list boxes have a maximum of roughly 65,535 records. So, if you've got 300,000 customers in your table and you want a combo box showing all of them, you can't do it. You just can't. It's a physical limitation of Access.
But you really shouldn't be loading thousands and thousands of records in your combo box anyway. What you should do is somehow filter or reduce that number by any number of other means. I'm going to show you a couple today.
Now, what I did, if you're familiar with this database, was in the customer form, go to orders and watch how long this takes to load. See, it's loading because it's filling that combo box with records. See how long that took, and this is a local database. I pulled in the tables locally, so we're not even pulling records across the network. This is just on this PC.
You can see there are my customers. I sorted them. If you hit Control+End to come down to the very bottom, you can see that the last record is customer 65535. That's the maximum number of customers you can have, and I've got 300,000 customers in here like you saw before. So, there are a whole bunch of them that are not in there.
We need to whittle this list down. There are a lot of things you can do. I recommend basing that off of a query where you can do whatever filtering you can. For example, in my database, I've got a thing called IsActive. In this order form, obviously in this combo box, you might not want to see inactive customers.
If we look at the Row Source for this, it's SELECT CustomerID, LF FROM CustomerLFQ. If you've watched any of my previous videos, CustomerLFQ just takes last name and first name and puts it together in a query for me so I can have it in one field. So you see it in the combo box that drops down here. It is right here, CustomerLFQ. It's mad at the load because there are so many records in there, and there's your LF.
We can limit this to only show active customers. We'll just go into Design View here and we'll add IsActive, there it is. We'll set the criteria here to True. Save it, and now when I run it, you're going to get a much smaller list of records. There you go, there are about 20 of them. There's my 20 active customers. When that combo box goes to run over here, you're only going to get 20 records. See that?
Now, the problem is that if you have an order, let's say for Joe Smith, and then Joe Smith moves out of your area or he dies, he's no longer active. So, you come in here and you mark him as active as No. Now this order will not show his name in here, even though this is his order.
We can fix that with the query. This query relies upon this form being open. The order form requires the customer form to be open. If not, it can't get that value as a default value. So, all we have to do is say, "Show me all the active customers or this customer." We can do that by modifying the query in here.
Design view. Right now we've got CustomerID, LastName, FirstName, and IsActive. If we add a criteria over here to say the CustomerID has to be equal to the current customer on the customer form, or an active customer, we can get away with that. But we've got to make sure to put it down one row. Remember, it's an OR condition, down not across. If you're not familiar with that, go watch this video.
So, in here, I'll put Forms!CustomerF!CustomerID. Let me make the wider so you can see everything that's in there. So, you can get CustomerID, LastName, FirstName, IsActive, where either IsActive is True or it's the current customer. Again, this query requires the customer form to be open. Save it, close it.
Now we open this guy up. In here, now we see just the customers that are active. Much, much shorter list. And yes, it still takes a little while to run, because that query still has to chew through all the records even though it's only displaying the 20 that are active.
Let's say this is my order here. Let's close this. Let's say I'm no longer active. Close it and reopen it so it refreshes. Now, if I go back into orders, you can see I still show up in the box because the query says active people or the current customer, so you'll still see him even if you limit the records.
You can do this trick with anything that you want. You can make a form field out here that's got state on it, like out here you could put a combo box or a text box where you could put some other kind of filtering mechanism.
You could do cascading combo boxes right here. That's where you've got another combo box that limits the records, let's say by state. What's the customer state? Pick New York, and then it'll show you, well, this customer is just from New York. I've got a whole separate video on how to do cascading combo boxes - very powerful stuff.
List of cities, for example. You pick the state first, and then the city combo box only shows cities from that state. You could do the same thing in your order form.
You've got 300,000 customers. Pick the state first, or any other type of criteria that you can think of to limit them down. You've got regions, you've got sales reps, whatever, then the customer combo box will only show the customers from that state or that region.
Another method you could use is to filter the combo box results in place as you type. I have a whole separate video about this topic that goes into a lot more detail, but let me go through the basics real quick for you. Go watch this first if you haven't watched this yet.
In fact, go watch that video now because this is a good spot to break for today. Tune in tomorrow, same bat time, same bat channel, and we'll continue on with part two.Quiz Q1. What is the maximum number of records that a combo box or list box in Microsoft Access can display?
A. 65,535
B. 500,000
C. 100,000
D. Unlimited
Q2. Why is it generally not advisable to load thousands of records into a combo box?
A. It increases the file size
B. It causes slow load times and may hit the row limit
C. It is more difficult to write VBA code for large lists
D. It creates duplicate records
Q3. What is a recommended method to reduce the number of records shown in a combo box?
A. Using a query with filters based on certain criteria
B. Increasing the row limit in Access settings
C. Printing the combo box data to a report first
D. Sorting the records in descending order
Q4. In the example provided, which field is used to filter for only active customers in the combo box?
A. CustomerID
B. LastName
C. LF
D. IsActive
Q5. What problem occurs if you filter the combo box to only show active customers?
A. Inactive customers' existing orders will not display their names in the combo box
B. The combo box will become empty
C. Duplicate customers may appear
D. The combo box sorts incorrectly
Q6. How can you ensure that an inactive customer still appears in the combo box if their order is currently being viewed?
A. Use a query that includes both active customers and the specific customer on the current order
B. Remove all inactive customers from the database
C. Increase the combo box row limit
D. Sort by order date
Q7. Why does filtering the combo box by active status still take time to load?
A. The query still processes all records, even if it only displays a filtered few
B. The combo box is corrupted
C. It is refreshing too often
D. Access does not support filtering
Q8. What is a cascading combo box?
A. A combo box that filters its list based on the selection from another combo box
B. A combo box that prints its contents automatically
C. A combo box that loads all records regardless of the filters
D. A combo box linked to a timer
Q9. Which of the following is NOT recommended to help increase the speed of loading combo box data?
A. Filtering data in a query
B. Using cascading combo boxes for additional filtering
C. Loading all records without any filtering
D. Filtering results as the user types
Q10. What type of Access object was suggested to be modified to filter records for a combo box?
A. Query
B. Table
C. Module
D. Macro
Q11. When adjusting the query for a combo box, why must you place the criteria for the current customer and active customers on separate rows in the query design grid?
A. To use an OR condition between criteria
B. To apply a NOT condition
C. To join tables together
D. To group results
Q12. What is the advantage of using a front-end and back-end database configuration as mentioned in the video?
A. Queries can be processed on the server for faster performance
B. It prevents the need for combo boxes
C. It automatically removes the row limit
D. It increases security for combo box contents
Answers: 1-A; 2-B; 3-A; 4-D; 5-A; 6-A; 7-A; 8-A; 9-C; 10-A; 11-A; 12-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 covers the row limit found in combo boxes in Microsoft Access. While most users never encounter this limitation, it can become an issue when working with large datasets. The combo box in Access is limited to displaying a maximum of 65,535 records, which can present a significant challenge if your database contains hundreds of thousands of records, as in the situation described today.
Abigail, one of my students, has a customer table with roughly 500,000 entries. She noticed that when scrolling through the combo box, the list appeared cut off, and the combo box itself took a long time to load, particularly when accessing data across a network. If you have run into similar issues, you are not alone.
First, let us address the root of the problem, which is the row limit imposed by Access. If you attempt to load more than 65,535 records into a combo box or list box, Access simply will not display them. It is a physical software limitation. However, it is generally a poor user experience to load that many records into a drop-down list anyhow. Most users will not scroll through tens or hundreds of thousands of entries, and doing so causes significant performance delays.
Instead, the best practice is to reduce the number of records shown in the combo box by filtering the data. There are several ways to accomplish this. One method is to base your combo box on a query that only selects records that are relevant to your situation. For instance, if you only want to see active customers in your combo box, your underlying query should filter out any customers marked as inactive. In my sample database, there is a field named IsActive, and you can simply set the criteria in your query to only include customers where IsActive is true.
After applying this filter, if you only have 20 active customers, then only those 20 records will appear in your combo box, resulting in much faster load times.
However, there is a potential issue if you filter out inactive customers. Suppose you are looking at an old order from someone who has since become inactive. In this case, the customer will not appear in the drop-down list even though they should be associated with the order. To handle this scenario, you can modify your query to include either all active customers or the specific customer attached to the current order, regardless of their status. This approach ensures that records belonging to inactive customers still display properly when needed.
You can further enhance your database forms by adding more flexible filtering options. For example, introducing another combo box to select a state first will let you limit the customer list to only those in the selected state. This technique is known as cascading combo boxes. You pick a state in one box, and then the city combo box only displays cities within that state. The same concept can be applied for regions, sales representatives, or any other filtering criteria that makes sense for your data structure.
Another powerful method is dynamic filtering as the user types in the combo box. This way, the list of available options reduces in real time based on user input, making it much easier to find a specific record among thousands. I have a dedicated video covering this approach in detail.
If you are working with a very large dataset and finding that your combo boxes are slow to respond or incomplete, remember that filtering and properly structuring your queries are the keys to making them both efficient and user-friendly.
For a complete video tutorial with step-by-step instructions demonstrating everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Access combo box row limit of 65535 records
Filtering combo box records by active status
Modifying Row Source queries for combo boxes
Displaying inactive but selected customers in combo box
Using OR conditions in query criteria
Referencing form fields in query criteria
Speeding up combo box load time by filtering
Adding form controls to filter combo box records
Overview of cascading combo boxes
Filtering combo boxes by state or region |
