Microsoft Access Create Your Own Custom Sequential Automatically Incrementing Number with DMAX

In this tutorial you will learn how to create your own custom order number that you can start at whatever value you want and have Access automatically increment the values so you have nice sequential order numbers that have no gaps.
Timothy asks: "I’m using an AutoNumber for OrderID in my table, like you taught me, but my accountant wants me to have sequential numbers for reporting purposes. What should I do?"
BONUS FOR CHANNEL MEMBERS: Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video will show how to replace NULL with Order Number values for existing records. We'll talk about problems with multi-user databases and see how to save that new number to the table immediately to avoid conflicts, and we'll learn how to make a Custom Order Number sequence for each Customer, so for example XYZ Company's invoices will be numbered: XYZ-001, XYZ-002, XYZ-003, etc.
Become a member here: / @599cd
MEMBERS ONLY VIDEO: • Microsoft Access Creat...
RESOURCES:
AutoNumbers Good or Bad: • AutoNumbers in Microso...
DMAX: 599cd.com/dmax
Allow Deletions: 599cd.com/allow+deletions
Before Insert Event: 599cd.com/beforeinsert
FREE Customer Template Database: 599cd.com/XCDT
FREE Access Level 1: 599cd.com/Free101
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/TechHelp
Learning Connection 50% OFF: 599cd.com/Connection
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/AccessForum
Twitter: / learningaccess
Email Me: [e-mail]
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
TOPICS COVERED IN MEMBER VIDEO:
- Apply values to existing records
- Multi-user problems: save the record immediately
- Custom ordering by customer
- Assigning Customer Codes
Sequential Numbers
Invoice Numbers
Custom Order Numbers

[@moreganesh6150]

Thank you so much for uploading such tutorials, it has helped me a lot to learn so much about access and it is increasing my curiosity to learn more and more. Hats off to you sir . You are helping numerous people like me . Falling short of words.

[@599CD]

Happy to hear that!

[@roodypooh525]

Simple and direct. Excellent tutorial. Thank you.

[@599CD]

You're welcome!

[@tdp2190]

THANK YOU FOR THIS TUTORIAL.. I not only did the BeforeUpdate I also did the AfterUpdate to fill a number i wanted to follow... THANK YOU

[@599CD]

Welcome

[@zuhairalmutawa9941]

Sample and to the Point. Thank you

[@599CD]

You're welcome!

[@moodkn]

Thanks..! Excellent ... Helpful

[@599CD]

Always welcome

[@mikea8659]

Thank you Rick 👍

[@599CD]

Welcome

[@Akbar.Ali300]

Amazing Sir, You'r Good

[@599CD]

My pleasure

[@fabianvillegas6313]

Gracias salió Perfect!!!

[@599CD]

Thanks

[@anandmallikarjunan7775]

Hello Sir! How are you? I got very useful information from this video. Have a happy life!. Thanks.

[@baseer4371]

Thanks it's also helpful like your all videos ... Thanks to come KZbin..

[@599CD]

Welcome

[@santdnyaneshwar9920]

Sir, best solution you have given, 👍👍👍👍👍👍👍👍but in case while entering data in form the next sequential number has generated as user entered data in first field and user wants to cancel current record for any other reason...by pressing ESC key... mostly it's may need while making a invoice... then the generated next number should be cancelled for that particular record and the same sequential number should be used for next invoice. If we not cancelled it by ESC key then sometimes there are chances that incomplete record may be remain saved unnecessarily and the invoice number also be utilized.... you can see this problem in this video.... while you showing demo, you have entered the only customerID and next number has generated but you kept the order amount field as "0" (empty) by skiping the order amount field. even though the whole record saved... in short we can say the FALSE record saved. so sir please give the solution to prevent from this error. Thanks...

[@599CD]

I'll have to watch it again to see what you mean. Can you give me a time index?

[@santdnyaneshwar9920]

