Error Handling | Beginning Access 2002 VBA (Programmer to Programmer)

Now that you know how to prevent mistakes, and how to use debugging to find the ones you did not prevent, you really need to know how to handle errors gracefully. We've all seen those odd error messages with obscure numbers , but you should really try to present these in a more user -friendly manner. So the errors that we are going to look at now are the run-time errors that you can foresee.

For example, consider the situation of dealing with external data, perhaps linking an external text file. What happens if the text file is missing, or if it is stored on a network drive and the network has died? You need to be able to inform the user of the problem in a clear and concise way.

The Err Object

When an error is generated in VBA, the details are stored in the Err object. This has several properties and methods that we can use to customize our error handling:

Property	Description
Number	The unique error number
Description	Some descriptive text explaining what the error is
Source	The name of the object or application that generated the error
LastDLLError	A system error code returned from a Dynamic Link Library
HelpContext	The context ID for the help topic in the help file
HelpFile	The name of the help file

There are two methods:

Method	Description
Clear	Clears all of the property settings of the Err object
Raise	Generates a run-time error

The most useful properties are Number and Description , although when generating your own error messages you might well set Source . Let's look at how we handle errors in code.

Visual Basic Errors

You've already seen some examples of error handling code. Remember back in Chapter 6 when we first started looking at the objects in Access, and we were changing the font on forms. We used error handling there to ignore a certain error. If you use the Control Wizard to add controls to forms, some of the code they create also contains some default error handling.

Error handling is a way of catching run-time errors as they happen, and then dealing with them in a way appropriate to the application. The alternative is to let the user see the error message generated by Access, and most likely have them baffled and upset!

Before you can deal with errors in VBA, you need to announce your intention to handle them. For this you use the On Error statement and labels . A label is a marker in the code, a bit like a bookmark, that allows us to jump to certain places when an error occurs. You use On Error in one of three ways:

On Error Goto disables any enabled error handler in the procedure.
On Error Goto Label_Name when an error is generated, causes execution to continue from the label named Label_Name . This is generally used for creating a central place to handle errors, as we will see shortly.
On Error Resume Next when an error is generated, cause execution to continue on the line after the one that generated the error. This can be useful for "in-line" error handling that is, we can turn error handling on with this statement, try something that may cause an error, such as a database update, and then immediately check for the error and deal with it.

A label name follows the same naming rules as a variable, except that it ends with a colon , and it does not need to be unique within a module. The general rule is to either call the label after the name of the procedure and append _Err to the end of it, or use generalized labeling such as ErrHandler and ExitHere . For example:

 Public Sub Foo()   On Error Goto ErrHandler   ' some code goes here ErrHandler:   ' Error handling code goes here End Sub

However, this isn't a correct solution, because the label isn't a terminator of any sort , just a marker. So if no errors occurred, the code would just drop through the label and continue to run the error handling code, which could give some unusual results. There are two ways to get around this:

 Public Sub Foo()   On Error Goto ErrHandler   ' some code goes here   Exit Sub ErrHandler:   ' Error handling code goes here End Sub

There is now an Exit Sub just before the error label, which forces the subroutine to exit immediately, without erroneously running the error code. Another solution, which is better, is:

 Public Sub Foo()   On Error Goto ErrHandler   ' some code goes here ExitHere:   ' clean up code goes here   Exit Sub ErrHandler:   ' Error handling code goes here   Resume ExitHere End Sub

This introduces a new label, ExitHere , which indicates the exit point of the procedure. Now we can use a new statement, Resume ExitHere , in the error handling code to say that, once the error has been handled, resume processing at the label indicated. This means that there is only one exit point in the procedure, which makes it easier if you need to do any tidying up, such as closing recordsets, clearing variables , and so on.

If you have the Office XP Developer edition, you can install the "Visual Basic for Applications Add-Ins" These include a rather neat "Code Commenter and Error Handler Add-In" that will automatically add the above template to every procedure you create (after you have created them), together with comments on parameter info , author name and more. It is template based so that you can easily modify it to meet your exact requirements. We'll look at this in the next section.

