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.