When no data report states error

  • Thread starter Thread starter Tom Tripicchio
  • Start date Start date
T

Tom Tripicchio

When I run a report based on a query and there is no data to report the
report states "error" in the body of the report.

Is there a way for the report to say "nothing to report"? Or something else
other than "error" When people see error they think something is wrong
rather than no data or nothing to report.

Thanks, Tom
 
Hi Tom,

The standard way of handling this is to use code such as this in the
report's On No Data event to prevent the report opening at all:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data to report"
Cancel = True
End Sub

If you are opening the report from code in another form, via a
DoCmd.OpenReport statement, you will need to add error handling to trap the
Cancel event which this code generates. It can be as simple as

Private Sub YourExistingProcedureName()
On Error GoTo ErrorHandler
...
... existing code here
...

Exit Sub
ErrorHandler:
If Err.Number = 2501 Then Resume Next
End Sub

HTH,

Rob
 
Rob, thanks for information. The no data command works great.

I think I would like to carry this further. I have 3 daily reports per
company. Lets say 10 different companies. I run these reports from a macro
(each company has their own macro) and they get emailed directly to the
company via the send object command.

If I use the no data command it stills creates a report to be emailed. Is
there a way for the report not to be emailed if there is no data?

Thanks for your help.

Tom
 
Hi Tom,

Sorry, I never use macros, so I don't know how/whether they offer any
conditional execution depending on error codes (I suspect they probably do).
If they do, you would use the Cancel error code (2501) from the report's On
No Data event to stop the SendObject action.

If you mean VBA code, rather than an actual macro, then one way of doing
this would be to put then DoCmd.SendObject statement in an If statement
which checks a flag set in the error handler after opening the report. The
code outline would be:

Private Sub YourExistingProcedureName()
Dim blnReportHasData as Boolean

On Error GoTo ErrorHandler
...
blnReportHasData = True
DoCmd.OpenReport ....
If blnReportHasData Then
DoCmd.SendObject ...
End If
...
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then
blnReportHasData = False
Resume Next
End If
End Sub

You can't test for Err.Number in the If statement, since the Resume Next in
the error handler clears the error condition (ie. resets Err.Number to 0);
that's why I declare a variable for the flag (blnReportHasData) to use in
the If statement. Alternatively, you could remove all error handling and
then test for Err.Number in the main body of the code:

If Err.Number <> 2501 Then Docmd.SendObject ...


If you want advice on macro execution, I suggest you post a new question,
with a different (more appropriate) subject line.

HTH,

Rob
 
Back
Top