There are three ways in which Resume can be used in error handling code:

You can use Resume on its own, which tells VBA to try the statement that caused the error again. This allows you to handle an error, perhaps fixing the problem, and then try again. You should use this option with care, as you don't want to keep generating the error, as this would leave you stuck in a loop.
You can use Resume Next , which is the same as when used in the On Error statement, telling VBA to continue at the line after the line that generated the error. This means that you can handle an error and then continue, as if nothing happened .
You can use Resume Label_Name to jump to a label, where processing will continue.

OK, now that you've seen how the error handler will work, it's time to give it a go.

Try It OutCreating an Error Handler

Create a new procedure, called ErrorHandling :

   Public Sub ErrorHandling()     Dim dblResult As Double     dblResult = 10 / InputBox("Enter a number:")     MsgBox "The result is " & dblResult     End Sub

Run this procedure from the Immediate window, or by pressing F5 when the cursor is in the procedure, enter a number and press OK . This simply divides a number by 10. Not very exciting, but it will allow us to generate some errors.
Run the procedure again and enter 0:
Press the End button, and run the code once more. This time don't enter anything, but just press OK straight away, and we receive a type mismatch error because the concatenation of The result is and dblResult fails when dblResult does not contain anything:

Press End once more. These are the two errors that we are going to trap.

Amend the code so that it looks like this:

 Public Sub ErrorHandling()   On Error GoTo ErrorHandling_Err   Dim dblResult As Double   dblResult = 10 / InputBox("Enter a number:")   MsgBox "The result is " & dblResult   ErrorHandling_Exit:     Exit Sub     ErrorHandling_Err:     MsgBox "Oops: " & Err.Description & "  " & Err.Number     Resume ErrorHandling_Exit   End Sub

Run the procedure again twice, re-creating the two errors first entering to get a division by zero, and then pressing OK without entering anything:

Notice that although the error details are the default ones, we've added a bit of our own code. There's also no option to debug pressing OK just ends the procedure. Let's customize the error handling, checking for these errors.

Modify the code again, changing the error handling procedure to this:

   ErrorHandling_Err:     Select Case Err.Number     Case 13         ' Type mismatch  empty entry     Resume     Case 11         ' Division by 0     dblResult = 0     Resume Next     Case Else     MsgBox "Oops: " & Err.Description & "  " & Err.Number     Resume ErrorHandling_Exit     End Select     Exit Sub

Now try to recreate the errors, and notice what happens. Entering gives a result of , and a blank entry doesn't do anything you're prompted again for a number.

How It Works

Instead of VBA just displaying a default error message, we are checking the error Number :

 Select Case Err.Number

From our previous examples we know the numbers of the two errors we need to trap. The first is for a Type Mismatch :

 Case 13   Resume

What we want to do here is just try again. A type mismatch indicates an incompatibility between variable types. In this case, we are trying to divide a number by an empty string, which is what is returned if nothing is entered in an InputBox . We just want the input box re-displayed; using Resume does this, because it continues on the line that caused the error. This sort of error is one reason why the results of an InputBox statement shouldn't be used directly in expressions.

For a division by 0, we also know the error number:

 Case 11   dblNumber = 0   Resume Next

Here we just set the result of the division to 0 and the resume at the next line, which is the MsgBox statement.

For any other error we want to display the error details:

 Case Else   MsgBox "Oops: " & Err.Description & "  " & Err.Number   Resume ErrorHandling_Exit

This uses a standard message box to display the error details, and then resumes execution at the procedure exit point. Since we don't know what the error is, it is safest just to end the procedure.

If you are handling errors you should always include an Else clause like this, to cater for unexpected situations, and so we are reporting unhandled errors back to the user.

Using the Code Commenter and Error Handler Add-In

If you have bought Office XP Developer edition, then VBA comes with a rather neat Code Commenter and Error Handler Add-In that can add error handling code to all of your procedures automatically as well as put in a template for your comments.

