MS Access Error Exception

Basic MS Access Error Exception

When you are coding, the best programming practice is always include error exception in your code. In MS Access, it is very easy to apply error handling technique. The key word  for the error exception is On Error, GoTo and label declaring.

Below are how to do basic MS Access error exception:

This is the template for error exception for Sub procedure.

Sub MS_Access_Error_Exception_Template()
 On Error GoTo Err_Exception
 'Add your code here
 '
Err_Resume:
 Exit Sub

Err_Exception:
 'show your message/keep error in log table
 '
 Resume Err_Resume
End Sub

This is the template for error exception for function procedure..

Function MS_Access_Error_Exception_Template()
 On Error GoTo Err_Exception
 
 'Add your code here
 '
Err_Resume:
 Exit Function

Err_Exception:
 'show your message/keep error in log table
 '
 Resume Err_Resume
End Function

Advance MS Access Error Exception

Here is more advance error handling technique.This technique we will keep the error message log in the table for reference. This one we need to create error log table. We also need a procedure to do the table insertion task for us, so we don't have to write the new code every time. With this log table, we will keep the procedure name, Date, Time and current user who login to the database. This information will tell us where and when the error happens, and who was the one who encountered the error.

1. Create Table Error_Log with the following detail.

2. Create the following procedure.

Sub RaiseError(p_proc_name, Optional p_raise_err = True)
 ' Purpose:   Keep MS error exception into log table.
 '
 ' Parameter: p_proc_name - sub or function that call this procedure
 '
 ' Author: Niramit Soonthawong
 '

 Dim rst As Recordset
 Dim ErrMsg As String
 Dim ErrNum As Integer

 ErrNum = Err.Number
 ErrMsg = Err.Description

 On Error GoTo Err_Exception

 If p_raise_err Then
  MsgBox "Error was found " & vbCrLf & ErrNum & ": " & ErrMsg, vbQuestion, p_proc_name
 End If

 Set rst = CurrentDb.OpenRecordset("Error_Log")
 rst.AddNew

 rst("Log_ID") = Nz(DMax("Log_ID", "Error_Log"), 0) + 1
 rst("Proc_Name") = p_proc_name
 rst("Log_Msg_ID") = ErrNum
 rst("Log_Msg_Desc") = Left(ErrMsg, 255)
 rst("Log_Date_Time") = Now()
 rst("User") = CurrentUser()

 rst.Update
 rst.Close

Err_Resume:
 Exit Sub

Err_Exception:
 MsgBox Err.Description, vbCritical, "RaiseError()"
 Resume Err_Resume
End Sub

3. Add test function

Sub Test_MS_Access_Error_Exception()
 On Error GoTo Err_Exception
 Dim ix As Integer

 ix = "d"

Err_Resume:
 Exit Sub

Err_Exception:
 Call RaiseError("Test")
 Resume Err_Resume
End Sub

4. Run the test to see how it works

You can download the file from ErrorExceptionHandling.rar