Preventing a report printing out on no data

  • Thread starter Thread starter Rebecca Hooker
  • Start date Start date
R

Rebecca Hooker

I have four reports running from four queries to pick out
a particular report for each record depending on their
data. As they are all to be run from the same set of
records I have a macro which opens and prints out each
report in turn .
However, if there are no records matching the criteria for
a specific report it obviously prints out one copy of the
report saying #error# on it. I have tried to set the on no
data property to close the report if this is the case
instead of printing but it does not work. Is there any way
of suppressing a report to prevent it being printed out if
there are no records for that report?
 
Rebecca said:
I have four reports running from four queries to pick out
a particular report for each record depending on their
data. As they are all to be run from the same set of
records I have a macro which opens and prints out each
report in turn .
However, if there are no records matching the criteria for
a specific report it obviously prints out one copy of the
report saying #error# on it. I have tried to set the on no
data property to close the report if this is the case
instead of printing but it does not work. Is there any way
of suppressing a report to prevent it being printed out if
there are no records for that report?
Click to expand...


What do you mean, the NoData event doesn't work? That's
the standard way of doing this so you probably have
something, somewhere that isn't right.
 
The query that the report runs from shows no records at
all, so I don't think that there is a problem there, it
may be my code. The code I have used on the OnNoData
property is:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Err_Report_NoData

DoCmd.Close

Exit_Report_NoData:
Exit Sub

Err_Report_NoData:
MsgBox Err.Description
Resume Exit_Report_NoData

End Sub

When I then try to open the report it gives the
message 'This action can't be carried out while processing
a form or report event'
I do also have a macro whichs runs when the page is
formatted, could this be the problem?
 
Rebecca said:
The query that the report runs from shows no records at
all, so I don't think that there is a problem there, it
may be my code. The code I have used on the OnNoData
property is:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Err_Report_NoData

DoCmd.Close

Exit_Report_NoData:
Exit Sub
Click to expand...


That may indeed be the issue. Instead of closing the
report, try canceling it:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

This will cause a message to pop up saying that the report
was canceled. If you don't want to see that message, you
have to use VBA code to open the report so you can trap the
"error" and suppress it. You can not do this with a macro
since macros do not provide a mechanism for trapping errors.
 
Thank you very much, code works a treat but as you say the
error messages come up. Not being all that good with code
would you be able to give me any ideas which would allow
for four different reports to be printed off one after the
other with these error messages suppressed?
 
Rebecca said:
Thank you very much, code works a treat but as you say the
error messages come up. Not being all that good with code
would you be able to give me any ideas which would allow
for four different reports to be printed off one after the
other with these error messages suppressed?

errors.
Click to expand...


You could try using the Tools - Macros - Convert to VBA menu
item to get started. Then you can modify the generated code
to along these lines:

Sub buttonname_Click()
On Error GoTo ErrHandler
DoCmd.OpenReport "report1", . . .
DoCmd.OpenReport "report2", . . .
DoCmd.OpenReport "report3", . . .
DoCmd.OpenReport "report4", . . .

ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
EndSelect
End Sub
 
Works great many thanks, no more wasted paper!
-----Original Message-----



You could try using the Tools - Macros - Convert to VBA menu
item to get started. Then you can modify the generated code
to along these lines:

Sub buttonname_Click()
On Error GoTo ErrHandler
DoCmd.OpenReport "report1", . . .
DoCmd.OpenReport "report2", . . .
DoCmd.OpenReport "report3", . . .
DoCmd.OpenReport "report4", . . .

ExitHere:
Exit Sub

ErrHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
EndSelect
End Sub
Click to expand...
 
Try this:

Private Sub Report_NoData(Cancel As Integer)
Call ReportNoData(Cancel)
End Sub

'---------------
In a separate module (e.g., basReports):
'---------------

Option Compare Database
Public intCount As Integer
Sub ReportNoData(intCancel As Integer)
intCount = intCount + 1
If intCount = 1 Then
MsgBox "No data found. ", vbOKOnly
Else
intCancel = 1
intCount = 0
End If
End Sub

'-------------------------------------------
In order for this to work (for some reason) you need to
include a 'page footer. You can use a page number field
such as:
'-------------------------------------------

="Page " & [Page] & " of " & [Pages]
 
Back
Top