The 'OpenReport' Action Was Canceled

There are several things that annoy me about the default behavior of DoCmd.OpenReport.  To address these annoyances, I built a replacement function that I named PreviewReport().

In this series of articles, I will take you through the evolution of this function as I address each of my frustrations.

Part 3: Unwarranted Errors

Reports in Access have a NoData event that triggers when there is, you guessed it, no data to display:

The NoData event occurs after Microsoft Access formats a report for printing that has no data (the report is bound to an empty recordset), but before the report is printed. You can use this event to cancel printing of a blank report.

This allows you to show your users a friendly error message, like this:

You can then cancel the opening of the report, to avoid the confusion that would result from seeing something like this:

Sounds great, what's the problem?

Let's say you have the following code behind your report:

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

When you run this report with criteria that filters out all the results, you'll get two messages.  

First, the message we want:

Followed immediately by this rather unhelpful message:

What to do with error 2501?

I swallow that sucker whole.  

That's right, I ignore every instance of runtime error '2501.' And I mean every...single...instance.  In fact, here's an excerpt from my vbWatchdog global error handler:

Select Case ErrEx.Number
Case 2501  'Ignore OpenForm/Report action was canceled error messages
    If ErrEx.State <> OnErrorGotoLabel Then
        ErrEx.State = OnErrorResumeNext
    End If
'...other exceptions...
End Select

In all my years of development, globally swallowing this error has never bit me (knock on wood), but it has simplified my development immensely.

Updating PreviewReport()

Even though I'm swallowing the error, I still like to know whether the report actually opened for the user or not.  So, I updated my PreviewReport() function to return a Boolean that indicates whether the report was able to open or not:

'NOTE: This code assumes a vbWatchdog global error handler
'      with an exception for error 2501 (such as in the sample above)
Function PreviewReport(RptName As String, _
                       Optional Where As String = "") As Boolean
    If ReportIsOpen(RptName) Then DoCmd.Close acReport, RptName
    DoCmd.OpenReport RptName, acViewPreview, , Where
    If Err.Number = 2501 Then  'The OpenReport action was canceled
        PreviewReport = False
    Else
        PreviewReport = True
    End If
End Function

'source: https://nolongerset.com/why-so-lazy-access/
Function ReportIsOpen(RptName As String) As Boolean
    ReportIsOpen = (SysCmd(acSysCmdGetObjectState, acReport, RptName) <> 0)
End Function

As the comments above make clear, the previous code requires a vbWatchdog global error handler with the appropriate exception for error 2501.

If you are not using vbWatchdog, you can accomplish the same thing using a more traditional VBA approach to error handling:

Function PreviewReport(RptName As String, _
                       Optional Where As String = "") As Boolean
    On Error GoTo Err_PreviewReport

    If ReportIsOpen(RptName) Then DoCmd.Close acReport, RptName
    PreviewReport = True
    DoCmd.OpenReport RptName, acViewPreview, , Where


Exit_PreviewReport:
    Exit Function
Err_PreviewReport:
    Select Case Err.Number
    Case 2501   'The OpenReport action was canceled.
        PreviewReport = False
    Case Else
        MsgBox Err.Description, vbExclamation, "Error: " & Err.Number
    End Select
    Resume Exit_PreviewReport
End Function

Recap

Three down, two to go:

1. Auto-Print: Opening a report sends it directly to the printer with no on-screen preview
2. Wrong Data: If you call OpenReport on a report that's already open, Access doesn't apply the new criteria to the report
3. Unwarranted Errors: When you cancel report opening in the OnNoData event, Access raises an error
4. Cascading Maximize: If you open a report from a windowed form, maximize the report, then close it, the windowed form from which you opened the report gets maximized
5. Missing OpenArgs: Prior to Access 2007, OpenArgs was not supported for the OpenReport method

Image by Gerd Altmann from Pixabay