No Data
By Richard Rost
4 years ago
On No Data Event in Microsoft Access Reports
What happens if you go to print a report and there are no records? You waste a sheet of paper, that's what. It would be nice if you could pop up a warning message saying, "sorry, you've got no records to print," and then cancel the report print operation.
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, on no data, report_nodata, no records, nothing to print, Report.NoData event, Cancel printing a report if it does not contain any records, Determine if a report has no data, ReportNoData, What to do When There Is No Data to Display, Report Has No Data
Subscribe to No Data
Get notifications when this page is updated
Intro In this video, I will show you how to display a custom message when a report in Microsoft Access has no data, preventing unnecessary pages from being printed. We will cover how to use the On No Data event in your reports and add a quick VBA code solution to alert users when a report is empty. This Fast Tips tutorial is perfect for anyone looking to avoid wasting paper and improve their Access reports.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I am going to show you how to display a message if a report has no records in it instead of just endlessly sending paper to your printer, which is not good.
So, here is the problem. You accidentally hit print on a report with no data in it. If you are like me and you have everything go to print preview, that is no big deal. You just see an empty report. But I know a lot of you like to send stuff straight to the printer. I have actually got one or two things that I send straight to the printer too, so fine, I am guilty as well.
Best case scenario, you waste a sheet of paper. Worst case scenario, you waste multiple sheets of paper because you might have different formatting. I do not know what your reports look like.
Now, that happens and conservationists might picket your company. Then you get fired for wasting resources. That is never a good thing. And then there is unrest in the forest and trouble with the trees because the trees hate you now for wasting all that paper.
The solution is to use the On No Data event. That means there is no data in this report. It will just pop up a message saying, hey, there is nothing to print. Everyone is happy and the trees are all kept equal by hatchet, axe, and saw. That is how it went. That does not make any sense because now the trees are not going to be happy.
Anyways, let me show you how to do this. Before we get started, if you have never done any VBA programming before, now is your chance to learn. Free video, it is on my website, it is on my YouTube channel. Intro to VBA. It is about 20 minutes long. It teaches you all the basics.
We really only need two lines of code. You could probably do it in one, but two is good. Go watch this. It is nice and simple. It is not scary. Do not be scared of VBA.
Here is a copy of my TechHelp free template. This is a free download. You can grab a copy of it on my website if you want to. In this database, I have a customer form. Customers can have orders, and if you go to print an invoice out, there it is right there, and there is a nice little pretty invoice.
So, what happens if you go to a new blank record and there is nothing in here? You go to print the invoice and it is blank. Well, you just wasted a print. Now, I have print preview going on here, but if you have this set to go straight to the printer, you just wasted a piece of paper.
If you want to see how I built this database, go watch my Invoicing video. It will show you exactly how I built this.
Now, what I want is, when I click on this button, if the report has no data in it, it is just going to say, sorry, nothing to print.
There are a couple ways you could tackle this problem. You could, right in the button, say, if the order ID is null, just do not print it. But there is a cool thing called the On No Data event inside the report here.
Open this up, go to the report's properties, go to Events, and find On No Data. This will happen, and this is also good because you could use this in subforms and stuff too, to not waste space.
That opens up our VBA code window. I am inside the Report No Data event. Notice it can be canceled. If you set Cancel to True, it basically does not display the report or does not print it. So, you could, with just one line of code, say Cancel = True. I know it says Integer, but True is fine. It comes back as a negative one. It is good. It is not looking for zero or not zero. It is either False or not False.
Before or after you do that, you could pop up a little message box. You could say a message box, "Nothing to print," like that. Comma vbCritical, and then "No Data" or whatever you want for the title.
That is it. That is all you need. Close that. Close that. Open it up. Print this one and you are good.
Let us go try to print something with no data in it. No data. Nothing to print. Nothing to see here. That is it. That was a fast tip.
There is your Fast Tip. That was a pretty fast one. They are not all half an hour long. If you want to learn more about the On No Data event, I cover it in my Access Developer 28 class. This is a more advanced class. I cover lots and lots of stuff in here including batch printing, email invoices, that kind of stuff.
I do spend some time on the On No Data event because there is more you can do with it than what I just covered here. I just went over the basics. That is why it is a Fast Tip. But go check this class out.
So, for AccessLearningZone.com, I am Richard Rost. That was your Fast Tip for today. I hope you learned something and we will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free.Quiz Q1. What is the main problem discussed in the video regarding printing reports in Access?
A. Reports with no data can unnecessarily use paper when sent to the printer
B. Reports always require manual data entry before printing
C. Access does not support print preview
D. Reports cannot be formatted before printing
Q2. What happens if you accidentally print a report with no data and have it sent directly to the printer?
A. The report prints one or more blank pages, wasting paper
B. Access automatically cancels the print job
C. An error message prevents printing
D. The report prints sample data
Q3. What is the solution recommended in the video to handle empty reports?
A. Use the On No Data event to display a message and cancel the print
B. Manually check every report before printing
C. Set all reports to print preview only
D. Add a warning label on each blank report
Q4. Which event in the report's properties is used to trigger code when there are no records to print?
A. On Load
B. On Open
C. On No Data
D. On Error
Q5. What is the effect of setting Cancel to True in the On No Data event of a report?
A. The report will not be displayed or printed
B. The report will automatically close after printing
C. The report will print with a watermarked message
D. The report will save as a PDF
Q6. What does the video suggest you can also do before or after canceling the print job in the On No Data event?
A. Display a message box notifying the user there is nothing to print
B. Email the report to the user instead
C. Log an error message to a file
D. Export the report data to Excel
Q7. What kind of VBA code is needed to implement the On No Data logic described in the video?
A. Just one or two simple lines to cancel and show a message
B. Several complex procedures and functions
C. Custom modules and classes
D. No VBA code is necessary
Q8. In which class does the instructor cover more advanced techniques for the On No Data event?
A. Access Developer 28 class
B. Intro to VBA video
C. TechHelp Free Template
D. Access Basics 101
Q9. What benefit does the On No Data event offer besides preventing unnecessary printing?
A. It can also be used in subforms to avoid wasting space
B. It improves database performance
C. It enables automatic data entry
D. It formats data automatically
Q10. According to the video, what should you do if you want to learn the basics of VBA?
A. Watch the free Intro to VBA video on the instructor's website or YouTube channel
B. Enroll in the Platinum membership only
C. Download the free TechHelp template
D. Submit a TechHelp question
Answers: 1-A; 2-A; 3-A; 4-C; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
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 video from Access Learning Zone addresses a common issue in Microsoft Access: how to display a message when a report contains no records, preventing unnecessary printing and wasted paper.
The problem often occurs when you try to print a report that has no data. If you typically use Print Preview, you simply see an empty report, which is not much of an issue. However, many users, myself included, have some reports that print directly to the printer. In those cases, sending a blank report to the printer is wasteful. Depending on the layout of your reports, you might even waste several sheets of paper. It's not just an inconvenience, but also an unnecessary use of resources.
To solve this, Access provides the On No Data event. This event fires whenever the report tries to print and there is no data available. By taking advantage of this event, you can display a message box to inform the user that there is nothing to print, and then cancel the print action. This way, you avoid unnecessary print jobs and help save paper.
If you're not familiar with VBA programming, this is a perfect place to start. The process only takes a couple of lines of code and is very accessible, even for beginners. If you want to learn VBA basics, I have a free introductory video available on my website and YouTube channel. It's about 20 minutes long and covers all the essentials.
In my demonstration, I use the TechHelp free template database, which you can download from my website. This database features a customer form and the option to print invoices. If you try to print an invoice for a new, blank customer record, you'll notice the invoice report is empty. With print preview, this isn't a major problem, but if you print directly, you waste a sheet of paper.
To address this, you can use the On No Data event built into reports in Access. In the report's property sheet, you'll find the Events tab, and from there, the On No Data event. Opening this takes you to the VBA editor, where you can set the Cancel property to True, which stops the report from displaying or printing. Before or after that, you can add a message box to notify the user that there is nothing to print. That's really all there is to it. These steps make sure users are alerted when there's no data to print, and you avoid wasting resources.
If you're interested in a more detailed database example, take a look at my Invoicing video. It walks you through the process of building the sample database step by step.
This Fast Tips video covers the basics of the On No Data event. For more advanced techniques, I recommend my Access Developer 28 class. In that course, I cover batch printing, emailing invoices, and several other ways you can use the On No Data event for more complex scenarios.
If you want to support the channel and get more out of Access Learning Zone, there are several membership options. Silver members and above get access to all extended cut TechHelp videos and receive one free beginner class each month. Gold members gain access to downloadable sample databases, the Code Vault, higher question priority, and a free expert class every month once you've completed the beginner series. Platinum members receive all of those perks, plus even higher priority for TechHelp questions, access to all full beginner courses for every subject, and one free developer class each month after the expert series. These courses include not just Access, but also Word, Excel, Visual Basic, and more.
No matter your membership status, all TechHelp videos will remain free for everyone, with more videos to come as long as you keep watching.
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 Displaying a message when a report has no data
Using the On No Data event in Access reports
Adding VBA code to the report's No Data event
Using MsgBox to alert "Nothing to print"
Cancelling report display with Cancel = True in VBA
Accessing report event properties in Access
Preventing blank printouts in Access reports |
