By Richard Rost 4 years ago
Create Sequential Numbering to Replace OrderID AutoNumbers
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?"
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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the database template from class plus get access to the Code Vault. If you signed up on YouTube you have to register on my YouTube Find User page so I can set up your account here on my web site. If you're not a member, Join Today!
If you start off with a table that already has data in it, the code in the video works just fine. However, if you start with an empty table, you'll get an error. The solution:
In the BeforeInsert event, wrap the DMax function inside Nz:
Subscribe to Sequential Numbers
Get notifications when this page is updated