RecordCount
By Richard Rost
4 years ago
See 1 of 6 with RecordCount & AbsolutePosition
In this Microsoft Access tutorial I will teach you how to show what record you're currently on and how many total records there are in your Microsoft Access form using the RecordCount and AbsolutePosition properties of a form's RecordSet.
Pre-Requisites
Recommended Courses
Links
Concepts
- Form_Current
- Me.Recordset.AbsolutePosition
- Me.Recordset.RecordCount
- Form_ApplyFilter
- Me.Recordset.MoveLast
Notes
- And yes, I know there is a Count property, and a CurrentRecord property for a Form. Why do I use the Recordset properties then? Well, the Form.CurrentRecord property is fine, but the Form.Count property doesn't work right if you filter the form. Try it.
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, recordset, recordcount, movelast, applyfilter, absoluteposition, Get Record Count, Count the number of records, record count, current record
Subscribe to RecordCount
Get notifications when this page is updated
Intro In this video, I will show you how to display the current record number and the total record count for a form in Microsoft Access using a combination of form controls and simple VBA code. We will talk about creating custom navigation buttons, setting up an unbound text box to show record information, using the RecordCount and AbsolutePosition properties of the form's recordset, and addressing quirks with filtered forms by updating the record pointer. This video is intended for Access developers with some VBA experience.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
This is a Fast Tip Developer Edition for the developers out there. This is not for the beginners; it's for people who have some VBA experience under their belt. If not, go watch this video. More on this later, though.
But in today's video, I'm gonna show you how to do this right here. See that? One of my longtime developers and moderators on my website, John Davy, asked me, "I'm building this database for a client of mine and I've got these little buttons that I've made," that I've shown before in some of my videos how to make. These are really easy; the command button wizard does these. Because he doesn't want to use the stock one down on the bottom here, which I don't blame him for. I don't like the way it looks myself.
But he wants to know, how do you show what record you're on and how many total records there are in the form? That's not that easy to do. So that, good people, is what I'm gonna show you how to do in this video.
Before we get started, if you haven't watched my command buttons video, go watch that right now. I teach how to make these pretty little buttons down here.
Here, I've got a customer form and I made my own little buttons just like I showed you in that other video. There's next record, previous record, first record, last record. There's add record, there's delete record. You can put all kinds of stuff on here.
Now, how do you get this thing - the current record - right in here? A lot of people don't know you can type in here, by the way. Type in, like, 15, press enter, and it'll take you to record 15. A little cute thing you didn't know.
I'll be honest, I almost never use that. But let's just say for display purposes, you want to create that because you want to put a nice big box right here to go with your big box buttons. Big buttons, a big box to go with your big button. I don't know, whatever. So how do we do that?
This is where the VBA comes in. With a little teeny tiny bit of VBA you can do this, folks. If you've never done any VBA programming before, go watch this guy. It's about 20 minutes long. It'll teach you everything you need to know to get started with VBA.
I'll walk you through it. I'll hold your hand. Well, not actually physically hold your hand. And also, go watch this guy: it's the On Current event. There are different events that fire at different times—when you open a form, when you close a form, when you delete a record, when you move from record to record. That's called the On Current event. So go watch this video too if you've never watched these. These are all free videos, by the way; they're on my website, they're on my YouTube channel, they're all free. Go watch them, then come on back.
So, we know how to make these little buttons from the button video, and we know a little bit of VBA now. What I'm gonna do is make a text box down here that's unbound. That means there's no data coming from the table; it's just a box sitting there. So we can do what we want with it. And then, when we move from record to record, we're gonna fill into that box the current record number and the total number of records - the record count.
So, design view. You open this up down here. Let me slide—we'll make it look at the other one—we'll slide this over here like that. You can grab any text box you want off of here. I'll take the State. I'll just copy and paste it, slide it down here, make it look however you want to make it look. Don't make it crazy big. We'll do this with our buttons - make our buttons the same height to try to keep them all looking nice. Maybe make this a little bit bigger.
Let's go live. Okay, like that. And then we have to go to its properties. We don't want it bound to any field, so it says control source. Take that out of there because right now it's bound to the State field. Get rid of that so now it's just an unbound box. It's got no data in it. We can put whatever we want in there.
Go to the All tab, give it a good name. We don't want to call it Text36. Never leave your controls that you're actually going to do stuff with a name like Text36. If you have some labels, like Label9, and you're not planning on doing anything with these labels, sure, fine, leave it Label11.
By default, when you bring in something into a new form off the Add Existing Fields, it has the same name and control source as the field that it's bound to. Labels usually don't; they're just Label10. If you're going to do stuff like change the colors or access this in any way in your code, then give it a good name, like PhoneLabel.
This guy, let's call it whatever we want - the RecCountBox or whatever. Okay, RecordCountBox.
And let's give it a little bit of formatting; not going to waste a ton of time on that. Let's make it centered. We'll make the text a little bigger, maybe bold. That should fit, because it's going to say something like "record 12 of 300" or whatever.
Now, where do we get the data from for this box? Let's do the easy one first. Let's do the record count. I want to know how many total records there are in the recordset under this form. Every form is bound to a table or it's bound to a query. The data underneath that form is in what's called a recordset. It could be a table, could be a query, could be a bunch of other things. That's called a recordset.
How do we get that recordset's count of the number of records in it? It's just like this. Let's go to the form's properties. I'm going to go to Events and then the On Current event. Hit the dot-dot-dot button; that's going to bring up our VBA editor. Size that—all my windows are moved around because I was doing some development work this morning.
Okay, so I'm in the Form_Current event now. Remember from the On Current event video: Form_Current runs whenever we move from record to record, including when that first record loads up. So, I want to put, in that RecordCountBox that we just created, the count of the number of records in the recordset of this form.
So, watch this. It's going to be:
RecordCountBox = Me.Recordset.RecordCount
Now, unfortunately, you lose your IntelliSense for that. You don't get anything beyond Me.Recordset, but trust me, it's a real thing. Press Enter and you'll see it gets capitalized. Why you don't get any IntelliSense for that, I'm not sure. There are properties of the recordset; you can Google them if you want to see what they are. There's RecordCount; there's another one I'm going to show you in just a minute.
But what that's going to do is count the number of records in the recordset under this form. So if it's a table with 10 records, you should see 10. If it's a query that's only showing you the customers from Florida, there might only be five in that record count, in that recordset.
Save this, go back over here, close everybody down, and then reopen the customer form. Boom. 29 records, and that should match what you see right there. That's the count of records in the recordset.
That's pretty easy to do, but now what about when I move through the records? I want to say "1 of 29," "2 of 29," "3 of 29," and so on, just like it does down here. I could turn this off, right? All right, let's go back to our code.
Let's put this aside for just a minute and come back to that. We're going to do a different one. RecordCountBox equals—the way you get the current position that you're at in that recordset is with:
Me.Recordset.AbsolutePosition
And again, when you leave that line, "AbsolutePosition" should pop up, capitalized, so you know you're right.
Where do I find all this stuff, by the way? You Google most of it. You find the recordset properties. On Microsoft's website, here they are: AbsolutePosition, all kinds of stuff in fields: RecordCount, RecordsetType, there are lots of them. I teach these in my courses as we need them because you don't need a lot of this stuff, honestly. In my almost 30-year career with Access, there are a lot of these properties I've never used, but some of them I use all the time.
I'll put a link to this page down below if you want to read more.
So you've got Me.Recordset.AbsolutePosition. Save that, come back out here, open up the customer form, and I got a zero there. What's with the zero? Well, let's move to the next record: one, two. Oh, okay, all right, it's working. But remember, AbsolutePosition is zero-based. Why? I don't know. It starts at zero. So, I've got to use:
RecordCountBox = Me.Recordset.AbsolutePosition + 1
And now you'll notice that it works just fine: one, two, three, and so on.
So now we have everything we need to say "2 of 29," for example. That's one of the reasons I love VBA. In other languages like C, you'd have to convert that over to a text value, but VBA does it all nicely for you.
We'll take that and then we'll say:
RecordCountBox = (Me.Recordset.AbsolutePosition + 1) & " of " & Me.Recordset.RecordCount
When you do that right there, VBA automatically typecasts that for you into a string value. That's kind of cool. In C, you have to be very specific about this stuff, but VBA is very forgiving, which can be bad sometimes. If you are expecting a number and Access tried to be nice and typecast it into a string for you and now it's not working, you've got to be careful with that stuff, too, sometimes.
All right, there we go. Save that and now—bam—there you go. Look at that. Pretty cool.
Now you can just get rid of this. You can turn off the navigation buttons if you want to. I do that, too, in the properties—form properties. Format and Navigation Buttons. Come back in here and voila.
You've got this big bar down here. If you turn off the scroll bar too, you won't see that big bar. Where's the scroll bar? You've got both, and for a single form, you don't need either, so I've set it to neither. Save that.
And there you go. If you've added your own delete button, you really don't need the record selector either, unless you want to give them the ability to copy and paste whole records, which I almost never do. I'll make buttons for that if they need to do that.
One other tricky thing I want to show you—this is bonus material. Time for bonus.
For this I will need to turn back on those record selectors. See what's going on here: record selector—oh, I meant navigation buttons. I get those mixed up all the time. I know which one's which in my brain; I've been doing this so long, but I always say the wrong one. I don't know why I think "record selectors" and I'm thinking this because you use those to select what record you're on. Your brain kind of thinks that way. Yes, I know this is the record selector; those are the navigation buttons, but I get them wrong all the time. In fact, in one of my early classes, I recorded the whole video and didn't realize I said them both backwards.
Now, here's the problem and it's got a really funky solution, but it works. This one took me a while to Google years ago when I first ran into this problem. This is back when Google wasn't as good as Google is today. We're talking, you know, 10 years ago. Not as much stuff out there.
Watch this: Florida.
Take a look at my customers real quick and I've got—how many customers from Florida? When I sort this: one, two, three, four Floridians.
All right, watch this: Right-click, add a filter equals Florida.
What's going on there? This says one of two. This says one of four. What's right, what's not?
The recordset data under here doesn't refresh fast enough and gives you a value of two. But if you click and it moves through the recordset at all, it comes up with "1 of 2," "2 of 4," and it puts the right value in there. If you unfilter, you're back to 29. You turn the filter back on again, you're back to 2.
How do you fix that?
What we have to tell Access is: If someone applies a filter to this form, I need you to traverse the whole recordset. Basically, we're going to say take the recordset pointer—the pointer is whatever record you're on, right? So right now I'm on record 2 of 4, 3 of 4. That's the pointer. Move it to the end of the recordset and give me that count.
Because what's happening is the recordset pointer isn't moving. The filter gets applied and it just goes, "I don't know where we are." So how do we do that? What event do we use?
When a filter is applied, go to Events. You've got Unfilter right here, dot-dot-dot. Form_Filter is the wrong one. This is another one that always gets people. You don't want the Form_Filter sub. Unfilter is called when someone opens up the filter dialog box. So you don't want Unfilter, you want On Apply Filter. Very subtle difference.
On Apply Filter happens after any filter is applied to the records at all.
In here, we're going to tell the recordset pointer to go to the end of the recordset:
Me.Recordset.MoveLast
Now, it's not actually going to change what record is displayed in the form. It's just telling the recordset pointer underneath: go to the end, and that will give you an updated count. It's kind of like when you're doing actual programmable recordsets in VB code and sometimes, depending on the cursor type that you have, you have to go to the last record before you get a count, using ADO for those of you who know what that stuff is.
Save it, give it a good compile once in a while. Debug, compile. I always do that.
Now, we're going to close this, open it back up again, "1 of 29," "2 of 29," go to the end, that's fine, go to the beginning. Now it's filtered for Florida, and look at that. It's "1 of 4." It's reading correctly now.
Unfiltered, and there you go. Notice that it doesn't actually go to the last record; it's just the recordset pointer in the background. The cursor goes to the end of the recordset so it knows how many records are there now. It's really freaky, folks.
There's a lot of behaviors in Access. When you get a program that's this complex, sometimes you get weird behaviors and that's just one of them. So you just have to know how to get around them. This is the kind of crazy stuff that I teach in my developer classes.
So if you want to learn more stuff like this, I've got—I think I'm up to 42 now—42 different levels of developer classes. They're all at least an hour long; some of them are three hours long, four hours long, because we get into these topics like this and I just go on tangents and I'll cover stuff that's weird and unexpected and unusual. I've been doing this since 1994 and it's 2022, so I've been doing this for almost 28 years now. I've seen pretty much everything that you can do with Access and I've built thousands of databases. This is what I've done most of my career, and I've been teaching for 22 years, so that's what I do in my developer classes.
We start with the topic and we just go into it, kind of like I've just been doing in this video.
So there you go. There's your Fast Tip Developer Edition for today. I hope you learned something. Give me some comments down below. Let me know what you thought, and give me thumbs up and all that good stuff. We'll see you next time.
If you want to see more developer tips like this, be sure to post a comment because most of the comments I get are from the beginner users, so I've been focused a lot on those. If you want to see more developer-type tips, I need to hear from you.
All right, thanks. See you next time.Quiz Q1. What is the main goal of the Fast Tip Developer Edition video?
A. To demonstrate how to create custom navigation buttons
B. To show how to display the current record number and total records in a form
C. To teach beginners how to use Access
D. To introduce the basics of relational databases
Q2. What kind of text box should you use to display the record count and current record number?
A. A bound text box connected to a database field
B. An unbound text box with no control source
C. A combo box pulling from a list of records
D. A label named after a field
Q3. What property lets you access the total number of records in the form's recordset?
A. Me.Recordset.AbsolutePosition
B. Me.Recordset.RecordType
C. Me.Recordset.RecordCount
D. Me.Recordset.CountRecords
Q4. In which event do you typically update the display for current record and record count?
A. On Open event
B. On Close event
C. On Current event
D. After Update event
Q5. What VBA code correctly displays "current position of total records" in the unbound text box?
A. RecordCountBox = Me.Recordset.AbsolutePosition
B. RecordCountBox = Me.Recordset.RecordCount
C. RecordCountBox = Me.Recordset.AbsolutePosition + Me.Recordset.RecordCount
D. RecordCountBox = (Me.Recordset.AbsolutePosition + 1) & " of " & Me.Recordset.RecordCount
Q6. What is important to remember about the AbsolutePosition property?
A. It starts counting from one
B. It is unavailable in Access forms
C. It is zero-based and needs to be incremented by one for display
D. It always matches the record count
Q7. Why do you need to move the recordset pointer to the end when filtering records?
A. To delete the last record
B. To search for null values
C. To ensure the record count property updates to reflect the filtered set
D. To add a new record
Q8. Which form event should you use to trigger updating the record count after applying a filter?
A. On Open
B. On Close
C. On Apply Filter
D. On Unfilter
Q9. What is a best practice when naming controls that you will access in VBA code?
A. Leave the default names like Text36
B. Use descriptive names that indicate their function
C. Choose names with spaces
D. Use only single-letter names
Q10. What happens when you turn off the navigation buttons and scroll bars on the form?
A. You cannot navigate through records at all
B. Only users with admin rights can move between records
C. The form uses only your custom buttons and controls for record movement
D. The database crashes
Q11. What is the main purpose of using an unbound text box in this scenario?
A. To store and later retrieve data from the database
B. To act as a field for user data entry
C. To display calculated or programmatically set information
D. To link directly to an existing table
Q12. According to the video, what is a common cause for mistakes between navigation buttons and record selectors?
A. They perform the same function
B. Their names are often confused, even by experienced developers
C. Only beginners make this confusion
D. They are not used in forms
Answers: 1-B; 2-B; 3-C; 4-C; 5-D; 6-C; 7-C; 8-C; 9-B; 10-C; 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 a Fast Tip Developer Edition, focusing on a technique for displaying the current record number and total record count on an Access form. This lesson is intended for viewers who already have some experience with VBA. If you are a beginner, I recommend starting with my introductory VBA tutorials before coming back to this topic.
One of my longtime developers, John Davy, raised a question about customizing navigation buttons for a client's database. Instead of using the default navigation controls found at the bottom of Access forms, John created custom buttons for navigating records. Many find the standard controls unattractive and prefer a cleaner or more personalized interface.
John's main challenge was figuring out how to display the current record number and the total number of records directly on the form, to match the look of custom navigation buttons. This is something that Access does not do automatically for you if you disable its default status bar. Today, I will show you a simple way to implement this using VBA.
First, if you have not seen my earlier video on creating custom command buttons, I suggest watching that before proceeding. In that video, I demonstrate how to make attractive navigation buttons for your forms, such as move next, move previous, go to first, go to last, add record, and delete record.
Once you have your personalized buttons in place, the next step is to create a text box that is unbound, meaning it does not retrieve or display data from your table directly. Instead, it will serve as a display box for our record navigation information. You can copy and paste an existing text box, place it wherever you like on the form, and size it to fit alongside your navigation buttons. Make sure to remove its control source so that it is not linked to any table field.
As a best practice, always give your controls meaningful names instead of using Access's defaults like Text36. For this example, I suggested calling the text box something descriptive like RecordCountBox.
With the box and some formatting in place, the next task is to fill it with the current record number and the total count of records shown in your form. Access forms are bound to something called a recordset, which is essentially the set of records being displayed, whether it comes from a table, a query, or another data source.
To access or update our new text box, we use the form's On Current event. This event fires each time the user moves to a different record or when the form is first loaded. Using VBA, you can retrieve the record count and the current position within the recordset. The property Recordset.RecordCount gives the total number of records in the current set, while Recordset.AbsolutePosition gives the zero-based index of the current record.
Because AbsolutePosition starts counting at zero, you have to add one to it to get the human-friendly current record number. So, if you want the box to show "2 of 29", the code would combine the current position plus one with the total count, formatted as required. Fortunately, VBA automatically handles converting these numbers to strings so they display correctly.
This method works well, but there is a special quirk you need to be aware of, especially when you apply filters to your form. Sometimes, after applying a filter, the recordset pointer does not update immediately, and the record count or current position can show incorrect values. To address this, you need to handle the On Apply Filter event for your form. Writing a small bit of code in this event that moves the recordset pointer to the last record ensures Access refreshes its count and updates your display accurately.
It is also valuable to know the distinction between navigation buttons and record selectors. If you have added all necessary navigation and record management buttons, and you do not want users to use Access's defaults, you can turn these off in your form's property sheet under the Format tab. This gives you complete control over the form's user interface.
This technique is just one example of the many advanced topics I cover in my developer-level classes. Over the years, I have encountered or solved just about every odd behavior or challenge you might run into with Access forms. If you are interested in deeper training, I offer a comprehensive series of developer courses with many more tips just like this.
Everything we discussed here is presented step-by-step in my complete video tutorial, which you can find on my website at the link below. Live long and prosper, my friends.Topic List Creating custom record navigation buttons in Access forms
Adding an unbound text box for record information display
Removing Control Source from copied text box controls
Renaming controls for better code readability
Centering and formatting unbound text boxes
Retrieving total record count from form's recordset
Using the On Current event to update record display
Getting the current record position with AbsolutePosition
Handling zero-based indexing for AbsolutePosition
Combining current record and total records in a display string
Disabling default navigation and scroll bars on forms
Fixing incorrect record counts when filtering records
Using the On Apply Filter event to refresh record counts
Moving the recordset pointer to the last record for accurate counts
Explaining differences between navigation buttons and record selectors |