It achieves this through templates that you create that contain a structure you want together with "tokens" that are replaced when you apply the template to your code.

To use this add-in you need to have run setup from the Office XP Developer CD and chosen to install the VBA Productivity Add-Ins . Once installed open the VBA window ( Ctrl+F11 ) in Access and select Add-Ins Add-In Manager . Now double-click on Code Commenter and Error Handler Add-In to load it:

Click OK and then go back to the Add-In menu. You will see that the Code Commenter and Error Handler Add-in is now available.

The add-in can add an error handling template to all your procedures in the current project, procedures in the current module, or just the current procedure (the one that the cursor is in). It is quite simple to use but also very flexible because it is template based. Let's have a go at using it with a simple template of our own.

Try It OutCode Commenter and Error Handler Add-In

Create the following function in a module:

   Public Function MyFunction()     'This function divides a number by a user entered number     Dim dblResult      As Double    ' holds the result of the division     dblResult = 10 / InputBox("Enter a number:")     MsgBox "The result is " & dblResult     End Function

Click inside the function anywhere and Select Add-Ins Code Commenter and Error Handler Add-in .

Click the New button to create a new template and open the template editor, then add the following. The text items prefixed with $$ are tokens that will be replaced later, and can be inserted by selecting them from the Insert menu

   '$$ProcedureName Created by $$Author     $$HeaderComments     $$HeaderVariables     On Error Goto HandleErr     $$ProcedureBody     ExitHere:     Exit $$ProcedureType     $$StartAuto     ' Automatic error handler last updated at $$CurrentDate $$CurrentTime     HandleErr:     Select Case Err.Number     Case Else     MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "$$ProcedureAndModuleName"     End Select     $$EndAuto

Close the Template Editor, and save the file as Simple.eht .
Now select your newly created Simple.eht template in the Template to Apply dialog.
Select Current Procedure from the Insert option buttons .
Enter your name in the Author textbox.
Click Apply .

You're done!

So what has it actually done. Well if look at your procedure now it will look like the following:

   Public Function MyFunction()     'MyFunction Created by Ian Blackburn     'This function divides a number by a user entered number     Dim dblResult      As Double    ' holds the result of the division     On Error GoTo HandleErr     dblResult = 10 / InputBox("Enter a number:")     MsgBox "The result is " & dblResult     ExitHere:     Exit Function     ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.     ' Automatic error handler last updated at 03 December 2002 21:51:17     HandleErr:     Select Case Err.Number     Case Else     MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Chapter 12 Code.MyFunction"     End Select     ' End Error handling block     End Function

We can see that it has taken our template and replaced the tokens (all those words prefixed with $$ ) with something more meaningful, and our procedure now has a nicely formatted error handler added to it, and the formatting has been improved somewhat. In fact there are many tokens that we can add to our templates as follows:

Token	Meaning
$$Author	Author. Replaced with the current author name, that you enter in the dialog when you run the add-in.
$$ Initials	Author initials from the add-in dialog.
$$CurrentDate	Current date, formatted as Windows short date.
$$CurrentTime	Current time, formatted as Windows short time.
$$HeaderComments	Header comments. This matches any number of comment lines or blank lines directly following the Sub , Function , or Property definition.
$$HeaderVariables	Header variables. This matches any number of Dim or Static lines directly following header comments.
$$ParameterList	Parameter list. Provides list of arguments to the current procedure.
$$ProcedureBody	The procedure body.
$$ProcedureName	The name of the procedure it is replaced with the fully qualified procedure name, including the class name if it is a member of a class.
$$ProcedureAndModuleName	The name of the procedure and module in the form ModuleName.ProcedureName . Note that this token is not available from the Insert menu of the editor, but is a valid, useful token.
$$ProcedureType	Type of procedure. Replaced with Sub , Function , or Property as appropriate.
$$ProjectName	Project name.
$$StartAuto	Start auto. Used to flag the start of an inserted error handler.
$$EndAuto	End auto. Used to flag the end of an inserted error handler.
$$StartHeader	Start of header for Code Commenter and Error Handler.
$$EndHeader	End of header for Code Commenter and Error Handler.

