Handling Report with No Data

I have a form in the Access 2013 program that allows the user to input selection criterion for a report.  In some cases there will be no data matching their selection criterion.

To handle that situation I have logic in the report 'NoData' event.

Private Sub Report_NoData(Cancel As Integer)
'
MsgBox "No Data Meets Selection Criterion"
Cancel = True
'
End Sub

Open in new window


If I don't cancel the report it thorws and errror in the logic referencing one of the fields on the report.

The above logic work and the report is cancelled but I now get a 'Runtime Error 2501.  The Open Report Action was cancelled' error on the statement that called the report:

DoCmd.OpenReport newReportName, acViewPreview, , , acDialog

Open in new window


I realize I can put error handling right after the 'OpenReport' command but I will have to do this everywhere I call a report in the application.

Is there a cleaner way of handling this situation?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Mick,

the answer is that you have to handle the error in the calling program. Cancelling a report, even though there is a valid reason to do so (like No Data), throws an error. There is a quick way to fix this. Often, DoCmd.OpenReport is the last thing that is done (except for cleaning up, etc). Rather than testing for specific error numbers, simply search for DoCmd.OpenReport and before each statement with OpenReport, add:
On Error Resume Next

Open in new window

after the first one, copy it (to the Windows clipboard) so you can paste it for quicker changing (this will probably take you 5-15 minutes ... or maybe less). The result, in generic terms, would be:
On Error Resume Next
DoCmd.OpenReport sReportname, acPreview   ' or whatever view you want -- and possibly more parameters

Open in new window

WHERE sReportname is a string variable containing the report name, or a literal value with the name of the report

If you do want to return error handling because more processing is done, or trap the errors, you can, of course, add more code. This, however, is the easy way ;)

have an awesome day,
crystal
0
mlcktmguyAuthor Commented:
Will this override any other error handling I have in the subroutine?  I use MZTools to set up standard error logic in all of my subroutines and functions.

Most of them start and end like this

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo Form_Load_Error
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
 
   much logic and code........

On Error Resume Next
DoCmd.OpenReport sReportname, acPreview   ' or whatever view you want -- and possibly more parameters

more logic and code.....


 
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
Form_Load_Error:
                               sysErrorHandler Err.Number, Err.Description, "Form_Load", "Form_frmAddrChng_Report", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

Open in new window


When I get to 'more logic and code.....' will any errors encountered 'Resume Next'  or 'On Error GoTo Form_Load_Error'?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Russell,

> "this override any other error handling I have in the subroutine?"

yes, that is why I mentioned that OpenReport is often the last thing done. No always though, of course. If it is last, then you have exit code and an error handler, then it will not matter if error handling is suspended.  On Error Resume Next is the first statement in my exit procedures because I don't want to throw an error cleaning up ~

HOWEVER, you said "more logic and code....." after OpenReport. In these cases, after OpenReport, you can put it back to the handler. For instance:
On Error GoTo Proc_Err

Open in new window

Your example, however, shows that error handlers are NOT generically named (ie: Proc_Err as opposed to Form_Load_Error) ... good lesson to do so, from now on anyway -- and for procedures you work on and don't have it that way).

Here is a short EE video on error handling. There are 2 other videos in this series if you wish to know more.

https://www.experts-exchange.com/videos/1478/Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
Personally,  I abhor "on error resume next", because it will allow all errors to slip through your code.  I prefer:

on error goto ProcError

DoCmd.OpenReport sReportname, acPreview   ' or whatever view you want -- and possibly more parameters

ProcExit:
     Exit Sub
ProcError:
     if err.number = 2501 then
          resume ProcExit
     else
         'handle other errors here
         msgbox err.number & vbcrlf & err.description, , "open report
         debug.print "open report", err.number, err.description
     end if
end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> " abhor "on error resume next""

As a general rule, I agree with Dale! I rarely use On Error Resume Next ... before OpenReport is one of the few places I do it a lot, mainly because of No Data (perhaps slightly lazy on my part to trap those in every program that uses OpenReport, especially when it is last)

However, Mick, if the routine is to issue a bunch of OpenReport actions, then I agree with Dale that the most central place to handle that would be to trap the errors and if the err.number is 2501 then, basically ignore it.  Dale's example then exits the procedure that is doing the processing

... which leads me to a question ...

