databasedev.co.uk - database solutions and downloads for microsoft access

Replacing a Standard Microsoft Access Error Message with a Custom Error Message

When creating Microsoft Access Forms for your database application, there are many times when you may come across error messages when user interaction takes place. Some of the error messages in Microsoft Access may appear completely by surprise or at other times you may intend to have a specific error occur.

Some of the standard error messages in Microsoft Access are not extremely user friendly, therefore it is wise if you can intercept these error messages and replace the standard Microsoft Access error message with a custom error message that will be easily understandable by your database application users.

If you expect that a common error may be encountered in one of your Microsoft Access forms, then displaying an error message that displays the error and advises the user what is required to fix the problem will ensure that the database is much more user friendly.

Here we will deal with how to replace the standard Microsoft Access message with one that you have created, which will respond to an error that the user makes in a form. This example will deal with ensuring that the user enters information in to a field or fields where the information has been specified as required data. We need to ensure that the event occurs when the users leaves the required field blank.

Initially, we need to find out what error event occurs when we leave a required field blank in the form. To do this we need to cause the error to occur to find out the error number so that we can use this later on.

We can use a little piece of code in the Error event of the form to find the error number and we will display this using a msgbox. Below shows the event procedure, for the forms Error event, and shows the required code to display the message when the event occurs.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Debug.Print "DataErr = "; DataErr
End Sub

Now, when we try to enter or edit a record in our form, and we leave a required field blank, we will receive the standard Microsoft Access error message, and we will also be shown the error event number in the Immediate Window in the visual basic editor:

Attempting to leave a blank field in the Access form that has it's properties set as Required

We receive the standard error message for this error:

Microsoft Access standard error message for required data

The Immediate Window in the visual basic editor in Microsoft Access now displays the error number:

The Immediate window in the visual basic editor displaying the error number

Now that we have the error number, we can use this in our code for the form, to respond to the error if it is encountered by a user of the database. In the Error event procedure, we can check to see if an error occurs due to a user leaving a required field blank. If so, we can display our own custom error message, rather than let Microsoft Access display an error message.

We now write the VBA code that will display the custom Microsoft Access error message. We can replace the code used in the earlier Form Error event, with code to show the message that we want the user to see:

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

'If an error occurs because of missing data in a required field
'display our own custom error message

    Const conErrRequiredData = 3314

    If DataErr = conErrRequiredData Then
        MsgBox ("Please ensure that you enter a First Name and Last Name")
        Response = acDataErrContinue
    Else
        'Display a standard error message
        Response = acDataErrDisplay
    End If
End Sub

What this code does is uses the error number that we found out previously, and tests the form against this error. If an error occurs because a required field is left blank it will show our error message (using the If statement). We use the Response argument to ensure that Access skips the standard error message in this case, as we have already displayed our own. The Else part of the statement will allow Access to display a standard error message if the error does not occur due to missing required data.

When we test this by entering or editing a record in the form and trying to save it with missing, required data, Microsoft Access will now show our error message:

The error message is displayed when trying to save a record with missing data.

By using the Error event, we can anticipate when errors or problems may occur and provide the database user with meaningful messages for what is required.

You can create more complex code to respond to error events, that will trap multiple errors and respond to various problems. Using this event, with Microsoft Access VBA, will allow you to create a graphical user interface for your database that is user friendly and not confuse your users with standard Microsoft Access error messages.