@@599CD Yes of course.... you have entered only customer ID at time index 7:17 and order num 112 was generated but you have not entered order amount and kept it by default as "0" as empty. like this its same happened for order no. 113 and 114 also and empty false data saved in those records. so it should be saved by clicking "save command button" only even though all the fields are filled. to prevent this there is option of data compulsory in all fields, but there may get the problem while creating other queries, forms. I hope you understand the importance of what i am trying to say. Thanks Sir you have looked in this issue.🙏

[@599CD]

OK, I understand what you're trying to say. The fact that the Order Total is $0 is not the point of the video. The point is to show you how to create the sequential numbers. If you want to force the user to enter a value for that field, set a validation rule. Like I mention in the video, this is a SIMPLIFIED database for the purposes of training. Of course you'll want to make sure there is an order total (or not, a $0 invoice could be an exchange, and you still want it tracked with an Order Number). I show you how to prevent deletions so you don't have any gaps in your numbers. You'll want to add an "IsValid" field or something like that to show canceled items. But, the numbering works as shown. The ONLY problem I could see having with this the way I have it built is if you have multiple users adding records at the exact same time, and I talk about that in the Extended Cut. I certainly appreciate your feedback, and I hope I've addressed your concerns. Remember, again, this is a TEACHING database, not a real working order-entry system. You can learn more about validation rules here: kzbin.info/www/bejne/r2KndI2VrrqokMU

[@bawtag]

Thank you Richard, how about in multiuser environment where multiple users are entering the data concurrently? If the record is not saved yet but the next sequential number is already assigned, the other users might have the same case in their form.

[@599CD]

That could definitely be a problem. I would recommend performing a Me.Refresh immediately upon assigning the custom ID. That way the record is written to the table as quickly as possible.

[@mikemellor8972]

Best practice in a multiuser environment is a document master table that allocates the next number to whichever user next enters a data capture form. The problem is when a user backs out without committing the transaction/s to file. That does leave gaps in the document number sequence. There are various ways of getting around this such as each user has their own sequence e.g. JD175503 for Jane Doe, but normally those gaps are allowed to stand. There is only a gap in the transaction table, not a gap in the document master table.

[@mysteriomagik]

Nice! This is somewhat similar to what I need.. Is it possible to have this sequential number restart from 001 every month, with the month value inserted into the sequential number as a prefix? E.g. In January it will be-- A/01/001, A/01/002, etc.. In May-- A/05/001, A/05/002, A/05/003, etc

[@599CD]

Sure. In the Member's Only Extended Cut version of this video, I show how to make sequential numbers or each customer. All you would have to do is add the Month(Date()) to that number. kzbin.info/www/bejne/roqWl3qdmdaEq7c

[@ssdusd]

Nice, but how can we make Sequential Number for M to M relationships Not for ID but based on the combination of FKs

[@doborkulabb2643]

Great video. I have an issue my customer wants to delete imputa like names of those who are inactive. If I put an inactive box and list all the names in a table or report, can I still have a regular nummber ordering in a sequence? Thx

[@599CD]

Sure. Just have your numbering ignore inactive customers.

[@rudraverma5970]

Dear Sir i used this method for my software but now when two front end user open new order same order number comes in both screen .how to solve it.Please suggest..

[@Only-a-Guess]

Thank for this it has really helped me with an equipment inspection database, one question though is if I start with zero records it does not work but if I already have say a single dummy record in with the number 1 it then works fine. Is there a way around this anomaly?

[@599CD]

Excellent question. Here's my answer: 599cd.com/a?34009

[@Only-a-Guess]

@@599CD That worked a treat many thanks, do you do a video on filtering a combo box based on another combo box selection, what I have are 3 tables 1 for customer (location) another with customer contact persons (could be many for the same site) and these are then entered into the main table for the records of equipment inspected. What I want to do is select a customer location which then limits the customer contact persons to only people who are on that particular site then all these get entered into the main records table.

[@599CD]

Yep. Those are called Cascading Combo Boxes. I have an older video on them here: 599cd.com/tips/access/two-combo-boxes - but it's funny you mention that because I'm also going to be covering them in today's TechHelp video on auto maintenance - pick a Make and then only show Models made by that auto manufacturer. Stay tuned.