What would the 'more logic and code' be? Why would you need to continue to trap errors? Does this code process multiple report requests?
0
mlcktmguyAuthor Commented:
Yes, based on the information entered by the users a couple more reports could be called in this event.

On Error Resume next will certainly get by the 'No data' situation for the remaining report calls.  My concern is that any other errors would be ignored rather than going to the error handler at the bottom of the routine.

What I may end up doing is this construct:

On Error Resume Next
DoCmd.OpenReport sReportname, acPreview   ' or whatever view you want -- and possibly more parameters
 On Error GoTo Form_Load_Error

Open in new window


I was hoping there was something cleaner.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "What I may end up doing is this construct: ..."

yes, that will work, Mick. However, I wouldn't call this method 'clean, as it skips errors -- which, presumeably are then handled by code behind reports -- where you need more detail about an error, if you need to care at all. Perhaps you meant even more simple? This is about as few statements as it gets ~ without some kind of global error handler such as  vbWatchdog (  http://www.EverythingAccess.com )

correction (hope you don't mind): "could be called in this event" --> could be called in this procedure (not event)

> "On Error Resume next will certainly get by the 'No data' situation for the remaining report calls."

great! Most times, except in procedures that process other reports too, this is short and sufficient.
0
mlcktmguyAuthor Commented:
Thanks for the ideas, went with this construct.  

After Adding this sub
Private Sub executeReport(passedReportName As String)

On Error GoTo Checkerr
DoCmd.OpenReport passedReportName, acViewPreview, , , acDialog
Checkerr:

If Err.Number = 2501 Or Err.Number = 0 Then
    Exit Sub
End If
'
sysErrorHandler Err.Number, Err.Description, "executeReport", "Form_frmRptJerrysCollectionSummary", "Sub"
'
End Sub

Open in new window


and revise all occurrences of

DoCmd.OpenReport newReportName, acViewPreview, , , acDialog

to
executeReport newReportName
0
Dale FyeOwner, Developing Solutions LLCCommented:
Another option, if you needed to continue the process, even if there was no data in that report, then you might use:

ProcError:
     if err.number = 2501 then
          resume Next                                               '<=modified this line
     else
         'handle other errors here
         msgbox err.number & vbcrlf & err.description, , "open report
         debug.print "open report", err.number, err.description
     end if
end sub 

Open in new window

which would return you to the line following the OpenReport if that event was cancelled.
0
mlcktmguyAuthor Commented:
Thanks for your input and ideas.
0
mlcktmguyAuthor Commented:
Dale, I didn't see your last suggestion before I closed the question.  It looked very good and I tried it that way.

However, the line to executing the report still got the 2501 error

I went back to

DoCmd.OpenReport newReportName, acViewPreview, , , acDialog

and put you logic in the procedure error handler.

But the 'DoCmd.OpenReport newReportName, acViewPreview, , , acDialog'

still got the error message.
0
Dale FyeOwner, Developing Solutions LLCCommented:
You need to make sure you have a line:

On Error goto ProcError

at the top of the code module.  Post your current code if it is still raising an error.
0
mlcktmguyAuthor Commented:
Sorry Dale, I rechecked my logic and had a coding issue.  Your final solution works perfectly and should have received all of the points.

My mistake.
0
Dale FyeOwner, Developing Solutions LLCCommented:
If you are using this:
Private Sub executeReport(passedReportName As String)

On Error GoTo Checkerr
DoCmd.OpenReport passedReportName, acViewPreview, , , acDialog
Checkerr:

If Err.Number = 2501 Or Err.Number = 0 Then
    Exit Sub
End If
'
sysErrorHandler Err.Number, Err.Description, "executeReport", "Form_frmRptJerrysCollectionSummary", "Sub"
'
End Sub

Open in new window

Then you need to add a line between the OpenReport and the Error handler:
Private Sub executeReport(passedReportName As String)

On Error GoTo Checkerr
DoCmd.OpenReport passedReportName, acViewPreview, , , acDialog
Exit Sub                        '<= added this line
Checkerr:

If Err.Number = 2501 Or Err.Number = 0 Then
    Exit Sub
End If
'
sysErrorHandler Err.Number, Err.Description, "executeReport", "Form_frmRptJerrysCollectionSummary", "Sub"
'
End Sub

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
don't worry about the points.  Crystal contributed as much here as I did.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.