Are There Records
By Richard Rost
2 years ago
Check for Table Records in Access Before Actions
In this Microsoft Access tutorial, I will show you how to determine if there are records in a table or query before performing actions like opening a form or report. We will explore using DCount and DLookup functions, handling errors, and optimizing efficiency with advanced developer techniques. Thanks to moderator Kevin Robertson for the idea for this video, and his initial function.
Members
There is no extended cut, but here is the database download:
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
Code Vault
Keywords
TechHelp Access, DCount function MS Access, checking for no records in Access, display message no records, MS Access form error handling, open report no data event, DLookup vs DCount, AreThereRecords function in Access, error handling in Access reports, contact table validation Access, NZ function in VBA, visual basic for applications Access development, AccessLearningZone tutorial
Intro In this video, we'll talk about how to determine if there are records in a table or query before opening a form or report in Microsoft Access. I'll cover using the DCount function as well as writing your own custom VBA functions to check for records, including one that uses DLookup for better performance with large tables. We'll also discuss handling the On No Data event in reports, how to display custom messages when no records are found, and some tips for error handling in your Access applications.Transcript 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.
TOPICS:
Determining if there are records in a table or query
Using DCount to check for records
The On No Data event in reports
Creating a function to count records
Creating a function using DLookup
Writing AreThereRecords function with DLookup
Using record sets to find the first field name
Implementing error handling with On Error Resume Next
Displaying custom messages for no records found
Cleaning up record sets in VBA
COMMERCIAL:
In today's video, I'll show you how to check if there are records in a table or query before you try to open a form or report in Microsoft Access. We'll discuss using DCount and DLookup functions, and demonstrate how to avoid errors by displaying a message when no data is found. This tutorial is based on a forum discussion where Kevin, one of my moderators, suggested a more efficient method to handle no data scenarios. From integrating Kevin's function to my optimized version using record sets, you'll learn how to ensure smooth functionality in your Access applications. 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 objective of the video tutorial presented by Richard Rost?
A. To learn how to create forms and reports
B. To determine if there are records in a table or query before opening forms or reports
C. To understand how to debug Microsoft Access applications
D. To learn about Microsoft Access installation and setup
Q2. What method does Kevin suggest using before opening a form or report to check if there are records?
A. DLookup
B. On Error Resume Next
C. DCount
D. SQL query
Q3. What issue arises when using the On No Data event for reports?
A. You cannot open the report at all
B. It throws an error saying "The OpenReport action was canceled"
C. It fails to display any message
D. It closes Microsoft Access application
Q4. How does the DCount function work in Kevin's solution?
A. It counts the total number of fields in the table
B. It counts the number of records in a data source
C. It checks if the first record exists
D. It sums up the values in a specific column
Q5. What downside does Richard mention about using DCount with large tables?
A. It could lead to inaccurate results
B. It can't handle more than 1000 records
C. It has to read through all of the records, which can be slow
D. It might delete records during counting
Q6. Richard suggests using an alternative to DCount. What is it?
A. SQL SELECT statement
B. DSum
C. DLookup
D. Aggregate functions
Q7. Why does Richard recommend using DLookup for checking if records exist?
A. It provides more detailed error messages
B. It opens the entire table which is faster
C. It pulls the first record, making it faster for large tables
D. It can edit the first record
Q8. In Richard's AreThereRecords function, how does it retrieve the field name?
A. By calling the FieldName function directly
B. By using rs.Fields(0).Name to get the name of the first field
C. By using a hardcoded field name in the function
D. By using a SQL query to fetch the name
Q9. In Richard's final solution, what does AreThereRecords return based on its check?
A. It always returns false if no specific conditions are met
B. It returns the total number of records found
C. It returns true if any record exists, otherwise false
D. It returns an error message if no records are found
Q10. Why might someone prefer to use a message box outside of the AreThereRecords function?
A. The function cannot handle message boxes
B. To customize the message based on different contexts or requirements
C. To reduce the complexity of the function
D. It is required for all functions in Access
Q11. What is the typical customization that developers might add to Kevin's function?
A. Adding complex error handling
B. Changing the table structure dynamically
C. Including custom messages based on the data source
D. Altering the DCount to check for specific fields
Q12. What is a common element in both Kevin's and Richard's solutions for checking record existence?
A. Both return the count of records
B. Both avoid using the On No Data event entirely
C. Both solutions involve checking for the presence of records before performing an action
D. Both are limited to use with forms only
Q13. What other resources does Richard mention towards the end of his video?
A. Installation guides for Access on various operating systems
B. Links to his other lessons and the forums on his website
C. Tutorials on using Excel and Word
D. Guides on purchasing Access and SQL Server licenses
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-B; 11-C; 12-C; 13-B
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 focuses on how to determine if there are records in a table or query before taking action with them in Microsoft Access. This is an important consideration for situations where you want to open a form or report and need to prevent errors that occur if there is no data available. In this lesson, I will show you ways to check for the existence of records before you proceed, which is a valuable skill for Access developers.
This topic came up in a discussion on my website's forums. Kevin, one of our moderators, shared his approach to this common problem. He created a function that checks if there are any records in a table or query before displaying a generic message if none are found. His method uses the DCount function to count the records in a given data source and can be used before opening forms or, more commonly, reports. This way, instead of opening a blank report or triggering an error, you can show the user a message such as "There are no records to display."
Let's consider an example: you have a report based on your contact table. You set up a button to open this report. If all the records are deleted and you try to open the report, Access displays a blank report, which is not very helpful for users. In reports, Access provides the On No Data event, which you can use to display a message when a report has no data and cancel the operation. However, this often results in an awkward error message from Access indicating that the OpenReport action was canceled. While you can bypass this with On Error Resume Next, that is more of a workaround than a real solution.
Kevin's suggestion is to avoid the problem entirely by checking if data exists before trying to open the report. He built a function in a global module that can accept a table or query name as a text string and returns the count of records using DCount. If there are no records, you can display a message and prevent the subsequent action from being attempted.
For example, before opening your contact report, you could call this function to check the number of records in the contact table. If the count is zero, you display a message and exit the subroutine. If records exist, the report opens normally.
After Kevin posted his solution, I chimed in with an alternative idea. If your only concern is whether any records are present (not the actual count), you might want to use DLookup instead of DCount. The reason is that DCount has to scan through every record in the data source to count them, which can be inefficient with large tables. DLookup only retrieves the first record it finds, which is much faster, especially with very large datasets. The difference is negligible with small tables, but with a large number of records DLookup provides better performance.
I put together a function, which I called AreThereRecords, that uses DLookup. With DLookup, you have to specify a field name (unlike DCount, which can use the asterisk), so the function takes both a field name and a data source name. It uses DLookup to see if there is a value in that field and returns either true or false depending on whether a record is found.
Building on that, I revised my approach so the function could automatically determine the first field in the table or query by opening a recordset, finding out the name of the first field (often an ID), and then using DLookup with that field and the data source. This way, you do not need to specify a field name each time you call the function. It checks if DLookup of the first field is null or not and returns a true or false value accordingly.
You can then use this AreThereRecords function in your code. Before attempting to open a report or a form, you can check if records exist in the relevant table or query. If records are found, you proceed as normal. If not, you can display a message to the user. This process helps you avoid awkward errors and provides a better user experience.
If you are interested in this kind of advanced developer technique, the forums on my website are a great resource, and I also offer many lessons that cover similar topics in more depth. For more information about concepts like If-Then statements, DCount, recordsets, the On No Data event, and more, check out the links on my website.
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 List Determining if there are records in a table or query
Using DCount to check for records
Using the On No Data event in reports
Creating a function to count records
Using DLookup to check for records efficiently
Writing the AreThereRecords function with DLookup
Using record sets to obtain the first field name
Displaying custom messages for no records found
Exiting a sub if no records are present before opening forms or reports
Cleaning up record sets in VBA functions |
