Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about determining if there are records in a table or query before you do stuff with them. What kind of stuff? I'll open a form, open a report. You can basically check to make sure there's going to be data before you get an error with the open form or the open report. This is a developer-level video, and I've actually labeled it extra nerdy because we're going to talk about some extra nerdy theory today. So, uh, yeah, let's get to it.
Today's video comes from a conversation we were having in the forums on my website, and Kevin, one of my moderators (love Kevin), posted this. He says, "I wrote a function to display a generic message if there are no records in a table," which also would work for a query. And he basically wrote it with a DCount. It just checks to see if there are records in whatever you're trying to open, whether it's a, you know, a form or report or whatever, and it'll just give you a message, "There's no records to display." This comes up more with reports than anything else, but it could also happen with forms.
Let's say you've got a contact report. It's based on your contact table, right, all your contact information. And if you've got a button, let's say that opens up this contact report, and there's no data in it, maybe you're doing like, you know, accounts receivable or whatever, and there's just no records. Well, if you make a button, let's make this button open that report. So I'll change it to DoCmd.OpenReport ContactR, acPreview. Okay, and it opens fine now, but if there's no records in it, let's go to contacts and just delete all the records. If I open it now, I get a blank record, right, or blank report, which isn't very friendly.
Now you can use On No Data. This is one of the things I pointed out to Kevin with reports. At least you can come in here, there's an event called On No Data, right, No Data right here. If no data, you could say, you know, MessageBox "No records," and say Cancel = True, but watch what happens if you do this. It says, "No records," and then you get "The OpenReport action was canceled." This happens in several different places. This is just one example. This guy here throws an error. Now sure, you could put On Error Resume Next above it, and that fixes the problem. But a better solution, and the one that Kevin is suggesting, is "Hey, let's just count the number of records that are in there before we do this," right, and let the user know that "Hey, there's nothing in this record set."
So in my Global module, let me open this up. Kevin wrote a function. We'll call it Public Function GetCountData, and then you send in a data source name, right. That could be a table or a query as a string, and this will return a Long, right, the count of the records in that data source. I'm paraphrasing Kevin. He wrote a more complicated one where you can, you know, have it checked based on different tables and stuff. This is the quick version. And then inside here, we'll say Dim L as Long L = DCount("*", dataSourceName). We're going to count all the records from the data source name. Now if you want to customize your message in here, you can. You can say, If L = 0 Then MessageBox "There are no records in" & dataSourceName. And you can do whatever other stuff you want in here. End If. Right, and then we'll just return the value as the function value. Right, GetCountData = L.
And now in our forms and such, before we try to even open that, we can just say, If GetCountData("contactT") = 0 Then Exit Sub, before it even tries to open the report. You come out here, hit the button. "There's no records in contactT." Okay, and if there is something in there, then it works, and there's your report.
Okay, now as we always do in the forums, someone else always has to chime in, and that someone else is me. This one here, you don't want to look at that one, right? I said, "If your goal is to see whether or not there are any records, you should use the DLookup instead of DCount. DCount is great if you want the count of all of the records, but DCount and DSum, they have to read through all of the records to determine how many there are. So if you got 50,000 records, it's got to say, "Okay, I got 50,000 records here. It's got to count through them to figure that out." Whereas with DLookup, it just pulls the first record, which is much, much faster. It's faster if you got huge tables; if you got little teeny tiny tables, you can't tell the difference, right?
So then I put together this one. It says AreThereRecords, right? You got to send it a field name with this version of it because this one up here, I tried to use a star, but you can't use the star with DLookup. So ignore this one, folks. All right, you send it the field name and the data source, and then it does a DLookup of the field name in the data source, and then if it returns a zero, right, because you're using NZ here, which will make it zero if it's null if it can't return a record, and that just returns true or false.
And then a few minutes later, I said to myself, "Well, what if the function could look up the first field name in the table?" All right, assuming of course, yeah, it should be - it's best if it's an ID, but it doesn't have to be; it could be any field. And so then I put together this guy. And let me copy this, and we'll put it over in the VB Editor. We'll go through it. Let's go back to Global Module and down here, paste that one in. All right, I'm going to call it AreThereRecords. You send it a data source name again. Okay, this one returns a true or false, Are There Records.
Now this one's going to open up a record set, which basically is a pointer to a table or query. Okay, and it's going to say FieldName as String = rs.Fields(0).Name. What does that mean? Well, every record set has a collection of fields, right, CustomerID, FirstName, LastName, Address. Those are all fields. So Field(0), the zero item of the Fields collection, is the first record. Give me its name, okay, which would be, you know, CustomerID or whatever the first field happens to be; it doesn't really matter. So then AreThereRecords is going to be Not IsNull. We're going to try to DLookup that field name from the data source. So DLookup CustomerID from the customer table. Okay, is that null? That'll be true or false, and then we're just not that. So if it returns a record, AreThereRecords will end up true, right. If it's null, this comes back false. Because IsNull will be true, and then this will be false, and then AreThereRecords will be false. Okay, close the record set, clean it up, and now you can use AreThereRecords. I didn't put a message box in mine. If you want to put a message box in yours, that's fine too.
Right, so what I did was, where's the thing, let's go over here. So I said, "If AreThereRecords("contactT") Then," we're going to open it up. Otherwise, MessageBox "No records." And if I choose, I like to put my stuff out here because usually I find that this is going to be different for every case, but it's up to you, right. So now, if I hit the button - oops hang on. Oh, contactR, duh, that's my fault. I'm looking in contactR instead of contactT, the data source. Okay, that was my bad. Hit it, and there it is. Now, if I delete the records, let's try it again. There we go, no records found.
So there's two great functions for you. Go back to where they were, right-click, Definition. There's Kevin's, there's mine. I'll put these in the Code Vault for you Gold Members. If you like this kind of super nerdy stuff, come check out the forums on my website. They're absolutely free. Also, I've got lots and lots of developer lessons where I talk about all kinds of nerdy stuff like this too. You'll find links down below for most of the things I talk about today: If-Then statements, DCount, record sets, the On No Data event, that kind of stuff. So check all that out.
And that's going to be your TechHelp video for today. Hope you learned something. Thanks to Kevin for this idea. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our Diamond Sponsors. First, we have Juan Soto with Access Experts, software solutions manufacturing experts specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at AccessExperts.com.
Another shout-out to Semmy Shamam from Shama Consultancy. Semmy is a certified Microsoft Office Specialist, and he not only offers Access application development but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Semmy is your guy. Check them out at ShamaConsultancy.com.
For a complete video tutorial on this topic, please visit https://599cd.com/AreThereRecords?key=Dev.To
Top comments (0)