Note, however, that for the template to be valid, the file must contain at least the following tokens: $$ProcedureBody plus either $$StartHeader and $$EndHeader , or $$StartAuto and $$EndAuto , and these tokens must start on their own lines. If not then an error will occur when attempting to apply the file.

Form Errors

The above examples have mentioned VBA errors, but in Access there are errors associated with forms. If an error occurs during processing in a form, such as an "Index or primary key cannot contain a Null value" error when adding a new record without a value for a primary key the Form_Error event is generated. This is just a normal event procedure, giving you the details of the error and allowing you to decide whether you want to handle the error, or whether you want Access to handle it:

The first argument, DataErr , is the error number, and can be used in the same way as the Err.Number shown earlier.

The second argument, Response , is an output argument, so you set this in the procedure to tell Access what to do about the error. You can set it to acDataErrContinue and Access will ignore the error and continue with your code, allowing you to use your own error messages instead of the default ones. If you set it to acDataErrDisplay , which is the default, then Access displays its error message. Let's look at an example of this.

Try It OutThe Form_Error Event

In the main database window, select Tables , and open tblIceCreamIngredient .
From the toolbar, select the AutoForm button:

This creates a new form for you.
On the new form, add a new record (use the navigation buttons or New Record from the Insert menu).
Select Apples for the Ingredient ID and leave the Ice Cream ID blank.
Navigate to the previous record, and you'll see the following error:

A pretty scary error message; and certainly not one we would like a user to see. The problem is that both of the ID fields are part of a unique primary key, which means that they both have to be filled in. We left one field blank, which is not allowed. We need to replace the default Access error message with one of our own.
Press OK to get back to the form and then press Escape to clear the record.
Switch the form into Design view and click the Code button on the toolbar to create a code module for this form.

In the form's Error event, add the following code:

   If DataErr = 3058 Then     MsgBox "You must supply values for Ice Cream and Ingredient IDs"     Response = acDataErrContinue     End If

Back in Access, switch the form back into Form view and try the same procedure again selecting only one of the ID values and then navigating away from the record. This time you get a more useful message:
Close the form, saving it as frmErrorHandling .

Notice that when you save the form, the default name you are prompted to save it as is actually tblIceCreamIngredient . This is purely down to the fact that you created the form (using AutoForm ) based upon this table.

How It Works

We know that the error number we need to trap is 3058 . How to we know? Well there are two easy ways. The first is just to do what we did create the same code as above, this time using an error number of 1, and put a breakpoint on the line. Then create the error, and when the breakpoint is triggered just see what the value of DataErr is, and then change the 1 to the correct number. The second method is to look up the error number, either in the help or in the error numbers table, as described in the next section.

Once we know what the error number is we can just output our own message, and set Response to tell Access that we are handling the error and it shouldn't display its own message. It's as simple as that.

You can use this technique for all sorts of errors that might violate the database, such as index errors or data integrity errors.

Access and VBA Errors

If you want to know what the standard Access and VBA error numbers are, it's quite simple. There are two procedures in the MS Office XP Developer help files for this, under Microsoft Access Visual Basic Reference: Error Codes . Both of these procedures are in the Chapter 12 Code in IceCream.mdb . You should note that these procedures use ADO and ADO Extensions, so if you want to use them in your own databases you need to go to the References option in the Tools menu when in the VBE. Select both Microsoft ActiveX Data Objects 2.1 Library and Microsoft ADO Ext. 2.5 for DDL and Security (or a later version) . Running these procedures create tables with the error numbers and descriptions in them.

To download extra documentation on Office XP including lists of error codes go to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xpreskit/html/appa17.asp and download the OrkDocs.exe file.

Data-Access Errors

So far we've talked about VBA errors, but there are plenty of occasions when errors are generated in response to data access, when using databases, recordsets, and so on, and in this case you need a way to access the error details. Remember how we said that the Err object relates to VBA errors? That means there must be another method for handling data-access errors.