[@Only-a-Guess]

Hi, this has worked perfectly and I am now wanting to improve my DB and the question is I have a field in one table (via a form) using this DMax based on another table of records which gives me the next number to use and store but can it also then store this new number back to the table it used in the DMax to obtain the number in the first place. Apologies for my explanation I'm just a QA manager not a DB expert in a small company trying to improve our non conformance reporting systems. Thanks

[@slimanimohamedseghir378]

Hi, Richard, how do i create an ID number from information that exist in other field in sequential way? like IDnumeber = year-personalID-zipcode-numberorder

[@599CD]

If it's in the same table, just concatenate it all together. X=Year(Date())&CustomerID&ZipCode&OrderID

[@lamrith]

Could this same code could be placed in "Before update" event so that it only grabs a number when you go to save/update the form? I would think that could help with multi-user environments? We have a database where shop users will enter incoming work, and they can often get pulled away or not finish and entry for a number of reason. We would like to have it so the entry form does not grab a new number until they finish and save and I have been searching for a good way to do that with Access.

[@599CD]

Yep. I cover that in the Extended Cut: "I'll show you how to replace NULL with Order Number values for existing records. We'll talk about problems with multi-user databases and see how to save that new number to the table immediately to avoid conflicts, and we'll learn how to make a Custom Order Number sequence for each Customer, so for example XYZ Company's invoices will be numbered: XYZ-001, XYZ-002, XYZ-003, etc. And of course we'll assign Customer Codes that are unique and alphanumeric."

[@TekhXpert]

Hi Rick! Thanks for the guidance to generate custom sequential numbers in MS Access. DMAX will return the maximum number from the table, if number exist, but if not exist then at time of first record insert? You did not guide about error handling using exceptions in your VBA Code. I think the use of Nz with DMAX is necessary here or the conditional check by use of If Dmax returns null then sequential number = 1, otherwise returned number + 1

[@599CD]

Yep. You are 100% right, and I have a whole separate video on NZ - 599cd.com/NZ. I can't cover everything in every video. These are quick SHORT tip videos. That's why I just seeded the numbers at 101 and moved ahead. I cover error handling and all of that fun stuff in my full courses - of which these are just a FREE PREVIEW.

[@599CD]

In fact, I'm pretty sure I cover that in the Extended Cut.

[@renzomorishita1330]

In older versions of Access there was a feature that I could automatically number lines. For example: I started numbering the first cell with the number "1" and the following cells with "2" and "3", and Access realized that it was a pattern, and when I pressed the arrow to the next cell Access would number automatically with the number "4" and when I pressed the arrow for subsequent cells, Access automatically completed the following cells as I pressed the down arrow, and filled sequentially as far as I stopped. In the new versions of Access, 365, does not have this feature. How do I include this feature or command?

[@599CD]

I don't ever remember this being a feature in Access. Are you sure you're not thinking of Excel?

[@renzomorishita1330]

@@599CD , yes I am sure Access has this feature. No, I'm not thinking about Excel. I have a version of Access older than Access 365 and I did this this week.

[@mechtechpotato4249]

Hi Richard, I'm wondering how to make a custom autonumber that relies on the prefix for the increment increase, like A1 B1 C1 A2 B2 C2 and so on. I couldn't find any videos on this, Is that part of the premium video?

[@599CD]

I cover something similar in the Extended Cut: we'll learn how to make a Custom Order Number sequence for each Customer, so for example XYZ Company's invoices will be numbered: XYZ-001, XYZ-002, XYZ-003, etc. And of course we'll assign Customer Codes that are unique and alphanumeric. Not exactly what you're looking for, but close. All depends on how you derive that A, B, C, sequence.

[@mechtechpotato4249]

@@599CD The sequence would be based off of if the user was inputting A first then the autonumber would assign 1 to that, A1. Then A agin, the auto number would assign 2, A2. Another user inputs B and the auto number then goes B1.

[@599CD]

