Why doesn't my error handler work in my form?
	How can I get the description of an error number?
	I work a lot with external data sources like SQL Server or attached Access tables. How can I get more detailed information when an error occurs?

Why doesn't my error handler work in my form?
Keywords: Error Handler Form
Posted Apr 24, 1997

Some errors occur at the form level. Because of this forms have an Error event which you can use to trap these kinds of errors. Using this event you can trap "Duplicate key" errors among others and choose to display your own message or display Access's default one.

Access sends two parameters to the error event:

	Sub Form_Error (DataErr As Integer, Response As Integer)

The value DataErr will contain the error number for the error that just occurred, and Response allows you to specify how you want Access to handle the error. Response can take the following constants:

	Response = acDataErrContinue;  This will prevent Access from displaying its own message
	Response = acDataErrDisplay; This will cause Access to display its own error message

Here is an example that traps for duplicate key errors:

Private Sub Form_Error (DataErr As Integer, Response As Integer)

	Select Case DataErr
		Case 3022
		     MsgBox "You added a record which duplicates an existing key."
		     Response = acDataErrContinue
		Case Else
		     Response = acDataErrDisplay	
	End Select

End Sub

How can I get the description of an error number?
Keywords: Error Number
Posted Apr 24, 1997

Access provides a useful method, AccessError, which accepts an error number as a parameter. You can use the AccessError method to return the descriptive string associated with a Microsoft Access error or a DAO error without generating the actual error.

This method comes in handy in a Forms' or Reports' Error event. Simply pass it the DataErr value for the error number and it returns the descriptive string as follows:

	MsgBox AccessError(DataErr)

I work a lot with external data sources like SQL Server or attached Access tables. How can I get more detailed information when an error occurs?
Keywords: External Data SQL Server
Posted Apr 24, 1994

Access provides the Err object, which in turn provides you with all the information you need about Visual Basic errors. However, it doesn't give you complete information about Microsoft Access errors or Microsoft Jet database engine errors. Microsoft Access and Data Access Objects (DAO) provide the Errors Collection for just such information.

The Error object and Errors collection are provided by DAO. The Error object represents a DAO error. A single DAO operation may cause several errors, especially if you are performing ODBC operations. Each error that occurs during a particular data access operation has an associated Error object. All the Error objects associated with a particular DAO operation are stored in the Errors collection, the lowest-level error being the first object in the collection and the highest-level error being the last object in the collection.

When a DAO error occurs, the Visual Basic Err object contains the error number for the first object in the Errors collection. To determine whether additional DAO errors have occurred, check the Errors collection. The values of the Number and Description properties of the first Error object in the Errors collection should match the values of the Number and Description properties of the Visual Basic Err object.

DAO and ODBC related errors usually cover the range starting at 3146 to 3299. You can test for this range in your code to determine if you need to loop through the Jet Errors collection. Below is an example of an error handler that checks for ODBC or DAO errors:

	Select Case Err
	    Case 3146 To 3299
	      For Each objErr in DBEngine.Errors
		Msgbox objErr.Description
	      Next
	    Case Else
	      Msgbox Err.Description
       End Select

Back to Top

Back to the FAQ Table of Contents

Don't be so open-minded your brains fall out.