Data-Access Objects

When using DAO, there is an Errors collection, which contains Error objects. Each Error object contains a single piece of information about an error. When using DAO methods a single SQL statement can results in multiple errors being generated, and thus multiple Error objects in the Errors collection

This means that there are two places you must check for errors the Err object and the Errors collection. If a DAO error occurs then Err is set to indicate this, as well as the Errors collection being filled. However, the reverse isn't true a VBA run-time error only ever fills Err . You can examine the Err.Source property in VBA to determine if the error is a result of a DAO operation or something else.

Try It OutThe Errors Collection

Create a new procedure called ShowErrors and add the following code:

   Public Sub ShowErrors()     Dim db   As Database     Dim recT As Recordset     Dim errE As Error     On Error GoTo ShowErrors_Err     Set db = CurrentDb()     Set recT = db.OpenRecordset("NonExistantTable")     recT.Close     ShowErrors_Exit:     Exit Sub     ShowErrors_Err:     Debug.Print "Err = " & Err.Number & ": " & Err.Description     Debug.Print     For Each errE In DBEngine.Errors     Debug.Print "Errors: " & errE.Number & ": " & errE.Description     Next     Resume ShowErrors_Exit     End Sub

Run the procedure from the Immediate window. You'll see two copies of the same error are printed:
Now add the following line of code, directly before the OpenRecordset line:
```
   Forms!frmCompany.Caption = "A new caption"   
```
Run the procedure again:

How It Works

The first time the procedure was run, we tried to open a recordset on a table that doesn't exist, so this generated an error. We jumped down to the error routine, which printed out the contents of both the Err object and the Errors collection. Since this was a DAO error, both of these are the same. This shows that when the DAO error is generated, the details are put into the Err object as well as the Errors collection.

The second time around though, the first error is that we try to set the property of a form that isn't open. This is a VBA run-time error 2450. As soon as this is generated, the procedure jumps to the error routine, prints the error details, and exits. That means that the OpenRecordset hasn't been called. So why does the Errors collection still show an error? Remember how we said that a DAO method call clears the Errors collection well we haven't yet done a DAO method call, so the Errors collection still holds the details of the previous errors.

This shows that the Err object and the Errors collection can hold different things, and that they both should be checked. The Err object always contains the details of the error, whether it is a VBA or a DAO error. You should compare the error number held in the Err object with that held in the Errors collection and if the numbers are different, then it is a VBA error. If the numbers are the same, you might want to loop through the Errors collection, in case there are any further DAO errors. In the example above, the DAO error only generated one error, but ODBC errors nearly always generate more. Have a look at this screenshot:

This shows the result of trying to add a record to a table linked with ODBC from a SQL Server database. The details we tried to add were incorrect and violated some SQL Server rules, so an ODBC error was generated. Notice that Err shows that this is an ODBC error, and this error matches the last error in the Errors collection. There are two other error messages that SQL Server has generated, and these have been added to the collection first. This means, that to have a proper error routine you should really check Err against the last Error object in the Errors collection, in a similar way to this:

 Dim errE As Error ' ' '   If Err.Number = DBEngine.Errors(DBEngine.Errors.Count - 1).Number Then     Debug.Print "Data Access Error"     For Each errE In DBEngine.Errors     Debug.Print "Errors: " & errE.Number & ": " & errE.Description     Next     Else     Debug.Print "VBA run-time Error"     Debug.Print "Err = " & Err.Number & ": " & Err.Description     End If

This uses the Count property of the Errors collection to see how many errors there are. We subtract one from it because the Errors collection is zero based, and this gives us the last object in the collection. We can then compare this against the Err object to see whether it is a VBA error or a DAO error.

ActiveX Data Objects

Although we are concentrating on DAO in this book, we need to briefly cover the same sort of things in ADO. Luckily there's not too much difference, as ADO has an Errors collection too, with similar properties, so you can use similar code.

