Link to home
Start Free TrialLog in
Avatar of markloessi
markloessiFlag 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,
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of 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.
User generated image
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!
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help, sorry to tarry on getting this closed, went on vacation.
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