Handling MS Access form errors generically

As a database owner you will not want users to edit data in MS Access tables directly. It’s one of the reasons forms exist; some of the obvious advantages are:

  • Specific fields can be locked or hidden
  • The field order can be changed
  • Event-handling can be implemented

However, errors in forms can occur due to inadequate data handling. If this happens the messages displayed are often not very user-friendly. For a common user following message looks quite intimidating:

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate values and try again.”

This is especially true for forms with a datasheet view since they display multiple records.

To address this issue I’ve created a generic solution to handle data-related form errors. It consists of two components:

1. Generic code

The routine HandleFormError is called from the indivual Form_Error events. Therefore insert following code it in a vba module:

'Constants
Public Const APP = "My application"
Public Const ERR_ONETOMANYCONFLICT = 3101
Private Const ERR_RELATEDRECORDS1 = 3200
Private Const ERR_RELATEDRECORDS2 = 3201
Private Const ERR_REQUIREDDATA = 3314
Private Const ERR_DUPLICATEKEY = 3022
Private Const ERR_DATATYPE = 2113
Private Const ERR_INPUTMASK = 2279
Private Const ERR_NULLKEY = 3058
Private Const ERR_NULLVALUE = 3162
Private Const ERR_ZEROLENGTHSTRING = 3315
Private Const ERR_DATAVALIDATION1 = 2107
Private Const ERR_DATAVALIDATION2 = 3317
Private Const ERR_ITEMNOTINLIST = 2237

' Handle data-related form errors by showing user-friendly messages.
' Assign the appropiate return value to the Response parameter.
Public Sub HandleFormError(oForm As Form, _
        DataErr As Integer, ByRef Response As Integer)

    Const NEXT_MSG = "See next message for details."

    Select Case DataErr

        Case ERR_REQUIREDDATA
            MsgBox "Required value missing!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_RELATEDRECORDS1, ERR_RELATEDRECORDS2
            MsgBox "Relation conflict!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_NULLKEY, ERR_NULLVALUE, ERR_ZEROLENGTHSTRING
            MsgBox "Field can not be empty.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DUPLICATEKEY
            MsgBox "The value is already in use to " & _
                   "create an unique record. Change the value.", _
                    vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATATYPE, ERR_INPUTMASK
            MsgBox "The value has an incorrect data type" & vbCr & _
                   "(f.i. text value in a numeric field).", _
                   vbInformation, APP
            Response = acDataErrContinue

        Case ERR_ITEMNOTINLIST
            MsgBox "Select an item from the list.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATAVALIDATION1, ERR_DATAVALIDATION2
            'Custom validation rules: show corresponding error message
             MsgBox AccessError(DataErr), vbInformation, APP
             Response = acDataErrContinue

        Case Else
            'Display the default error message
            Response = acDataErrDisplay

    End Select

End Sub

Regarding the errors ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2 a work-around was needed due to a MS-bug. Ideally we would like to show the error messages with the field specific information using our own messagebox formatting (just like the errors ERR_DATAVALIDATION1 and ERR_DATAVALIDATION2). However, in that case the original message is not shown correctly. Therefore the default message is preceeded with our own. An alternative could be to comment out the cases regarding ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2, so that only the default error message is shown.

2. Event-code per form

Per individual form implement next event:

' Data-related error is triggered in the form
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    'Check for one-to-many relation conflict
    If DataErr = ERR_ONETOMANYCONFLICT Then
        'User entered data on the "many" side for which
        'there is no matching record on the "one" side.
        '[TODO:] Specify [field] in message
         MsgBox "First select a(n) [field]", vbInformation, APP
        Response = acDataErrContinue
    Else
        'Other data error; go to generic form error handling
        Call HandleFormError(Me, DataErr, Response)
    End If

End Sub

In this way I’ve been able to prevent a lot of cumbersome coding.  I hope it will benefit you as much as it did me. Feel free to use this code and alter the messages to your own liking.