How to Display "No Data" Message if report is empty

  • Thread starter Thread starter sara
  • Start date Start date
S

sara

Hi -
Pretty new to Access here, and finding it's helping us
with great information.

I have some reports, like "cancelled orders" that often
will have NO Data for the time period requested to run.
The report comes up with all sorts of #Error and looks
unfriendly to the user.

Is there any way I can know that the report is empty (or
the query returned no records) and print "NO Data" message
to the user?

Thanks.
sara
 
sara said:
I have some reports, like "cancelled orders" that often
will have NO Data for the time period requested to run.
The report comes up with all sorts of #Error and looks
unfriendly to the user.

Is there any way I can know that the report is empty (or
the query returned no records) and print "NO Data" message
to the user?
Click to expand...


You can use the report's NoData event to cancel the report
and pop up a message.

MsgBox "There are no cancelled orders between " _
thestartdate & " and " & theenddate
Cancel = True

where thestartdate is the exact same thing that your query
uses as the start date parameter.

Note that you will alse get a message that the report was
cancelled. If you're using button on a form to open the
report, the button's Click event procedure can use error
handling to ignore error 2501 to prevent the extra message.
 
Thanks, Marshall. I understand and tried what you
suggested and have 2 problems:
1. The MsgBox is too small - doesn't display all the
text, including the dates. How can I make the box bigger?

2. I tried the error handling. I have simple error
handling (see code) to display a message to tell me if
there is a problem. I tried to insert code for 2501, and
clearly haven't done that right. The user presses a
button and it runs ALL the weekly reports (5 right now) 3
of which could be empty. Can you tell me how to code the
error handling properly? My code is below.

Thanks so much. You (and this site) have been a fabulous
life-saving help. I'm the only "programmer" here and when
I get stuck it's curtains!
Sara

If (Me!grpReportList = 2) Then ' Weekly Reports
' Show Year, Qtr buttons (Qtrly)
EnableWeeklyButtons

stDocName = "rptPODetailsforWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptSummaryWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Summary Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptImpactofFreightChanges"
On Error Resume Next
If Err.Number = 2501 Then
Resume Next
Else
GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with" & _
" Weekly Freight Report"
End If
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptPOCancelsbyDates"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly Cancels Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize



End If
 
Comments inline below
--
Marsh
MVP [MS Access]

Thanks, Marshall. I understand and tried what you
suggested and have 2 problems:
1. The MsgBox is too small - doesn't display all the
text, including the dates. How can I make the box bigger?
Click to expand...

The standard message box automatically expands to display
all of the text. Post your code (along with what you expect
the message's text to be) and maybe I'll be able to spot
something.

2. I tried the error handling. I have simple error
handling (see code) to display a message to tell me if
there is a problem. I tried to insert code for 2501, and
clearly haven't done that right. The user presses a
button and it runs ALL the weekly reports (5 right now) 3
of which could be empty. Can you tell me how to code the
error handling properly?

If (Me!grpReportList = 2) Then ' Weekly Reports
' Show Year, Qtr buttons (Qtrly)
EnableWeeklyButtons

stDocName = "rptPODetailsforWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptSummaryWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Summary Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptImpactofFreightChanges"


strStepErrorMsg = "Tell IT there was a problem with" & _
" Weekly Freight Report"
Click to expand...

On Error Resume Next
DoCmd.OpenReport stDocName, acPreview
Click to expand...
' Save the error number
ErrNum = Err.Number
' Restore your regular error handler
On Error GoTo PrintPOReports_Err
' Now check the error status
If ErrNum = 0 Then
' no error, carry on
DoCmd.Maximize
Click to expand...
ElseIf ErrNum <> 2501 Then ' Check for real error
' You can not use GoTo to get into
' your error handler
Err.Raise ErrNum
End If ' Note that 2501 just goes onto next report
stDocName = "rptPOCancelsbyDates"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly Cancels Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize
End If
Click to expand...

This way of handling errors may be a little convoluted, but
I can't tell if there might be a better way without knowing
what you do with other possible errors. It may be better to
move your existing error logic to a Function procedure so
you don't have to do all the checks so many times.


 
Marshall -
Thank you thank you thank you.
I am actually having fun putting all this code in. I did
take a class on coding - but I have to go back to my notes
to see the called function creation. I see where I am
copying and pasting the same code over and over, so I'll
work on that later. Fantastic help and great suggestion.
I truly appreciate the help (and I can imagine it's a
little difficult and frustrating for you to see all this
junky code - I'm getting better, tho!)

Sara

-----Original Message-----
Comments inline below
--
Marsh
MVP [MS Access]

Thanks, Marshall. I understand and tried what you
suggested and have 2 problems:
1. The MsgBox is too small - doesn't display all the
text, including the dates. How can I make the box
Click to expand...
bigger?

The standard message box automatically expands to display
all of the text. Post your code (along with what you expect
the message's text to be) and maybe I'll be able to spot
something.

2. I tried the error handling. I have simple error
handling (see code) to display a message to tell me if
there is a problem. I tried to insert code for 2501, and
clearly haven't done that right. The user presses a
button and it runs ALL the weekly reports (5 right now) 3
of which could be empty. Can you tell me how to code the
error handling properly?

If (Me!grpReportList = 2) Then ' Weekly Reports
' Show Year, Qtr buttons (Qtrly)
EnableWeeklyButtons

stDocName = "rptPODetailsforWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptSummaryWeek"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly PO Summary Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize

stDocName = "rptImpactofFreightChanges"


strStepErrorMsg = "Tell IT there was a problem with" & _
" Weekly Freight Report"
Click to expand...

On Error Resume Next
DoCmd.OpenReport stDocName, acPreview
Click to expand...
' Save the error number
ErrNum = Err.Number
' Restore your regular error handler
On Error GoTo PrintPOReports_Err
' Now check the error status
If ErrNum = 0 Then
' no error, carry on
DoCmd.Maximize
Click to expand...
ElseIf ErrNum <> 2501 Then ' Check for real error
' You can not use GoTo to get into
' your error handler
Err.Raise ErrNum
End If ' Note that 2501 just goes onto next report
stDocName = "rptPOCancelsbyDates"
On Error GoTo PrintPOReports_Err
strStepErrorMsg = "Tell IT there was a
problem with Weekly Cancels Report"
DoCmd.OpenReport stDocName, acPreview
DoCmd.Maximize
End If
Click to expand...

This way of handling errors may be a little convoluted, but
I can't tell if there might be a better way without knowing
what you do with other possible errors. It may be better to
move your existing error logic to a Function procedure so
you don't have to do all the checks so many times.



.
Click to expand...
 
Back
Top