With DAO, the Errors collection belongs to the DBEngine object, but in ADO it belongs to the Connection object. So our code might look something like this:

 Dim db As New ADODB.Connection   Dim recT As New ADODB.Recordset   Dim errE As ADODB.Error   On Error GoTo ShowErrors_Err   db.Open "DSN=pubs", "sa", ""   recT.Open " NonExistantTable ", db, adOpenKeyset, _     adLockOptimistic, adCmdTable   recT.Close ShowErrors_Exit:   Exit Sub ShowErrors_Err:   Debug.Print "Err = " & Err.Number & ": " & Err.Description   Debug.Print   For Each errE In db.Errors     Debug.Print "Errors: " & errE.Number & ": " & errE.Description   Next   Resume ShowErrors_Exit

This example uses an ODBC DSN to connect to a SQL Server database, and the ODBC driver has good error reporting. The native OLEDB Providers seem to return less error information. For multiple errors, ADO also returns the error details in the opposite order to DAO, so you should compare the first member in the collection against Err , rather than the last.

User Defined Errors

Much has been said about Access errors, but you can also define your own errors. This is very useful because it allows you to create a specific error in your procedure if something happens, then handle it in the same way as built-in errors. It also allows you to trigger the default error routines yourself without actually having an error occur.

Using the Raise method of the Err object generates user defined errors. All this does is cause the object to report an error in the normal way. The difference is that you must supply an error number. This can be a VBA standard error number, such as 13 for Type Mismatch, or one of your own that is outside the range VBA uses. The highest number is 65535 , but VBA doesn't itself use anything above 31999 so you have plenty to choose from, however user-defined errors should always be outside the range of error numbers reserved by Access, DAO, and ADO.

Try It OutRaising Errors

Open the ErrorHandling procedure that you were looking at earlier and modify the code to read as follows:

 Public Sub ErrorHandling()   Dim dblResult As Double   Dim VarNumber As Variant     On Error GoTo ErrorHandling_Err     VarNumber = InputBox("Enter a number:")     If Not IsNumeric(VarNumber) Then     Err.Raise 32000     End If     dblResult = 10 / VarNumber   MsgBox "The result is "& str$(dblResult) ErrorHandling_Exit:   Exit Sub ErrorHandling_Err:   Select Case Err.Number   Case 13 ' Type mismatch - empty entry     Resume   Case 11 ' Divide by zero     dblResult = 0     Resume Next   Case Else     MsgBox Err.Description & " - " & Err.Number     Resume ErrorHandling_Exit   End Select End Sub

Now, if the entry is not numeric, the error routine is called with an error number of 32000 . However, to prevent conflicts with Access, make sure that you always use numbers 32000 and above for your code.

If you don't explicitly handle your user-defined errors, but simply use a message box to display the standard error text, you'll get the message Application-defined or object-defined error .

   MsgBox Err.Description & " - " & Err.Number

For example, this would display the message Application-defined or object-defined error 32000 , if used in the previous procedure.

Now modify the error handling routine so that it has an extra selection:

   Case 32000     MsgBox "Must be a number"     Resume ErrorHandling_Exit

Run the procedure and enter a letter. The new error handler processes the error number 32000 in exactly the same way as other errors. So now the function displays our own message.

The Error Stack

Because Access is event-driven, it is constantly running small pieces of code that correspond to events. As each procedure is entered, the error handler is reset, and so any error is treated as the only error. Access does not keep a list of errors, but responds to them as they occur. This is fairly sensible , but you need to understand where Access looks for the error-handling code.

When an error occurs, Access backtracks through its currently active procedures looking for an error-handling routine and executes the first one it finds. If it does not find one, the default error handler routine is called.

For example, let's imagine that you have three procedures, A, B, and C. A calls B, and B in turn calls C. If C generates an error, Access will backtrack through B to A, looking for an error routine. If it does not find one in B or A, it calls the default routine.

Note that this does not affect the flow of control in the program if no errors occur, as procedure C is still currently active. The backward arrows just show what Access does when searching for an error routine.

