Avatar of markloessi
markloessi
Flag for Afghanistan asked on

MS Access Report - not to print if no data

I have a number of reports that generate automatically throughout the day. Use a Windows Scheduled Task to execute a macro in MS Access. Sometimes the reports have no data and a page prints off, I'm wondering if there is a way to have nothing print if there is no data in the query.

Thanks,
Microsoft Access

Avatar of undefined
Last Comment
ghettocounselor

8/22/2022 - Mon
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

No points please.

I generally go with Jim's method #2, because it avoids having to run the underlying query twice (once for the test, a second time when the report is actually opened).

If the queries which the reports are based on are relatively simple and take little time to run then the first method will probably be faster, but if the query takes a while to run, doing so twice just locks things up for longer.
SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

Jim,

Either way (Count or DLookup) still requires that the entire query be run, so I don't see much time savings there.  That is why I tend to go with #2.
... unless a series of reports is based on basically the same data, in which case #1 comes out ahead.
If I have a series of reports based on the same query, I will generally create a temp table, so that I don't have to rerun the query multiple times.  Then I'll add some code (just prior to the OpenReport call) that tests the DateCreated property of the temp table and if the time since creation exceeds some value (5, 10, 30, ... minutes - depending on the importance of 'current' information) I'll ask the user if they want to update the data before completing the report.
markloessi

ASKER
This is not a complex query, runs in <1min generally and returns <dozen items. It runs every hour and I'm just trying to keep from wasting paper.

I'm in need of understanding how to implement the code for
Use the reports NoDataEvent() and set Cancel = True
I'm assuming that there is some syntax for this area.
no data event
Also this bit here
Your calling code must have error trapping in place as this will return a error 2501 to the calling code (operation cancelled, which is correct as the open is canceled).
not sure how to implement this.

Thanks for the help!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

Private Sub Report_NoData(Cancel as Integer)

    Cancel = True

End Sub

Then, in the main code where the OpenReport method is called, you nee something like

On Error Goto ProcError

docmd.OpenReport "rpt_Name"


ProcExit:
    Exit Sub
ProcError:
    if err.number = 2501 Then
        Resume next
    else
        msgbox err.number & vbcrlf & err.description, vbOkOnly, "error in report process"
    end if
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
markloessi

ASKER
Thanks for the help, sorry to tarry on getting this closed, went on vacation.
ghettocounselor

Juat a little clarity added to this:

you open your report in design view
in properties of the 'report' on 'event' tab use the 'on no data' field and set to [event procedure]
click on '...' button
enter this code (part of it will be entered for you)
Private Sub Report_NoData(Cancel As Integer)
Me.Detail.Visible = False
Me![MTmsg].Visible = True
Cancel = True

End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.