Sequential Annual Coding
By Richard Rost
4 years ago
Sequential Annual Coding for Customers in Access
In this video, I'm going to show you how to assign a code to each customer that's based on a sequential number according to the year they were added to your database. So for example, you'll have 2022-0001, 2022-0002, 2022-0003, and so on. When 2023 rolls around, the counting starts over: 2023-0001, etc.
Sandra from Norfork, Arkansas (a Gold Member) asks: I'm a pug breeder. For each litter, the pup is given a number, for example 22-001 is the first pup of the year 2022. I can't figure out how to make the sequential numbering work for this format of year they are born (WhelpDate) and the sequence they are born. Can someone help?
Update
- A few people have mentioned to me that if you go back and delete one of the previous records from that year, then the count is wrong. Yes. That will happen. That's one of the reasons why I always say Don't Delete Data! But if you're going to not listen to me, then use DMax instead, and you'll have to wrap that in an Nz function if none exist. That's again, why I say Don't Delete Data!
Pre-Requisites
Recommended Courses
Links
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, incremental number, counter value, dcount, incrementer, sequential, counter based on year, annual counter, date based counter
Intro In this video, we will talk about how to assign sequential annual codes to records in Microsoft Access, such as generating unique customer or item codes based on the year they joined or were created. I will show you how to use DCount and a bit of VBA to calculate these codes in a format like 2022-001, discuss common pitfalls with autonumber fields, and demonstrate how to trigger the code assignment using a button on your form. This tutorial is especially helpful if you need custom sequential numbering that resets each year.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor Richard Rost.
In today's video, we're going to talk about sequential annual coding. What does that mean? Well, we're basically going to assign a code to each customer based on the year that they joined, that they became a customer. That's pretty straightforward, when you think about it.
Today's question comes from Sandra in Norfolk, Arkansas. A Gold Member. Wait a minute, Sandra, didn't you just get a video like two or three days ago? That's okay, I like this question, so I decided to make a video on it last minute. Sandra posted this in my forums on my website under the Sequential Numbers video, which is another video that I did that's very similar to this. I'm going to point you to that one in a minute.
Of course, I love my guys, Adam and Scott, and everybody else jumped in. They gave her a working solution, but I, of course, have to give my two cents. So that's what this video is.
Go watch the Sequential Numbering video if you haven't already, where I teach you how to make a counter. This was from another customer whose accountant wants sequential order IDs in his database. When we use an autonumber, we can't always guarantee that autonumber is going to be sequential. Sure, it starts at one and counts up, but you can't rely on it. If you delete some stuff in the middle, you lose those numbers. Again, autonumbers are not for you. That's a whole different video. But in this video, I do teach you how to make sequential numbers so that if, for example, you end up deleting record five, you can get record five back.
Here's another similar video that just gives you a counter for each one. It doesn't assign it to that record, but when you display them, it says this is record one, two, three, four. If you display them a different way, they'll say, here they are: record one, two, three, four. So that's a slightly different video. Go watch this if you want to.
Also, go watch my DCount video, DCount is a function we're going to use to count the number of other people that are in the database that are from the same year, so that we get the count of how many records are already there so we can add to that.
But no matter what you do, for today's video, you're going to have to know a little tiny bit of VBA now. I'm going to show you everything you need, but if you've never done any VBA programming before, go watch this. It's about 20 minutes long and teaches you all the basics—everything you need to know to get started.
All right, here we go.
So, back to Sandra's question. Sandra said, I'm a pug breeder. That's awesome. I have a couple of dogs myself. I have a couple of black labs. For each litter, the pup is given a number. For example, 22-001 is the first pup of the year 2022. I can't figure out how to make the sequential numbering work for this format of the year they are born. And the sequence they are born. Can someone help?
The first thing, Sandra, is I'm going to try to talk you out of using that format because you're going to run into a Year 2000 problem when 20-99 rolls around if you're still doing this in 77 years. So I'm going to change your format to 2022-001. If you don't like it, I'll show you how to do it the other way too.
I'm going to switch it from dogs to customers because I already have a customer form and a customer table. I don't feel like building a dog table. But it's not a matter if you're doing dogs or customers or purses or jet engines. It doesn't matter. It's all the same stuff, just different field names.
Let's see how I would do this. The first thing I need in my customer table (and your dog table, whatever it is) is a code to put in here. We're going to call this the customer code, and that'll be a text field. Make sure there's no default value. Save changes.
Now, on my customer form, since this is based on Customer Since, I'm going to move Customer Since over here. I'm just going to copy this guy, copy and paste. We'll change this guy to be based on customer code. Don't forget to change the name. I'm going to rename this Customer Code.
I'd suggest you lock this, and I'm going to gray it out so the user can't just type in what they want. If you want to lock it too, you can come over here, go to Data, set "Locked" to yes so the user can't just type whatever they want in there—unless that's something that you want to do. If you've got existing dogs with existing dog codes or customer codes, you can type those in if you want. Leave it unlocked. I don't care. It's your database.
Now, you need some way to trigger this to calculate. There are a lot of different ways you can do it. For now, let's just use a button. I'll just grab one of these buttons, copy and paste, slide it over here. We'll make this the calculate button. When I click on this, it'll calculate it based on Customer Since. We'll talk about some other ways to trigger this in a few minutes.
This will be the Calculate Customer Code button or whatever you want to call it. Right-click, Build Event. Here's our code builder. Let me slide this down like this. We don't need you for now. Let me size this form a little bit.
What do I want to do in here? I want to calculate the number of people who have a Customer Since from this year already—or whatever the Customer Since of this customer is, if you want to go back and do the old ones too.
We have to look at the Customer Since of all the people in the table who match the same Customer Since as this person. First thing we're going to do, first of all, is: if IsNull(CustomerID), then Exit Sub. If this isn't a person, then exit sub. If they don't already have a Customer Since (if you haven't typed it in already), you can either exit sub again, or you can assign it—let's say you're putting a new pup in the database, a new person in the database. You could say: if IsNull(CustomerSince) then CustomerSince = today's date, for example. If you haven't already put the Customer Since in, assume it's a new pup, new person, whatever.
Those are the two things we're going to do for the top. Another thing you might want to do is not allow them, or at least warn them, if they try to calculate this thing again and they already have a code. So, you could do: if not IsNull(CustomerCode) then what do you want to do? Let's ask them, are you sure you want to override it? It's going to override it, are you sure? If MsgBox("This will overwrite existing customer code. Are you sure?", vbYesNoCancel), and if that is anything other than vbYes, then exit sub.
I'll add some links to other videos, if you don't know what IsNull and If Then statements or the MsgBox function are, I'll put those in the link section down below.
Now we get to the main part. Now we have to count how many other records are from the same year as this one, who have a person number already—don't count the null ones that have not been assigned—and are also not this person, because if they've already got a record in the database already and they've already got one, you don't want to count them in the count.
We need to store that variable somewhere. Let's make a variable up here: Dim L as Long. I'll add a link down below if you don't know what variables are.
Down here, we're going to say L = DCount. What am I counting? Count all of the records from the customer table.
Here's our big long WHERE condition:
The first thing is, the Year of Customer Since in your case, Sandra, would be WellPDate. The Year of Customer Since is the same as the Year of the Customer Since of the current record.
So, Year(CustomerSince) of the record in the table is the same as Year(CustomerSince) of this record. For example, if the current record is from 2022, I want to count up all of the other records in the table that are from 2022.
That's not all. I want to make sure they have a customer code already because there might be other records in there that haven't been assigned. So, if you put in 15 records previously and this is the first one you're actually giving a code to, this will be record one. So, you want to make sure you do them in order.
So, And Not IsNull(CustomerCode). In other words, only add up the ones that have a customer code.
One more thing. We don't want to count this record because if you've already saved this record in the table and this is the first one, this one's going to be not included. So, this is going to have zero, and then we're going to add one to it at the end.
So, And CustomerID of the record in the table is not the CustomerID of the current record. Add all that stuff up.
Then you could do it in two steps if you want to or one step, right? L = L + 1, you're going to add one to that. You could put the +1 over here. When I'm teaching this stuff, I'm more about making the code readable than I am about making it compact. You can put multiple stuff together in one statement, but I like to make sure that when you're rereading this code, you understand what I'm doing instead of just tacking a +1 over here.
This is because if this is the first record, if this is the first dog you're adding, the first customer you're adding, it's going to say, count up all the other customers from the same year who have a customer code and are not me. That might return a zero. Now, we have to add one to it. This is going to be customer one or pug one or whatever you want to call it.
Now we can format that person code, which is a text string. Again, I strongly recommend using a four-digit year. So, I'm going to say CustomerCode equals the year of their Customer Since date—CustomerSince—so if you're doing 1994, it's going to be 1994 and then a dash and then we want to take that L value, one, two, three, whatever. I want to format it as a three-digit number. I'll do a four-digit number just to be different. Format(L, "0000"). That says take that L. Sometimes I have a hard time typing and talking at the same time.
So, take that customer code, give me a four-digit year value, put a hyphen there, and then a four-digit L value for that, whatever it is.
Save it. Yes, Sandra, if you're going to be stubborn, you can also say, give me the right of that, two, and you'll get just 21, 22, 23, and so on. But I am sticking to my guns.
Now, let's see if it works. Close this, save changes. Yes. Open up a customer form. I'm going to sort these by Customer Since. 29 records in here. I'm going to right-click on this field and sort them oldest to newest.
So, the first one's in year 87. We click the calculate button: boom—1987-001. That's good. Go to the next one. There's an 89. Good, 89-001. 90. 90. 90-001. Where's the next one? 94, 95. Let's skip. Okay, 95. We have a couple of records. Let's do 94. 95. Here we go: 95-001. Next record: 95-002. See? Next record: 95-003. Looks like it's working. 96, 78, there's a bunch of eights. I think 2000, okay, 2003, there's a bunch.
But let's say I don't do them in order. Here's the first 2003 record. Let me skip ahead a couple. Let's say Geddy Lee is the first 2003. Boom, he gets assigned 001, because none of the other 2003s have been assigned a number yet. Let me go back a couple, here's the first 2003. It calculates. Boom, that's 2003-002 now.
Obviously, I'm doing this in the order that I calculate them. If you want to make sure it's definitely in sequential number order, that's up to you. You definitely want to make sure that you assign them in the order of their birth date. That's completely up to you. Make sure when you're going through, you sort all the existing records, sort them, and then click the button in the order that you're going down.
If you have thousands of records already and you want to assign these numbers without having to click the button each time, that will be a separate video. We could do that by taking this function—well, by taking this subroutine, making it a module function that'll return a value. Then we can use that value that's returned in a query. We can do an update query. That, however, will be a separate video if you really want to see it. If that's the case, let me know. Post in the comments down below, and if I'm feeling generous that day, maybe I'll make a video on it. Or you can consider that homework if you want.
There are a lot of other things you can do in here. I had a million ideas while I was doing this. You can have it so that this is triggered when Customer Since is typed in—the first time you type in the Customer Since, it automatically triggers that. That's just a matter of running that same code in the After Update event for this guy. I'll include a link to my After Update video if you want to use that. You could put it in the After Insert event for the form. You could make this required if you want to make sure that the person, when they're typing in a new dog, definitely figures that out. Calculate on whatever. There are a million things you can do.
The goal for this video was for me to just show you this and how I would calculate that number. I personally would do it with a button. That's just me. Make it so that if the user tries to leave this form or close the record, if this is required, it will say, I have to calculate that number first. That's just how I would do it, but totally up to you. It's your database. I just show you how to put the Legos together.
I hope this helps you out. Again, special thanks to all my moderators who helped Sandra out in the forums. I just saw the question, and I know we get asked this a million times: How do I do a sequential number for this or for that? I've done three videos on it now so far, and they're all slightly different. I hope this helps everybody out, and I hope you learned something. I'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver Members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold Members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum Members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page. You'll be shown in each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But don't worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the main topic of this video?
A. Assigning sequential annual codes to records based on the year they joined
B. Importing customer data from Excel
C. Encrypting Access databases for security
D. Creating visual reports using charts
Q2. Why are autonumbers not recommended for creating sequential annual codes?
A. They are always sequential and cannot be changed
B. They may skip numbers if records are deleted, leading to non-sequential numbering
C. Autonumbers use text instead of numbers
D. Autonumbers cannot be sorted
Q3. What is Sandra trying to accomplish with her pugs?
A. Generate random codes for each pug
B. Assign each pup a sequential code based on its birth year and order
C. Track the colors of her pugs
D. Automatically send emails to new buyers
Q4. Why does Richard recommend using a four-digit year in the code?
A. It looks more professional
B. It prevents confusion and avoids the Year 2000 problem
C. It shortens the code
D. It is required by Access
Q5. Which Microsoft Access function does the video recommend to count the number of existing records for a given year?
A. DSum
B. DMin
C. DCount
D. DLookup
Q6. What is the purpose of locking the Customer Code text field on the form?
A. To make the form look cleaner
B. To prevent users from manually entering incorrect codes
C. To make data entry faster
D. To hide the field from all users
Q7. Which event is used to trigger the calculation of the customer code in the video?
A. On Open event of the form
B. On Close event of the form
C. A button click event
D. Form Load event
Q8. When calculating the code, which records should be included in the count?
A. All records in the table
B. Records from a different year only
C. Records from the same year with a non-null customer code, excluding the current record
D. Only records without a customer code
Q9. Why is "L = L + 1" used after the DCount?
A. To skip every other record
B. To account for the current record as the next sequential number
C. It fixes a bug in DCount
D. To subtract a record that was deleted
Q10. How is the final customer code formatted in the video example?
A. "YYYY/NNN" where NNN is the sequential number
B. "ID-YEAR-NUMBER"
C. "YYYY-NNNN" with a four-digit year and a four-digit sequential number
D. "YEAR-NUMBER" without leading zeroes
Q11. If you want the code to be generated automatically after entering the Customer Since date, which event should the calculation code be added to?
A. After Update event of Customer Since
B. On Load event of the form
C. Timer event
D. After Insert event of the table
Q12. If you have many existing records and want to assign codes to all at once, what approach does Richard suggest?
A. Manually click the button on every record
B. Use a module function and an update query
C. Export the table and re-import it
D. Delete all records and re-enter them one by one
Q13. What should you do if a customer already has a code and you try to recalculate it?
A. Automatically overwrite without warning
B. Show a message box warning the user about overwriting
C. Skip the calculation without user input
D. Lock the entire record
Q14. How can you change the code format to "YY-NNN" instead of "YYYY-NNNN"?
A. Change the number of digits in the sequential number
B. Use the Right function on the year portion
C. Use the Left function on the sequential number
D. There is no way to do this
Q15. What is a key benefit of following the video's approach for sequential annual codes?
A. It guarantees the code is always unique for each year and sequence
B. It creates random codes for each record
C. It ensures codes are only visible to admins
D. It removes old records from the table
Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-B; 10-C; 11-A; 12-B; 13-B; 14-B; 15-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 video from Access Learning Zone covers the topic of sequential annual coding. In this session, I explain how to assign each customer a unique code that reflects the year they joined, combined with a sequential number for that year. This approach is helpful when you want to track customers, products, pets, or any other type of record, based on when they became part of your database.
The inspiration for this tutorial comes from a question Sandra posted in the forums. She's a pug breeder who wants to assign codes like 22-001, where "22" is the year and "001" is the sequence number for each pup born that year. However, I recommend using a four-digit year, such as 2022-001, to avoid confusion and future issues with the date format.
While Sandra's scenario deals with dogs, for demonstration purposes, I'll use my pre-existing customer form and table. The method applies regardless of whether you're tracking dogs, customers, or anything else.
First, you'll want to add a "customer code" field to your table, which can be a text field with no default value. Next, bring this new field onto your customer form, placing it near the "Customer Since" date field, since the code will be determined by the year indicated there. It's generally a good idea to lock this new text field so users can't enter values manually, unless you need to make exceptions for existing records.
To generate the code, you'll need a way to trigger the calculation. For convenience, I suggest adding a button on the form labeled something like "Calculate Customer Code." When clicked, this will create the sequential code using the "Customer Since" date.
Behind this button, we use VBA to do the heavy lifting. The basic process involves counting the number of existing records in the table for the same year with valid codes, making sure not to include the current record. This is done using the DCount function, which tallies only the records meeting these specific criteria. If you're new to VBA, I suggest watching my short introductory video on Access VBA basics, which covers everything you need to get started.
There are a few things I check for in the code. If the current record does not yet exist or if the "Customer Since" date is missing, I handle those cases to ensure the calculation only proceeds when there is enough information. Also, if a code is already in place, I prompt the user before overwriting it.
Once I have the necessary count, I increment it by one (since, for example, if no other records are from 2022, this would be the first for that year). The final code is formatted as four digits for the year, followed by a dash, and then a four-digit sequence number. This ensures clear, future-proof codes, such as "2022-0001."
After applying this process, you can test by sorting your records by date and using the button on each one. The sequence numbers will increment properly for each year as you assign new codes. If you are not assigning codes in order, the count still only factors in records that already have codes assigned, so numbering remains accurate.
If you have a large number of historical records and want to apply codes all at once, rather than clicking the button for each record, that can be done with a batch process. This would involve converting the procedure into a function you could use in an update query. I can make a separate video covering that if there is interest.
You might decide to trigger the code calculation automatically when a "Customer Since" date is entered, or in another event, such as when the record is saved. You have plenty of flexibility in how and when codes are assigned, and I recommend picking the method that best suits your workflow.
Throughout the video, I give links to other related lessons, including those on sequential numbering, using DCount, understanding variables, and handling form events like After Update. All of these resources can help clarify the various pieces involved in this solution.
I hope this explanation helps you handle sequential annual coding in your Access databases, whether you are managing clients, puppies, or any other type of record. If you would like to see a detailed, step-by-step walkthrough of everything discussed here, you can find the complete tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Sequential annual coding explained
Designing a code based on join year and sequence
Modifying code format for four-digit years
Adding a code field to your customer table
Adding a code textbox to your customer form
Locking and formatting the code field on the form
Creating a calculate button to trigger coding
Building the VBA event to assign sequential codes
Using DCount to count records from the same year
Skipping records with existing codes and current record
Formatting the code as Year-Sequence with leading zeros
Applying the code to new and existing records correctly
Demonstrating sequential numbering across different years
Ensuring correct order when assigning codes
Discussing alternate code triggers (e.g., After Update event)
Explaining error handling and overwrite warnings in VBA |
