Automatic Record Numbering
By Richard Rost
5 years ago
Automatic Numbering of Rows. Renumber on Add, Delete.
Learn how to have Access automatically number your records sequentially, starting at 1 and counting up. This is handy if you want to show a count of records. Renumber them when you add, edit, or delete records too.
Julian from the UK (a Learning Connection Member) asks: Is there any way to number records in order, starting with 1 and counting up? I need the list to renumber itself when I add or delete records. AutoNumbers don't work, obviously.
Members
We'll modify the form so you can add a custom sort order that YOU can control, instead of relying on Access to number the records for us. We'll add a delete button, a renumber button, and buttons to move items up and down in the sort order.
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!
 Links
Intro
In this video, I will show you how to set up automatic record numbering in a continuous form using Microsoft Access. We will learn how to create dynamic row numbers that automatically recalculate when you add or delete records, using DCount and IIF functions. I will also walk you through numbering records in both main forms and subforms, handle potential issues with deleted records, and set up a refresh to ensure your numbering stays accurate. This method works for contacts, orders, or any other list where you need sequential record numbers that update on the fly.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to automatically number the records in your continuous form. If you have contacts, orders, partners, or whatever, and you want to have them automatically numbered starting with 1, 2, 3, 4, and so on, this lesson will show you how to have Access automatically calculate that and re-number it if you delete and add new records.
Today's question comes from Julian in the UK, one of my Learning Connection members. Julian says, is there any way to number records in order starting with 1 and counting up? I need the list to re-number itself when I add or delete records. AutoNumbers will not work, obviously.
Yes, of course, Julian, there are a couple of different ways to do this. In one of my previous TechHelp videos, I showed you how to create your own custom sequential counter number, where you can start at any seed like 101 and have it count up. But this only works when you add new records, and if you delete a record in the middle, you have a gap. That's one of the problems with this method.
I used orders in this example, and one of the things I talked about in the video is it's not always wise to allow your users to delete stuff. You do not want to delete orders. Just mark them as deleted, a soft delete. Mark them as invalid, for example, or canceled. But sometimes you have a situation where deleting a record is okay, and you want this list to re-number itself.
Now, my buddy Alex put together a tip a while back called using a row number in a query. This is the method I am going to show you right now. He automatically numbers the rows using this technique with DCount. Let's see how this works.
Let's start with a copy of my blank customer template. You can find a copy of this template down below in the description. You can download it from my website. It's a free download. For this example, it really does not matter what the records are. They could be orders. They could be products. They could be anything.
So let's just take a look at our customer list. We already have them in here with AutoNumbers. Let's say we delete a couple from the middle. I have this form set up so I cannot delete items. Let's go back to Design View and make it so that deletions are allowed.
I like to make it so that you have to open up the customer record itself to delete stuff. But let's say I delete two out of the middle here. Now I have one, two, five, six. If I add someone else down here, notice it's now seven. But I have got a gap. I want a row number on here that automatically shows a sequential numbering system, so I can see exactly, for example, how many there are. I could look down here, but I want a row number. So how do I do that?
Let's go into Design View. Let's add another field out here. Let's just grab a blank text box and drop it in there. I'll just delete the label and slide it over here. This is where I'll put my counter.
Open that guy's properties up and let's call it my row number. What should the control source be? The control source is going to be equal to DCount, which is the domain count function. If you have never used DCount before, I have got other videos on DCount and DLookup. They are very similar. Go watch those videos first. I will put links down below in the description.
What do we want to count? I am going to count the number of customer records. Count any field. You could count * if you want to. This is count all the records or count a specific field like CustomerID. It's an AutoNumber, so every record is guaranteed to have one.
Where are we counting from? We are counting from the customer table. Now what's the criteria? The criteria is: I want all the records that came before this one. So the CustomerID has to be less than the current CustomerID.
OK, DCount the records. CustomerID, any field, or even a star, from what table? The customer table, where the CustomerID of that record is less than the current CustomerID.
Let's save it and close it and see what we have. Close this up. Save that. Close it and let's open up the customer list.
Take a look at this guy. How many records are less than that ID? Zero. That's correct. We just need to add one to this value now, because that's a one, two, three, four. This should be one through five. So let's add that one now.
Design view. Come back into here. Let's tack a one onto the end. Save it and let's see what it looks like now. Better.
What about this #Error at the end? I do not want to see that. If this guy over here is a new record, that means it does not have an AutoNumber yet. All we have to do is check and see if this is null. If it is, leave that null or blank. If it's not, put the calculation in there.
Design view. Let's wrap this guy inside an IIF function - the immediate if. I have videos on that. Go watch those if you have never used it.
If IsNull([CustomerID]), then, just null. Otherwise, do the DCount function, and then end parentheses at the end. I am missing something here. I have to put a parenthesis right after this guy here too. It gets a little confusing sometimes.
If IsNull([CustomerID]), then use the value null. Otherwise, use the value DCount plus one. OK, so let's save that. Close it up. Open it back up again.
There we go. Perfect. Now, if I add someone new, notice I get a six. Add someone else. There's my seven. It takes a second to pop up, but it works.
If I delete people in the middle, the numbers will recalculate themselves. You might have to requery it. If you open it up again, it will requery itself. If I delete this guy, four stays in there for a second. We could probably force a refresh or a requery.
Go into design view, open this guy's properties up, then go to the events tab, and go to On Delete. Click the dot-dot-dot button - that will open up your Visual Basic Editor. Pick Code Builder if it asks you. In here, just put Me.Refresh. That says after I delete the records, do a refresh. Recalculate anything that needs to be recalculated.
Save that. Come back out here. Save that. Open it back up again. Let's add a few records. Adding stuff is not a problem.
Now, if I delete something in the middle like this guy, that refresh takes care of recalculating the numbers. See that? There you go.
If you want to number the items in a subform, it's pretty much the same thing, but it's a little more complicated. Let's say, for example, I have this contact history.
Now, this only shows you the records that are related to the customer. If I open up the contact table, you can see there are more records in here: customer one and customer two.
So it's the same kind of calculation. You just also have to take into consideration the parent ID - the related records ID.
Let's go to that contact subform, which is this guy. We are going to do something very similar. Add some room in here. Drop in a text box.
Now, what's this guy going to be? This will be our row number. What's the control source now?
It's going to be, to start off, I like to start with the simple stuff. So, equals DCount. What am I counting? The ContactID from the contact table, where the CustomerID equals the current CustomerID, and the ContactID is less than the current ContactID. Then we have to add one to that, remember.
Actually, let's wrap that inside an NZ. I like to wrap it inside an NZ, because if that returns a null value, then you will get a zero. So, comma zero, like that. Now that whole thing, we have to add one to it. Then we can do the IF.
So, IIF(IsNull([ContactID]), Null, the rest of that).
I know it looks pretty crazy. Let me zoom in so you can get a good look at it. There it is. But if you break it down into its individual pieces, it's not too bad. It's not too hard.
You have your DCount, wrap that inside an NZ, and then put your IsNull out here and wrap that whole thing in a statement.
There are a lot of different functions here to learn. Now let's take a look at our customers with contacts. It's too big to fit in that little subform, so let's make this bigger. Open up, save that, open it back up again. There you go. 1, 2, 3, 4, 5.
We need our delete. Delete that. Oh, this one also is set so you can't delete. Let's put our delete in. Design view. I am going to change the template so we can. I think the example that I was using for this did not want deletions, but you can have deletions.
It's OK. We will put our On Delete event in here. Event On Delete. Code Builder if you need it. Me.Refresh says recalculate everything. It needs to be recalculated.
Don't mess with the copyright notice. Here we go. Boom. There it is. Delete, renumbered.
Now, there are some drawbacks to this method. You do not have a way to control this numbering at all. It's based on the ID, so you are literally going to get these in the order in which they're added to the table.
If you want, you could base that on the DateTime as long as you have unique DateTimes. If two records get added with the same exact DateTime to the second, you'll get two numbers that are the same over here.
If you want to be able to control that numbering at all, you could use dates. However, a better way to do it if you want to be able to control the numbering is to set up your own custom numbering field. But then again, you need events for when you add a record, which I showed in that other lesson, auto numbering the record. But then you also have to re-number it yourself every time you make a change if you want to change the numbering, or if you make an addition or a deletion. That I will show in the extended cut for the members.
If you want to learn more about numbering your records, there is a 22-minute extended cut for members only, Silver members and up. I will show you how to add a manual sort order so you can control what order these records appear in. Access will automatically sort them for you the first time if you want, but then you can change them.
We will make a re-number button to do a manual reorder. You can, of course, change the value if you want to by typing in a number. We will make buttons plus and minus, so you can click on a record and it will move it up or down in the order. Of course, we will have events for all those things - deleting records and so on.
That is all in the extended cut. Silver members and up get access to all of my extended cut videos. We are getting close to 100, so there is lots of material available for members.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. If you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free.
Quiz
Q1. What is the primary purpose of the tutorial in this video?
A. To create a custom AutoNumber for table primary keys
B. To automatically number records in a continuous form and update numbering when records are added or deleted
C. To create a macro for exporting data from Access
D. To assign random numbers to records for security
Q2. Why is using the built-in AutoNumber field not suitable for automatic sequential numbering that renumbers after deletions?
A. AutoNumber only works for text fields
B. AutoNumber cannot be used in continuous forms
C. AutoNumber does not fill in gaps when records are deleted
D. AutoNumber sorts records alphabetically
Q3. In the method described, which function is primarily used to count the number of records with a lower ID than the current record?
A. DLookup
B. DSum
C. DCount
D. DMax
Q4. When setting up the row number field in a continuous form, what should you do if the field being counted (such as CustomerID) is null?
A. Leave the row number field blank or null
B. Force the field to zero
C. Add one to the result anyway
D. Display an error message
Q5. Which function is used to handle cases where the counted value might be null, especially when numbering in a subform?
A. Sum
B. NZ
C. Min
D. Val
Q6. What is the purpose of wrapping the row number calculation with an IIF function?
A. To change the calculation for alternate records
B. To prevent errors by handling null values
C. To create a cascading delete effect
D. To copy values between forms
Q7. What event is recommended to use for refreshing and recalculating row numbers after deleting records?
A. On Click
B. On Load
C. On Delete
D. On Open
Q8. Where do you place the Me.Refresh command to ensure the form recalculates the row numbers when a record is deleted?
A. In the table's default value property
B. In the On Delete event procedure of the form
C. In a query's criteria
D. In the AutoNumber field property
Q9. When applying this technique in a subform (such as a contact history), which IDs must be taken into account to ensure correct row numbering per parent record?
A. Only the unique ID for the subform table
B. Both the parent ID and the subform table's unique ID
C. Only the parent ID
D. Only the AutoNumber of the main table
Q10. What is a limitation of numbering records based on their ID values as described in the video?
A. The numbering cannot be changed after initial assignment
B. Duplicate numbers can appear if IDs are deleted
C. The order is strictly determined by ID and not easily customized
D. It allows users to select their own row numbers easily
Q11. What enhancement is suggested for manually controlling the sort or numbering order in the form?
A. Using a wildcard character in the criteria
B. Adding a custom sort order field and update logic
C. Changing field types to text
D. Assigning random numbers on form load
Q12. What should you do if you want to add a button to allow the user to reorder items manually?
A. Create a new query for sorting
B. Program buttons to increment or decrement a custom order field
C. Use a report instead of a form
D. Change the default view to datasheet
Answers: 1-B; 2-C; 3-C; 4-A; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-B; 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 covers how to automatically number records in a continuous form using Microsoft Access. If you have a list of contacts, orders, or any other records and you want each one to show up with a sequential number starting from 1, this lesson explains how to do it so your numbering stays consistent even when records are added or deleted.
This question came from Julian in the UK, who wanted to know if there is a way to have a list always number itself sequentially, adjusting automatically when items are inserted or removed. As he pointed out, AutoNumbers are not suitable for this purpose, since they do not fill in gaps if records are deleted and are meant to uniquely identify records, not to always show a sequential order.
There are several ways to approach auto-numbering. In a previous video, I showed how to generate custom sequential counters starting at any number you choose. However, this approach only works for new records and will leave gaps if records get deleted, which may not be what you want.
It's important to mention that, especially for orders or important data, it's wiser to avoid allowing users to delete records outright. Usually, you should mark records as deleted instead, but sometimes true deletion is acceptable, and that's when you may want your numbering to update automatically.
A useful solution comes from a tip by my friend Alex, where he recommends using a row number created by a query. This method involves using the DCount function to count the number of records before the current one, which gives you your desired numbering regardless of record deletions.
To begin, start with any table; in the example, I used a customers table, but this applies to orders, products, or any set of records. While you may already have AutoNumbers assigned, deleting a couple of records will cause gaps in those numbers. Instead, what you want is a separate field on your form that displays the sequential row number, filling in any gaps and always showing a clean 1 to N count.
In form design, you simply add a new unbound text box where you want the row number to appear. The control source for this text box uses the DCount function. For example, you can count the number of customer IDs in the customer table where the ID is less than the one for the current record. This tells you how many records came before it. Since DCount returns zero for the first record, just add one to that value so numbering starts with one.
If you notice an error showing up at the end of your form for new records (where there is no ID yet), you can prevent that by using an IIF function combined with IsNull to check for a missing CustomerID. If the ID is null, you leave the field blank; if the ID is present, you show the calculated row number.
After making these changes and saving the form, you can test adding and deleting records. You will see that the row numbers always renumber themselves to stay sequential. To refresh the numbering instantly after a record deletion, you set an On Delete event for the form and use the Me.Refresh command, which recalculates and updates the displayed numbers whenever a record is deleted.
If you need to implement this in a subform, such as a contact history for a customer, the same principle applies with a slight modification. You have to include the parent record's ID in your criteria. For instance, you count ContactIDs in the contact table where the CustomerID matches the current customer's ID and the ContactID is less than the current ContactID. Just as before, wrap it in an NZ function for safety and use IIF and IsNull to keep things clean and blank for new records.
Setting up the delete event again in the subform ensures your numbering stays accurate even after records are removed.
One thing to keep in mind with this method is that numbering is governed by the record's primary key (like CustomerID or ContactID), so the order follows the order in which records were added. If you want to control the numbering in a different order, such as by date or a custom order you specify, you will need a different approach, like adding your own sort field and building additional numbering routines. Setting up that kind of manual numbering, complete with options to sort and renumber on demand, is more advanced.
If you're interested in going further and learning about creating custom sort orders, making manual reorder buttons, and moving records up or down in your numbered list, I have a 22-minute extended cut for members where I cover those features in detail.
If you want to become a member, you can join on my website and gain access to all of my extended cut TechHelp videos, live sessions, and additional perks. Regardless, all my basic TechHelp videos will always remain free.
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
Automatically numbering records in a continuous form
Using DCount to calculate sequential row numbers
Adjusting form properties to allow record deletion
Setting control source for the row number field
Handling new records using IIF and IsNull functions
Preventing error display for unassigned AutoNumbers
Forcing form refresh after record deletion with Me.Refresh
Applying automatic row numbering in subforms
Counting related records with multiple criteria in DCount
Using NZ to handle null values in DCount calculations
Creating On Delete event procedures for refresh
Limitations of DCount-based row numbering method
Considerations for sorting and controlling row number order
|