Then yes, the Extended Cut would show how to do something very similar.

[@abdulrahmanalqahash8737]

Hello there what about if there is a letter before the numbers Like F3S00101. Who to work this out to get F3S00102 automatically. Thanks

[@599CD]

One of my members just asked a very similar question a few days ago. Video coming soon. Stay tuned.

[@12onin_Gypsy]

is there a reason this doesnt work when i use order id that is a primary key? its in a form with a subform attached

[@599CD]

As long as it's not an AutoNumber, it should work fine.

[@tonychaar6780]

Hi Rick, how we can solve the first record when the table has no records

[@599CD]

Wrap the DMAX in an NZ function: 599cd.com/NZ

[@LHPottery]

What version of Access are you using? There is no Before Insert in 2016.

[@599CD]

I'm pretty sure it's been around since at LEAST 2003... tinyurl.com/23damteb

[@LHPottery]

@@599CD Found it. At the form level, not control level. Gotta watch out what I'm dealing with...

[@afrinsultana7821]

how can i control autonumber manually in Data Sheet View subform

[@599CD]

You can't. Autonumbers are for internal use. They cannot be controlled. You have to create your own counter, which is what I show in the video.

[@manhalalqaisy1448]

I want to delete one of the records and reorder the sequence, how can i do this ?

[@599CD]

599cd.com/Ask

[@diegomarino25]

is there a way to replicate an autonumber in an insert query? i mean, adding more than one record with an incremental number?

[@599CD]

Nope.

[@599CD]

599cd.com/QQ

[@nehasrinivas7122]

Hi, I have 2 questions 1. I cant see the lock field in property sheet. 2. How to prevent auto sorting Eg. If I give ID 1,2,3 and if I close and reopen the table, it gives 2,1,3 Please help

[@599CD]

599cd.com/ask

[@govindbehera9897]

How to count total entries in a table like we do it in query using totals..but in dis i can only count for a single field how many entries..groupby,sum,average n all..but how to count total entries of a table...hope u gt ma doubt...can u plz help me

[@599CD]

Sounds like you need DCOUNT: 599cd.com/dcount

[@govindbehera9897]

@@599CD can we make it through query design...

[@599CD]

You could try an aggregate query: 599cd.com/aggregate

[@SanjayKumar-yh6il]

How to Add Serial Number e.g. 1,2,3... for each Customer ID

[@599CD]

599cd.com/Counter

[@johnnyringo79]

I just cant get this working, I've got the code right but nothing updates on the form when I add data

[@599CD]

Can't tell from here

[@hwuser-gk7dt]

Hi John. I am facing the same issue. Did you get it to work?

[@Pakclub1]

I need same app for android, it's possible?

[@599CD]

599cd.com/AccessOnline

[@mpazisimutanda3906]

Sir, nice tutorial. i want help on how to increase tuitions fees every after three months for different pupil grades. for example, grade 1-5 pay $50.00 for the first term, and second term should $100.00, and third term &150.00. grade 6 - 7 pays $100.00 per term(Quarter), so second term should be $200.00 and so on. now i want after a period of three months at a given date the system automatically adds the fees for different grade e.g grade 1-5, 6-7. The system contains grades from 1 to 9 with different tuition fees. Grade 8-9 pays $200.00 per term. if there could be a way also to enter manually these fees on form and update the column with different tuition fees that also help. in short, upgrading a column with different tuition fees,

[@599CD]

Should be able to do this with one or more Update Queries: kzbin.info/www/bejne/pXerdXSFoLiEZrs

[@amirhosseinrezaei4723]

number = DMax("number", "Table1",)+1

[@599CD]

Don't forget to NZ it.

[@paulatkinson9010]

Thanks for the awesome tutorials Richard! I cant seem to get this to work for a new table with no existing records. It just leaves it blank. If I add a record and fill in the first number myself every number following will work. How can I get this to count right from the beginning?

[@599CD]

See the addendum on my site: 599cd.com/Counter

[@paulatkinson9010]

@@599CD Thats Awesome!! Thank You! :)