Run-Time error if save cancelled, how to overcome?

[CarysW]

Hello

I have a custom dialog box which has combo boxes to choose criteria for a query and then exports the query to Excel. The problem comes if the user gets to the 'Output To' save box but then decides to cancel I/they get Run-Time Error '2501': The OutputTo Action was cancelled which then stops the procedure and efefctively for the end user breaks the database and scares the hell out of them! Is there some code I can add that will either stop them cancelling or alternativel, and preferably for VBA to just ignore it and cancel the whole action, closing the dialog box(as it is supposed to once saved) Code shown below:

Private Sub cmdOKRepHQAll_Click()
If IsNull(cboREPAll) Then
      MsgBox "You must choose a Rep." _
         & vbCrLf & "Please try again.", vbExclamation, _
         "More information required."
      Exit Sub
      
 End If
 
 ' Open MsgBox to tell user that a save box will open next
    MsgBox "Please Rename and Save The Export", vbQuestion, "Export To Excel"
    ' Exports Query to Excel
    DoCmd.OutputTo acOutputQuery, "qryByRep", "ExcelWorkbook(*.xlsx)", "", True, "", 0, acExportQualityPrint
    ' Closes Form
    DoCmd.Close acForm, "frmChAllbyRep"
End Sub

 

[CarysW]

Hello

I have a custom dialog box which has combo boxes to choose criteria for a query and then exports the query to Excel. The problem comes if the user gets to the 'Output To' save box but then decides to cancel I/they get Run-Time Error '2501': The OutputTo Action was cancelled which then stops the procedure and effectively for the end user breaks the database and scares the hell out of them! Is there some code I can add that will either stop them cancelling or alternatively, and preferably for VBA to just ignore it and cancel the whole action, closing the dialog box(as it is supposed to once saved) Code shown below:

(Be gentle I'm still learning)

Private Sub cmdOKRepHQAll_Click()
If IsNull(cboREPAll) Then
      MsgBox "You must choose a Rep." _
         & vbCrLf & "Please try again.", vbExclamation, _
         "More information required."
      Exit Sub
      
 End If
 
 ' Open MsgBox to tell user that a save box will open next
    MsgBox "Please Rename and Save The Export", vbQuestion, "Export To Excel"
    ' Exports Query to Excel
    DoCmd.OutputTo acOutputQuery, "qryByRep", "ExcelWorkbook(*.xlsx)", "", True, "", 0, acExportQualityPrint
    ' Closes Form
    DoCmd.Close acForm, "frmChAllbyRep"
End Sub

 

[PeterF]

If you ad a error handler you can trap the error.
If all errors can be ignored just adding "On Error Resume Next" at the start of your code will ignore all errors.

 

[boblarson]

I wouldn't go with Resume Next but I would put an error handler in and do something like:

At the top of the procedure

On Error Goto Err_Handler

at the bottom of the procedure:

Err_Handler_Exit:
   Exit Sub

Err_Handler:
   If Err.Number <> 2501 Then
      MsgBox Err.Description, vbExclamation, "Error Number: " & Err.Number
   End If
   Resume Exit_Err_Handler_Exit

 

[jjturner]

Looks like a classic case for putting in some error handling (which I could use some brushing-up on myself):

Private Sub cmdOKRepHQAll_Click()

[B][COLOR="Navy"]On Error GoTo cmdOK_Error[/COLOR][/B]

If IsNull(cboREPAll) Then
      MsgBox "You must choose a Rep." _
         & vbCrLf & "Please try again.", vbExclamation, _
         "More information required."
      Exit Sub
      
 End If
 
 ' Open MsgBox to tell user that a save box will open next
    MsgBox "Please Rename and Save The Export", vbQuestion, "Export To Excel"
    ' Exports Query to Excel
    DoCmd.OutputTo acOutputQuery, "qryByRep", "ExcelWorkbook(*.xlsx)", _
        & "", True, "", 0, acExportQualityPrint
    ' Closes Form
    DoCmd.Close acForm, "frmChAllbyRep"

    [B][COLOR="navy"]Exit Sub[/COLOR][/B]

[B][COLOR="navy"]cmdOK_Error:[/COLOR][/B]
    If Err.Number = 2501 Then
        Resume Next
    Else
        Msgbox "Error " & Err.Number & ": " & Err.Description
        Resume Next
    End If

End Sub

HTH,
John

 

[CarysW]

Thankyou Very Much

 

[CarysW]

Thanks both, not sure what happened but I had 2 posts! Have put error handing in and is now perfect. Thaks again.