Opening report from switchboard when no data 1

[TrekBiker]

This looks like an old chestnut but my searches for an answer have failed.

A report opened from the Switchboard has this for no data

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data", , "No Data"
    Cancel = True
End Sub

It flags up the warning but then brings up the standard Microsoft Visual Basic 2501 run-time error message with End and Debug options, not good for users.

If I take out Cancel = True a blank report opens, which is marginally better.

How do I just abort opening the report after the 'There is no data' warning?

Being driven from a Switchboard the HandleButtonClick event covers all sort of possible responses - open query, form, report, etc. On stepping through the code it never seems to get to its error handler for the no data case.

 

[dhookom]

You need to trap for this error in the code that opens the report (I assume in your switchboard).

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks Duane but need some more guidance.

The Switchboard code starts like this

Private Function HandleButtonClick(intBtn As Integer)
    Const conCmdGotoSwitchboard = 1
    Const conCmdOpenFormAdd = 2
    Const conCmdOpenFormBrowse = 3
    Const conCmdOpenReport = 4
    Const conCmdCustomizeSwitchboard = 5
    Const conCmdExitApplication = 6
    Const conCmdRunMacro = 7
    Const conCmdRunCode = 8
    Const conCmdOpenPage = 9

    Const conErrDoCmdCancelled = 2501
    
    Dim con As Object
    Dim rs As Object
    Dim stSql As String

On Error GoTo HandleButtonClick_Err

It then continues past this for the report in question

' Open a report.
    Case conCmdOpenReport
    DoCmd.OpenReport rs![Argument], acPreview

... and ends with this

HandleButtonClick_Exit:
On Error Resume Next
    Set rs = Nothing
    Set con = Nothing
    Exit Function

HandleButtonClick_Err:
    If (Err = conErrDoCmdCancelled) Then
        Resume Next
    Else
        MsgBox "There was an error executing the command.", vbCritical
        Resume HandleButtonClick_Exit
    End If
End Function

When I step through the code it never gets to the error handling section but fails on trying to open the report, which has this for No Data

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No records", , "No Data"
    Cancel = True
End Sub

Where should I put the extra trapping you suggest?

 

[dhookom]

I would expect your error handling to manage and hide the error. Have you set a break point and stepped through the code?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Yes, first thing I did.

On stepping through to the point where it wants to open the report,

DoCmd.OpenReport rs![Argument], acPreview

the report's No Data message box appears, and I can step through it

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data", , "No Data"
    Cancel = True
End Sub

The next5 step after End Sub makes this appear

 

[dhookom]

I would check your VBA Tools->Options->General to make sure you haven't set Break on All Errors.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for giving this more time. But no, the Error Trapping is like this

 

[TrekBiker]

Sorry Duane, you're right, it was set to Break on All errors, as in the screen shot. I'll check later if this fixes it.

 

[TrekBiker]

Can confirm it handles the error properly, so many thanks once again.

 

[dhookom]

The "Break on All Errors" has bit me many times. I will set it while testing and forget to change it back.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016