This information is also available as a PDF download.
When capturing and handling errors, you probably think of the Err object, which stores information about the current run-time error. If a form or report encounters an error, you'll find the information you need in an event — the object's Error event to be exact. By default, Access handles action errors without any help from you. On occasion, you might find you want a little more control. When that's the case, take matters into your own hands by usurping the object's Error event.
About the Error event
Both forms and reports sometimes generate action errors, such as trying to delete a record protected by referential integrity or trying to add a new record without filling in required fields. When this happens, Access displays a predefined warning message that's usually adequate. But if you prefer, you can take charge of the event and force it to do the following:
- Inhibit the internal message
- Display a custom message
- Redirect the application's flow
Using the Error eventA common form error occurs when you try to save a new record without filling in all required fields (required at the table level). Figure A shows how Access handles this error in a simple form based on the Customers table in Northwind (the sample database that comes with Access). Figure A
In this case, the default message is sufficient. In fact, most are. So you probably won't use this event to change the message. Generally, you'll use it to redirect the flow, changing what happens after the Error event occurs.
The Error event uses the following syntax:
Private Sub object_Error(DataErr As Integer, Response As Integer)
where object is the name of the Form or Report object, DataErr is the error code that identifies the current action error, and Response determines whether the event displays an error message. The following constants are appropriate for Response:
- acDataErrContinue: Doesn't display the default error message
- acDataErrDisplay: Displays the default error message (Response's default value)
Adding a custom error message
A Form object can generate a number of errors, so don't totally take over the Error event with your code. Instead, determine the error or errors you want to control and let Access take care of the rest. The easiest way to determine a Form error's number is to display DataErr's value. During the develop and testing stages, you can use a MsgBox statement to learn error numbers, as follows:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
End SubFigure B shows the result of this event for our examples error, number 3314. Access will also display its internal message (see Figure A) after you clear the message box. Figure B
Once you know the expected error number, you can use a Select Case statement, or some other method, similar to the one in Listing A, to determine what happens when that specific error occurs. Listing A
Private Sub Form_Error(DataErr As Integer, Response As Integer)The Select Case uses DataErr to determine what happens when the form generates error 3314. In this case, the Case action solicits a company name for the user using an Input Box. Once the user enters the company name, as shown in Figure C, and clicks OK, the Case action will enter the user's response in the appropriate form control. Figure D shows the resulting form. The Resume Next statement inhibits the subsequent error encountered if the user clicks Cancel or OK without entering an appropriate value. Figure C
'Solicit Company Name if user fails to enter one.
Dim strInputCompanyName As String
Select Case DataErr
strInputCompanyName = InputBox( _
"Please enter the company name for this new customer:", _
"Enter Company Name")
'Avoid Null value error.
On Error Resume Next
Me!CompanyName = strInputCompanyName
MsgBox "The form error, " & DataErr & " has occurred.", _
'Inhibit internal message.
Response = acDataErrContinueEnd Sub
When the error isn't 3314, the Case Else action displays the message box shown in Figure E. It's important to remember the Case Else statement, although you can use any message text you like. Without the Case Else, the Error event captures only the event(s) you specify using Select Case. The last statement in the event inhibits the default message (see Figure A). Figure E
The Select Case statement seems like an unlikely choice for only one error. In truth, it allows for the flexibility you'll need to add more errors as you discover them. If you want to capture only one specific error, Select Case probably is overkill.
If you're working with more than one version of Access, consider assigning error values to constants as follows:
Const conRequiredValueError = 3314
Select Case DataErr
That way, you can more easily update the code with the most appropriate error value.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at email@example.com.