access-vba Catching and handling errors in user code


Example

On error move to labelled code and see if there is a specific error that needs to be handled.

Public Const cErrCodeNotNumber = 2262   ' This value must be a number.
Public Const cErrCodeNumericOverflow = 2263   ' The number is too large.

Private Sub MySub()
    Dim objConn As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim objRS As ADODB.Recordset
    'etc.

    On Error Goto ErrHandler
    [...My code goes here...]

ExitSub:
    'Cleanup
    If objConn.State <> adStateOpen Then objConn.Close 'Closing connection to database, if it is still open
    If Not objRS Is Nothing Then objRS.Close
    objConn = Nothing
    ObjRS = Nothing
    'Do any other cleaning.
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case cErrCodeNotNumber
            MsgBox "The value found is not a number. Execution stopped.", vbCritical
        Case cErrCodeNumericOverflow
            MsgBox "The value found is too big. [instructions how to resolve this]", vbCritical
        Case Else
            MsgBox "Error " & Err.Number & ". " & vbCrLf & Err.Source & "--->" & Err.Description, "Unrecoverable error", vbCritical
    End Select
    
    Goto ExitSub
End Sub