Now suppose that you need to add a little more meaning to this default error routine so you decide to create your own. Do you put a routine in each of A, B and C, or do you make use of the backtracking feature and just put it in A? Clearly, if you are dealing with similar errors, it makes sense to have one error routine and, because of the backtracking, it is also sensible to put it at the highest level.

Now imagine that you have the following error-handling code in procedure A, and none in the other procedures:

   A_Err:     Select Case Err.Number     Case w ' Dangerous, so quit     Resume A_Exit     Case x ' Safe to carry on to next line     Resume Next     Case y ' Retry again     Resume     Case z ' A default error, let Access 2000 handle it     Err.Raise q     End Select

This seems straightforward, but there is one serious drawback. This can change the program flow. Neither Resume nor Resume Next continues execution in procedure C, as you would think, but at the current line in the procedure that handles the error. This is because the error handler is scoped to procedure A and sees the call to procedure B as a single line of code (which just happens to call another procedure; see diagram below).

So Resume will resume execution at the line that called procedure B this is the current line in procedure A.

Likewise, Resume Next will continue on the line after the call to procedure B. Notice that neither method will return execution to procedure C. Have a look at this diagram to see what happens:

Error handling is initialized in Sub A
Sub A calls Sub B
Sub B calls Sub C
An error occurs in Sub C and the error handler in Sub A catches it
In this case execution is resumed at label A_Err:
Resume Next means execution will resume at the line after the call to Sub B
Resume means the call to Sub B is executed
Err.Raise takes us to the default VBA error handler

This clearly shows the danger of using a single routine for errors. However, it can also be useful the thing to do is to be aware of this and plan accordingly . You can, for example, have a single error routine in A, and then another in C. The one in C would handle errors generated there, allowing continuation in non-fatal circumstances, whereas the one in A could be used to cope with errors from A and B. You could also use Err.Raise in C to force Access to move the error handler up to the next level. For example, look at the following code:

   Sub C()     On Error GoTo c_Err     Dim intOne As Integer     intOne = 123456789     intOne = "wrong"     C_Exit:     Exit Sub     C_Err:     Select Case Err.Number     Case 13     MsgBox "C: Error " & Err.Number & ": " & Err.Description     Case Else     Err.Raise Err.Number, "C", Err.Description     End Select     Resume C_Exit     End Sub

This procedure has an error handler that checks the error number. If it is error 13 (Type Mismatch), then the error is displayed here, but any other error is handled by sending the error back up to the calling procedure. This is done by using the Raise method of the Err object, which in effect fires the error off again. The three arguments are the error number, the source (in this case the procedure C ), and the description. The actual code of the procedure only consists of two lines, both of which generate errors. The first sets an integer variable to a very large value, which will cause an overflow this is not error 13 , and so will raise the error again. The second line generates error 13 , and so would be handled here. In this example, you would have to comment out setting the variable to the large value before you could generate this error though, as they can't both happen. Let's look at a procedure that calls this one:

   Sub B()     Dim intI As Integer     Debug.Print "In B"     Call C     Debug.Print "In B: setting intI to 123456789"     intI = 123456789     End Sub

This procedure just calls procedure C and then generates its own error an overflow. It has no error handling of its own, so Access will search back through the call tree to find an error handler, and in this example, it's in procedure A:

   Sub A()     On Error GoTo A_Err     Debug.Print "In A"     Call B     Debug.Print "In A: 2 / 0 = " & 2 / 0     A_Exit:     Exit Sub     A_Err:     MsgBox "A (" & Err.Source & "): Error " & Err.Number & _     ": " & Err.Description     Resume A_Exit     End Sub

This does have an error handler, which displays the source of the error, the number, and the description. Let's review the procedures:

A calls B, which calls C
C has its own error handler, but this only handles error 13 all others are sent back to B
B does not have its own error handler, so it sends the error back to A
The handler in A displays the error message

This shows you can handle some errors locally, but still have a general error handler to cope with the others. The Source property of the Err object allows you to identify where the error was generated.