Numbered Form Items
By Richard Rost
8 months ago
Auto Number List Items in a Microsoft Access Form
In this Microsoft Access tutorial, I will show you how to number items in a continuous form without the convenience of a running sum property. You'll learn how to create a calculated field using the DCount function to display a sort order based on the CustomerID. We'll cover the use of functions like NZ to handle null values and explain the drawbacks of using DCount in larger datasets. This tutorial is designed for an expert level audience, bridging the gap between beginners and developers, and shows practical techniques for simple counting in smaller tables.
Members
In the extended cut, we will use a little VBA code to renumber the list based on whatever field the user sorts on. I will show you how to take into account any filters that are applied, ensuring the numbering is accurate and dynamic according to the sort order and filter conditions.
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, number items continuous form, running sum property, calculated field, sort order, DCount function, display sort numbering, NZ function, null conversion, expert-level tutorial, control source properties, dynamic field calculation, CustomerID sorting, avoid VBA, efficiency considerations, continuous form limits, display count dynamically, IIf function, conditional counting
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 number items in a continuous form in your Microsoft Access database. Today's video is a follow-up to yesterday's video where I showed you how to do this in a report, and in a report, it's super simple. You just make a text box. It's got a value of one, you turn the running sum property on, and that's it. Well, with forms, it's not that simple. Forms don't have a running sum property, so you have to get a little more creative.
We need a couple of functions today, and that's why today is an expert-level video. Expert is what I call the level between beginner and developer. Beginners are just figuring things out—they're learning how to build forms and stuff like that. Developers, VBA programming, all that. We don't have to go that far. But we do need to know a couple of things for today—a couple of functions specifically. First, go watch yesterday's video if you haven't already just to see it and kind of know what we're doing. You'll need to know how to use calculated fields today. We're going to make a calculated field to display that sort order. You'll need to know how to use DCount. DCount is the grand-nephew of the lookup. Go watch this video. And go watch this video on the NZ function. NZ converts null to zero, and this will only come up if you have zero records in your form, but it will come up. Go watch this—these are all free videos. They're on my YouTube channel. They're on my website. Go watch them and come on back.
I'm just going to pick up using the database from yesterday. In here, I've got a customer list form. Now what we're going to do is we're going to add a calculated field to the left of this guy. So we're going to slide everybody else over just a little bit. We're going to draw a box right through here. And we're going to go just like that. I'll just copy CustomerID, copy, paste. And we're going to slide you over there like so. And we're going to get it right in there. Now that blue is what I use to indicate to the user that you can double-click on it and do something, but you can't do anything to this one. So we're just going to format it and make it like a light gray. Gray tells people that you can't change that.
Let's bring this bottom up like so. Double-click on this guy—that'll bring up its properties. Let's go over to the All tab, and let's give it a good name. Let's call it sort order or whatever you want to call it. I don't care. Control Source is where you get your data from. It could be a field in the table, or it could be a calculation like a function. I'm going to hit delete, and I'm going to zoom in so you can see this better. Shift F2. I'm in the control source property. Here's what's going to look like. We're going to use the DCount function. What I want to do is I want to count all of the items that have a CustomerID less than or equal to the current one. Because the first record, if you count the number of items that have a CustomerID less than or equal to the first one, it's going to be one because it's equal to the current one. The second record is going to have two items that are less than or equal to the current item. So we're going to use DCount for that.
This will be equal to DCount. Now, you could put a field in here for the first parameter or just put a star in there. That counts everything. From what table? The CustomerT. Where the CustomerID is less than or equal to the current CustomerID. So I'm going to close the quotes and put & CustomerID in here. Then we can close the parentheses. What that does is it puts the current CustomerID from the current record in there, right? 1, 2, 4, 6, 35, whatever it happens to be. It goes inside of here, and then it evaluates that. And it will count the total number of records from that table that have a CustomerID less than or equal to the current one. For the case where you have no records in the table, we have to intercept that with an NZ function. If that returns a null, we don't want an error. So we're going to go NZ that comma zero. If this returns a null, you're going to get a zero. All right. Hit okay. Put it in here. Save it. Close it. And you can put a number or something up here in the label. Watch this. I usually just do this. I just slide that over like that. I'll put a number in here and then just space that guy over. I don't mess around with the labels too much unless I need those labels for something, like sometimes I make it so you can click on one of these labels and it'll sort by that column or something. Usually, they're just for display. So I don't care if I have multiple labels like that. All right. Save it. Close it. Open it, and there you go.
Take a look at this guy. How many records are in the table that have an ID less than or equal to that one? There are three. This one has four. This one has five. This one has six. Notice I deleted a bunch of records in the middle. Now, if you delete some more, if you come over here and you can delete some more, you will have to refresh that calculation. Hit F5, or it will recalculate next time you open up the form. And yes, you can throw events in there, and you can have it recalculate automatically, but all of that involves some VBA programming, which we're trying to avoid right now. This gives you a nice simple count of the number of records based on the CustomerID. And yes, I just noticed that error down here. Now, this error down here is because we're sending a null value into the DCount function. So what we could do is we could tweak our function even more. We can do this right here. It's sending CustomerID—this is actually a null here. And it's causing a problem with this function. So you can actually put an NZ around this guy.
And if CustomerID is null, send a zero in. Then you're going to get a count of how many records have a CustomerID less than or equal to zero, which should return a zero. Let's see. Save it. Close it. Close it. Open it. And there we go. We got a zero down there. See, that's perfect. Or if you really want to get cool, you could return the next number here by just returning this whole count of all records. That will get a little trickier. And I would use an IIf function for this. If you're not familiar with the IIf function, go check this video. It's basically an if-then statement inside of a function.
So here, what I'd do is I'd break this up, and I'd say if IsNull CustomerID, which means we're on the last record, then I'd put in here, let's call it for now X, which will be the total count of records. Otherwise, I'd do the rest of this stuff. And if that's the case, we're getting rid of the possibility of this being a null in here. So we can get rid of this NZ there. We just say & CustomerID and get rid of that comma zero and that parenthesis right there. All right. Now, here, what we'd put here is the count of all the records, and then just add one to it. So DCount star from CustomerT—count all the records and add one. Try to get that so it fits right. It's not going to fit, is it? I'll put a space there. There we go. Hit okay. Save it. Close it. Close it. Open it. And there we go.
And then next, new one. So we're basically saying, okay, if you're on the new record, if it's blank, then count all of them and add one. Otherwise, count the record you're on and go below that. And that's the benefit of DCount. Now, those of you who've been with me for a while probably have a couple of things ringing through your head. First of all, I try to pound into your heads not to use these DLookup, DCount, DSum functions in continuous forms or queries because they are slow. Because for each one of these records, it has to calculate and count all of the other records in the table below it. So that is not by any means a quick calculation. The fact that it seems like it's instantaneous is because I only have 22 records in a local table. If you've got 50,000 records and you're pulling these over a network, this will run slow. There's no qualms about it. If you're running SQL Server, this will run slow. There are certain things you can get away with in smaller databases and local databases than you could over a network or over the internet. So keep that in mind. I do not recommend using DCount in continuous forms unless it's something simple like this in a small table. But this is the only way I can think of to do it unless you pull the records down into a temporary table and do all kinds of crazy tricks.
The other thing I want to mention is this calculation is based on the CustomerID. What happens if the user resorts it based on another field? Well, that's going to scramble those counting numbers. He sorted it on the last name. Scramble it again. Can we fix this? Yes, we could. We will cover that in the Extended Cut for the members. I'll show you how to use a little VBA code to renumber the list based on whatever field the user sorts on, and it will take into account any filters that are applied.
So there you go. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Numbering items in a continuous form
Creating a calculated field in a form
Using DCount to count items in a form
Formatting fields to indicate non-editability
Setting control source property for calculated fields
Handling null values with NZ function
Counting records with a dynamic condition
Refreshing calculations in forms
Handling null CustomerID in DCount
Using IIf function for conditional logic
Considering performance issues with DCount
Impact of sorting on calculated numbering
COMMERCIAL:
In today's video, we're learning about numbering items in a continuous form in your Microsoft Access database. Building on our previous video focused on reports, today we're stepping up to expert level to tackle forms, which lack the convenient running sum property. We'll explore the DCount function to help you create a calculated field that accurately counts records by CustomerID, while also addressing common issues and ensuring a seamless experience by using NZ to handle nulls. Keep in mind that continuous forms may slow down with larger datasets, so use this technique cautiously. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main focus of the video tutorial by AccessLearningZone.com?
A. How to create a report in Microsoft Access
B. How to number items in a continuous form in Microsoft Access
C. How to use Microsoft Excel with Access
D. How to design a table in Microsoft Access
Q2. Why is numbering items in a form more complex than in a report?
A. Reports are inherently simpler to work with
B. Forms require VBA programming
C. Forms do not have a running sum property
D. Reports are not designed for numbering
Q3. What Microsoft Access function is primarily used in this tutorial to number items?
A. DCount
B. DSum
C. DLookup
D. DMax
Q4. What is a possible problem when using DCount with large datasets or over a network?
A. DCount might miscalculate the count
B. DCount will sort the records unexpectedly
C. The calculation will run slowly
D. DCount will fail to execute
Q5. In the absence of records, which function is used to handle null values?
A. DMax
B. DSum
C. NZ
D. Trim
Q6. When creating a calculated field in the form, which property is used to write the calculation formula?
A. Format
B. Row Source
C. Control Source
D. Validation Rule
Q7. What does the NZ function do when applied in this context?
A. It sums all numbers in a field
B. It prevents null values from causing errors by converting them to zero
C. It divides all numbers in a field
D. It concatenates string values in a field
Q8. How is DCount configured to work with the CustomerID field in the tutorial?
A. DCount counts records greater than the current CustomerID
B. DCount counts records equal to the current CustomerID
C. DCount counts all records regardless of CustomerID
D. DCount counts records less than or equal to the current CustomerID
Q9. What is highlighted as a downside to using DCount in continuous forms?
A. It is hard to implement without VBA
B. It complicates the database schema
C. It cannot be used with calculated fields
D. It slows down performance in large or networked databases
Q10. What is suggested as a potential solution if a user resorts the records in a different order?
A. Re-calculate using a different field
B. Disable sorting altogether
C. Use a temporary table
D. Pull the records into another database
Answers: 1-B; 2-C; 3-A; 4-C; 5-C; 6-C; 7-B; 8-D; 9-D; 10-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.
Summary
Today's TechHelp tutorial from Access Learning Zone is focused on numbering items in a continuous form in Microsoft Access. I'm your instructor, Richard Rost. We've previously covered how to accomplish this in reports, which offer a straightforward method due to the running sum property. However, when it comes to forms, this functionality isn't available, requiring a more innovative approach.
For today's lesson, which I categorize as expert-level, we need a few specific functions. This level is positioned between beginner, where you're just starting to understand form building, and developer, which involves deep VBA programming. We're not diving into programming today, but it's crucial to have some familiarity with calculated fields and functions like DCount and NZ.
If you haven't watched the previous video on this topic, I recommend doing so to get a foundation of what we'll be doing today. Understanding how to create and use calculated fields, grasping the DCount function, and utilizing the NZ function to handle null values will be vital. These topics are covered in free videos on my YouTube channel and website.
I'm going to continue using the database from the last session. We have a customer list form, and our task is to add a calculated field for number ordering. By repositioning elements slightly, we can introduce a new field for displaying the order. To indicate that this field is static and non-editable, I'll format it in light gray.
Within the properties, set up the calculated control source using the DCount function. The idea is to count the records where the CustomerID is less than or equal to the current record's ID. This approach gives each record its respective number position based on the CustomerID field.
To handle cases with no records, we'll manage potential null values using the NZ function, converting any null into zero to avoid errors. Once the setup is complete, save, close, reopen the form, and see the effect of the numbering, which aligns with the CustomerID hierarchy.
If record deletions occur, refreshing the calculation using F5 or reopening the form can update the count, although incorporating events for automatic recalculations can be considered later if desired. This method will provide simple counts without engaging in VBA programming.
However, be cautious with using DCount in continuous forms as it can slow down performance, particularly in large datasets or networked environments like SQL Server. While efficient in small, local tables, large-scale implementation could cause significant delays.
Moreover, be mindful that this numbering approach relies on CustomerID for order. Sorting data by other fields will affect the sequence. However, solutions can be devised to address this issue.
I hope this tutorial has enhanced your understanding of using calculated fields for numbering in Access forms. For detailed, step-by-step guidance on everything discussed here, you can find the complete video tutorial on my website at the link below. Live long and prosper, my friends.
Topic List
Numbering items in a continuous form
Creating a calculated field in a form
Using DCount to count items in a form
Formatting fields to indicate non-editability
Setting control source property for calculated fields
Handling null values with NZ function
Counting records with a dynamic condition
Refreshing calculations in forms
Handling null CustomerID in DCount
Using IIf function for conditional logic
Considering performance issues with DCount
Impact of sorting on calculated numbering
|
