ACC2000: How to Turn Off "Break on All Errors" Option in Code

This article shows you how to turn off the Break on All Errors option in Visual Basic for Applications code to prevent users from interrupting your error handling routines.

Even when your code contains error handling routines, if you share the database with a user who has the Break on All Errors option turned on in Microsoft Access, a run-time error causes your code to halt and open the module containing the error. If you do not want users to handle your run-time errors, you can create procedures that suspend the Break on All Errors option while your code is running.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Sample Procedures to Suspend and Resume the "Break on All Errors" Option

You can use the following sample procedures in your own database to temporarily suspend the Break on All Errors option. Note that these procedures work, whether or not the Break on All Errors option is set. You can include them in your code as added protection against exposing run-time errors:

1. Create a module and type the following line in the Declarations section:

   Dim varOldBOAEOptions As Variant
   					

2. Type the following procedures:

   '-----------------------------------------------------------------
   ' Save the current setting for the "Break on All Errors" option
   ' Turn off the "Break on All Errors" option.
   '-----------------------------------------------------------------
   
   Public Sub SuspendBreaks()
   
      varOldBOAEOptions = GetOption("Error Trapping")
      SetOption "Error Trapping", 2
   
   End Sub
   
   '-----------------------------------------------------------------
   ' Restore the "Break on All Errors" settings that were temporarily
   ' suspended by the SuspendBreaks procedure.
   '-----------------------------------------------------------------
   
   Public Sub ResumeBreaks()
   
      If Not IsEmpty(varOldBOAEOptions) Then _
        SetOption "Error Trapping", varOldBOAEOptions
   
   End Sub
   					

3. Save the module as basErrHandling.
4. When you want to ensure that the Break on All Errors" option does not interrupt the running of your code, call the SuspendBreaks procedure at the beginning of your code, and call the ResumeBreaks procedure at the end. For example:

   Function MyCodeModule()
      SuspendBreaks
      On Error GoTo MyCodeModule_Err
   
      ' Add your code here.
   
   MyCodeModule_Exit:
      ResumeBreaks
      Exit Function
   
   MyCodeModule_Err:
   
      ' Add your error handling routine here.
   
      Resume MyCodeModule_Exit
   End Function
   					

Example Showing Results of Different "Break on All Errors" Settings

The following example demonstrates what happens when code that contains error handling routines runs with the Break On All Errors option turned on.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Start Microsoft Access and create a new blank database called MyError.mdb.
2. Follow steps 1 through 3 in the previous section to create the procedures that suspend and resume the Break on All Errors option.
3. Create a new form not based on any table or query in Design view:

      Form: frmTestErrors
      ----------------------------------------------------
      Caption: Test Error Handling
   
      Text box:
         Name: txtUName
      Text box:
         Name: txtPwd
      Command button:
         Name: cmdOK
         Caption: Without Turning Off Break On All Errors
         OnClick: [Event Procedure]
      Command button:
         Name: cmdOKBreakOff
         Caption: Turning Off Break On All Errors
         OnClick: [Event Procedure]
      Command button:
         Name: cmdCancel
         Caption: Cancel
         OnClick: [Event Procedure]
   					

4. On the View menu, click Code, and then type the following procedures:

   '---------------------------------------------------------------
   ' Test UserName and Password.
   ' Returns:
   '   True if UserName and Password are valid.
   '   False if UserName and Password are invalid.
   ' Displays corresponding error message.
   '-------------------------------------------------------------
   
   Public Function ChkPwd(uid As String, strPwd As String)
      On Error GoTo badPwd
      Dim ws As DAO.Workspace
      Set ws = DAO.DBEngine.CreateWorkspace("TestPWD", uid, strPwd)
      MsgBox "Your password is correct, " & uid
      ChkPwd = True
   
   exitChkPwd:
      Exit Function
   
   badPwd:
      MsgBox "Not the right UserName or Password, " & uid & _
        ", if that is your real name!"
      ChkPwd = False
      Resume exitChkPwd
   End Function
   
   Private Sub cmdOK_Click() ' Without "Break on All Errors" turned off.
      Call ChkPwd(Me![txtUName] & "", Me![txtPwd] & "")
   End Sub
   
   Private Sub cmdOKBreakOff_Click()
      SuspendBreaks  ' Turn off "Break on All Errors."
      Call ChkPwd(Me![txtUName] & "", Me![txtPwd] & "")
      ResumeBreaks   ' Reset "Break on All Errors."
   End Sub
   
   Private Sub cmdCancel_Click()
      DoCmd.Close
   End Sub
   					

5. On the Tools menu, click Options.
6. In the Options dialog box, click the General tab, and then click Break on All Errors. Click OK.
7. On the File menu, click Close and Return to Microsoft Access.
8. Save the frmTestErrors form and close it.
9. Open the frmTestErrors form in Form view.
10. Type User1 in the txtUName box, and type MyPassword in the txtPwd box. Click Without Turning Off Break On All Errors, and note that you receive the following run-time error message, even though your code handles errors:
    Run-time error '3029':
    Not a valid account name or password.
    Click End in response to the error message.
11. Click Turning Off Break On All Errors. Note that your error handling routine produces the following message:
    Not the right UserName or Password, User1, if that is your real name!

For more information about the GetOption and SetOption methods, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type GetOption, SetOption methods in the Office Assistant or the Answer Wizard, and then click Search to view the topic.