|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Sequential Numbers By Richard Rost 
6 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?" MembersI'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 files from class plus get access to the Code Vault. If you're not a member, Join Today! AddendumIf 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: Links
IntroIn this video, I will show you how to set up sequential numbering for invoices in Microsoft Access, making sure your order numbers increase by one with no gaps, as accountants often require. We will talk about the limitations of using AutoNumber fields for reporting, how to add a custom order number field to your table, enforce unique values, and use the DMax function to generate the next sequential number automatically on your order form. You'll also learn important tips for preventing users from deleting records to maintain your numbering sequence.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's lesson, I'm going to show you how to set up sequential numbering. We'll learn how to set up numbers that go from one to whatever, with no gaps, for invoice numbers.Today's question comes from Timothy. Timothy says, I'm using an auto number for order ID in my table, just like you taught me. But my accountant wants me to have sequential numbers for reporting purposes. What should I do? I get it. Your accountant wants to make sure that your numbers - your order numbers - are all in sequence, just like your check numbers: 1000, 1001, 1002, and so on. That way you can make sure there's no gaps in your reporting. Unfortunately, the problem with Access is you can delete records in a table that you're using auto numbers for, and those numbers are pretty much gone. There is a trick you can play to get them back - I just covered that in my previous video - but for the most part, for all intents and purposes, you don't want to reuse those auto numbers. The trick here is to create your own sequential numbering system. It's not that hard to do, but it does involve a tiny little bit of programming. Let me show you. Here's my real simple customer database. I've got my customer form and my order form. It lists all my orders - this is for everybody. If I open up a customer like this one, I can go here and see just that customer's order. So it basically limits the list. Now, this order ID is an auto number, and it's used internally for the database only. Your customer should never see that. Your accountant doesn't necessarily have to see that. Exposing your auto numbers to your customers or anybody else may cause some problems. Look for my video on auto numbers: good or bad for more information on that. If you want to make sure these are also sequentially numbered, we have to create our own number. We can use a function called DMax to do that. But first we have to create our own field to put that value in. So let's go to our order table design view. Now you can make another value in here called order number. Make that just a number. Long integer is fine. I like to use ID to represent auto numbers and I like to use number to represent number things. If I have a value that might have text in it, I like to use the word code, like customer code, if you want to put characters or stuff in there. That's just my personal naming convention. Now what I want to do is index this down here and make it no duplicates. Indexing will speed up your searches and sorting, and it will make it easier for you to do lookups on this value. But the biggest benefit is that Access will automatically make sure that no duplicate values are entered. So I'm going to go indexed: yes (no duplicates) for the order number field. Save your table. The existing data is OK because those are all null values. I just entered this. But if I try to put the same thing in here twice, like 101, it'll yell at me, so you can't do it because it's got duplicate values. Escape out of that. Unfortunately, you're going to have to type in these values yourself. Yes, there's a way we could fill these in automatically, but it'll take too long to go over here. I will actually put that in the extended cut members edition. The rest of us, let's just type in these values. Fortunately, we've only got a couple here, so it's not going to take that long: 106, 107, 108, 109, and 1010. What I want to do next is - in my form, you can't do this at the table level, you have to do this at the form level - in my form, I want to go to this field, look up what the largest value is, and then add one to it. You could do this in a query, but I strongly recommend a form. Let's close this, save changes. Sure. Let's go to our order form that we have here: design view. Now we have to add that order number in here. I'm going to copy the order ID, copy and paste, because it's already gray - we don't want users changing it. Let's double click on that, and let's change this to order number, the control source and the name. I'm going to lock this field. Go to the data tab and change locked to yes. That means the user can't change this value, especially if you're not the only user of your database. If other people use it, you don't want to let them go in there and modify it, unless you do. If you want them to be able to change that, they can. They won't be able to type in different numbers that are already in use, but they could type in higher numbers. If it comes in that the next number is 205, they could type in 215. But that kind of defeats the purpose of having sequential numbering with no gaps. So it's all how you want to build your database. I'm just going to change this back here to order number. Order num is fine. Let's close this and see what we got. Save changes. Yes, orders. That looks good. Now, when I start typing in the next order: yes, this is a simplified order form. It only has the ID, the customer ID, and the order amount. You'd have other things on here like your order date, shipping address, "is it paid", all that stuff. This is simplified for class purposes. You'd probably want customer ID to be a combo box so you can pick the customer instead of having to type in their ID. Let's say customer one places another order. At that moment, I want this value to be filled in. I'm going to hit escape. Let's go back. Let's go to design view. Let's pull up the properties for the form itself. Double click on that little box right there. Here's the property sheet. Let's go to events. We're going to find the Before Insert event. Before Insert, you'll see right down here. It's a function that runs with the first character that is typed into a new record. Before Insert, hit the dot dot dot button. If you get asked what kind of builder you want, type in code builder. Now right here, I want to put in the value for order number. What is it going to be? Well, order number equals DMax, the largest value. What field? "order number", inside quotes, comma domain, is what table? "order t". That's it. But that's just going to bring back the biggest value. I want to add one to it. So that will go out to the table, find the largest value, add one to it, and set that in the order number field. Save it. Let's come back out here. I'm going to close down this form and reopen it. Now, as soon as I start putting the next order in, boom, there's 111, for $17. My tab order is messed up. That's OK. Next record, customer 5, boom, 112. This will create the next record. It's up to you to prevent them from deleting existing ones to prevent gaps. What I recommend is that you don't ever delete records from your order form. You don't let your users delete records. You can control that in the form properties right up here under data and change "allow deletions" to no. Don't let them delete anything. Now if I save my form and open it back up again, if I try to delete order 5 - nope. I'm hitting delete on the keyboard. Can't do it. Can I change these? Nope. They're locked. The code that we put in will insert the next order automatically. Don't let them delete this stuff. Have a checkbox over here that maybe says void or something or "is it valid"? If you want to be able to indicate which orders were just quotes or they weren't placed or whatever. Never, never, never, never delete data like orders, customers, that kind of stuff. Keep it in the database and mark it inactive. This should satisfy your accountant. You could print that number out for him because he'll be able to see what the order number is for all of your customers. He'll know that you've only done X in sales. He'll know that you've only done 500 orders. You don't want your customers necessarily to see that because then if customer 5 places an order here on June 1st and then customer 5 comes back again three days later and he's now order number 114, then he can see exactly that you've had two orders in how many days. It's like guest checks in a restaurant. If you go on Monday and you get that guest check number and then you go back a couple days later and it's only gone up three, then you know that they've only given out three guest checks in the past three days with that pad. There are different variances. It brings up something called the German tank problem that I talk a lot about in my other video - my auto number good or bad video. I'll put a link to that video in the description box below. So there you go, that's how you can create a custom order number sequence that is sequential, that Access will automatically add one to, and you don't have to give up your order IDs to your customers. You can start this number at whatever you want and it'll just go up by one each time. Now for the members, the extended cut edition is going to include three things. First, I'll show you how to replace null values with order numbers for any existing records. You don't have to keep typing them in. If you've got a few thousand records in your database already, this will save you some time. There is a problem with this method for multi-user databases. If you're not the only one using your database, if you run this function and you go to add a record and someone else is adding a record at the same time, you might get assigned the same number. It involves some programming but it's not that hard to alleviate that problem. Finally, I'll show you how to do a custom order number sequence for each customer so your customers will get custom numbers just for them. So if it's XYZ company, their orders will be XYZ001, XYZ002, and so on. You can keep that in addition to the other order number that you have for your accountant. You can make sure that over the entire company, your orders are sequential and each customer has sequential orders as well. The members only extended cut edition is for Silver members and up. How do you become a member? Just click on that join button on my YouTube channel right next to the subscribe button. You'll see several different levels to pick from. Silver members and up get access to all of my extended cut TechHelp videos. Don't worry, I'm still going to keep making these free TechHelp videos. Make sure you subscribe to my channel. That just means you'll get notifications whenever I release new free videos. Click on the little bell there for all and you'll get emailed every time I put something new on YouTube. Also, make sure to stop by my website. I've got a pretty active Access forum there. Want to see your question answered? Visit my TechHelp page. If you haven't yet, check out my free 3 hour long Access Level 1 course. It's free on my website and on YouTube, and if you like it, Level 2 is just $1. Thanks for watching and I hope you learned something today. We'll see you next time. QuizQ1. What is the primary problem with using an AutoNumber field for order numbers in Microsoft Access, according to the video?A. AutoNumbers can be edited by users B. AutoNumbers may have gaps if records are deleted C. AutoNumbers can only be used by accountants D. AutoNumbers are always sequential without any gaps Q2. What solution is suggested in the video for creating gap-free, sequential order numbers? A. Allowing users to edit the AutoNumber field B. Deleting duplicate records from the table C. Creating a custom order number field and using DMax D. Using a different database software Q3. Why does the instructor recommend not displaying AutoNumber IDs to customers or accountants? A. Because AutoNumbers are too complicated B. Because AutoNumbers should remain confidential C. Because exposing AutoNumbers can cause confusion or issues D. Because AutoNumbers are only needed for employees Q4. What type should the custom order number field be set to in Access? A. Short Text B. Currency C. Date/Time D. Number (Long Integer) Q5. What is the purpose of indexing the custom order number field with 'No Duplicates' selected? A. It allows duplicate order numbers for faster entry B. It prevents duplicate order numbers and speeds up searches C. It enables users to edit order numbers directly D. It automatically fills the field with unique text Q6. At which event in the form should the code to assign the next order number be placed? A. After Update B. On Click C. Before Insert D. On Close Q7. What does the DMax function do in the context of generating the next order number? A. Returns the smallest value in the field B. Returns a random number C. Returns the maximum current order number D. Returns the average of all order numbers Q8. What is a recommended way to prevent users from deleting order records and creating gaps in the sequence? A. Deleting records is not a problem B. Hide the delete button on the keyboard C. Set 'Allow Deletions' property of the form to No D. Change the order number field to text Q9. If an order is canceled or should not be counted but you don't want to delete it, what alternative does the instructor suggest? A. Delete the order anyway B. Mark the record as void or inactive with a checkbox C. Change its order number to zero D. Print out the order and shred it Q10. What issue did the instructor mention might occur when using this auto-sequencing method in a multi-user environment? A. The table becomes read-only B. Two users might receive the same order number if both add records at the same time C. AutoNumber becomes disabled D. The database runs out of space Q11. What additional feature is covered in the members-only extended cut edition? A. How to enable record deletions B. How to generate order numbers using only manual entry C. How to assign custom order numbers per customer (e.g., XYZ001, XYZ002) D. How to remove the order number field Q12. What does the instructor recommend regarding never deleting certain types of data like orders and customers? A. It is good practice to delete old data regularly B. Deleting is necessary for performance C. Data should never be deleted, just marked inactive or void D. Delete data and re-enter it each year for accuracy Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-C; 7-C; 8-C; 9-B; 10-B; 11-C; 12-C 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. SummaryToday's video from Access Learning Zone is focused on setting up sequential numbering in Microsoft Access, specifically for things like invoice or order numbers where you need each entry to be numbered consecutively without any missing numbers.I received a question that many people encounter. Someone mentioned they're currently using an AutoNumber field for their order IDs, as is common in Access, but their accountant needs sequential order numbers, similar to check numbers – 1000, 1001, 1002, and so forth, with no gaps. This makes sense from a reporting perspective. One of the limitations with AutoNumber fields is that if you delete records, those numbers are gone and will not be reused. While there are ways to try to reseed AutoNumbers, it's generally not a good practice and you shouldn't depend on it for creating a reliable, gap-free sequence. The solution is to set up your own custom sequential numbering system. You need to create your own order number field, and assign values to it yourself rather than relying on AutoNumber. This only takes a small amount of programming and is straightforward once you know how. In my demonstration, I use a simple customer-order database. The order form lists all orders. When we look at a specific customer, you can see only their orders. The internal Order ID field is still present – it's an AutoNumber, and only used by the database for internal tracking. I do not recommend ever showing this value to your customers or even your accountant. Exposing AutoNumbers can lead to confusion and potential issues, so reserve them only for internal use. I've discussed this further in a separate video about whether exposing AutoNumbers is a good idea. If you want truly sequential order numbers for accounting, you need to add another field to your order table. Add a field called "Order Number," set the data type to Number (Long Integer is appropriate), and index it with no duplicates. Indexing will speed up lookups and, more importantly, will ensure Access prevents duplicate order numbers. If you try to enter the same order number more than once, Access will show an error. You will need to enter order numbers for any existing records. There are ways to fill these automatically, which I'll discuss in the Extended Cut. For demonstration purposes, I manually entered order numbers for a few records. Next, you have to automate the assignment of the next order number when a new order is entered. This isn't something you can do at the table level – instead, it's best handled in your data entry form. I show how to take the Order ID control on the form and duplicate it for the new Order Number field, setting it to locked so users cannot manually change the value. In the form's properties, under the Before Insert event (which runs as soon as the first character is typed into a new record), you use a simple bit of VBA code. The code looks up the highest existing order number in the table, adds one to it, and assigns that as the new order's number. This approach guarantees that each new order gets the next number in sequence. Once this is in place, whenever a user starts a new order, the form will automatically provide the next available order number, such as 111 or 112, right in the form field. One thing to remember: to truly prevent gaps in numbering, you have to control deletion of orders. If records are deleted, you'll again have missing numbers. In the form's property sheet under the data tab, you can set "Allow Deletions" to No. This prevents users from deleting orders through the form. If you want to track voided or canceled orders, consider adding a checkbox to mark an order as void or inactive, instead of deleting it. Never delete key data such as orders or customers – always mark them inactive so you maintain integrity in your numbering. This system will satisfy most accountants, as you can easily provide a list of all sequential order numbers for their records. Only your accountant needs to see this number – there are situations where you might not want customers to see the order count, as it might reveal more about your business activity than you intend. There are also further topics to consider, which I cover in detail in the Extended Cut for members. In the Extended Cut for members: - I show how to automatically fill in sequential numbers for any existing orders that do not already have them, which is a huge time saver if your database is already populated. - I explain how to handle the potential problem in multi-user databases, where two users might create orders at the same time and accidentally get the same order number. I'll cover techniques to prevent that overlap. - I'll also walk through creating a custom order number sequence for each customer. For example, XYZ customer could have order numbers like XYZ001, XYZ002, etc., while maintaining a company-wide sequential system at the same time. All of these topics are covered in depth in the Silver Member's Extended Cut for this lesson. If you're interested, you can become a member by joining my YouTube channel, where Silver members and up get access to all of my extended lessons. I'll continue producing free TechHelp videos, and you can subscribe to the channel for notifications about new content. Also, check out my website, where there's a very active Access discussion forum and where you can submit your own questions for TechHelp. Don't forget, I also offer a completely free three hour Access Level 1 course on my website and on YouTube, and if you enjoy it, Level 2 is just a dollar. 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 ListSetting up a custom sequential order number fieldDifference between AutoNumber and custom numbering Creating a new number field in the orders table Setting the order number field as indexed with no duplicates Manually entering initial sequential order values Adding the order number field to the order form Locking the order number field to prevent user edits Using the Before Insert event in form properties Programming sequential numbering with DMax in VBA Automatically assigning the next order number in the form Preventing users from deleting order records Disabling deletions via form property settings Explaining the importance of not deleting transactional records How to present the sequential order number for accounting purposes |
||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access counter Create Your Own Custom Sequential Automatically Incrementing Number with DMAX counter consecutive PermaLink Custom Sequential Numbers in Microsoft Access |
