Monday, November 10, 2008

Access 101: How Do I Replace System Error Messages With Custom Messages?

Access provides a number of error messages, but many are confusing to the users. I find it sometimes useful to replace the system error messages with custom error messages. The Null primary key is a case in point.

When you try leave a record on a form without a primary key value, the form will display the following error.

But you might want to replace this with your own error message, identifying the actual field.

At it's simplest, you can use the Form_Error event to trap for this and otherrecord-level errors. Something like this:

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

If DataErr = 3058 Then
MsgBox "You must give Account Number a value."
Response = acDataErrContinue
Else 'msgbox DataErr
Response = acDataErrDisplay
End If

End Sub

3058 is the error number for the null primary key. The "Else" section will let any other error display the normal system error message.
If you want to know the number for another error, just un-comment the msgbox and you'll get the error number. Add any additional errors as an ElseIf.

Other errors you can trap:

Limit to List: 2237
Input Mask: 2279
Required field: 3314
Validation Rule: 2107
Bad Data Value: 2113

1 comment:

Alex Dybenko said...

Hi Roger,
i would suggest a form beforeupdate event, then you can check if a certain field empty, and give error message about this field.