Provided by Allen Browne, allen@allenbrowne.com
(Note: for Access 95 or later, use the VBA error handler
Every function or sub should contain error handling. Without it, a user may be left viewing the faulty code in a full version of Access, while a run-time version just crashes.
The simplest approach is to display the Access error message and quit the procedure. Each procedure, then, will have this format (without the line numbers):
1 Sub|Function SomeName() 2 On Error GoTo Err_SomeName ' Initialize error handling. 3 ' Code to do something here. 4 Exit_SomeName: ' Label to resume after error. 5 Exit Sub|Function ' Exit before error handler. 6 Err_SomeName: ' Label to jump to on error. 7 MsgBox Err & " " & Error$ ' Place error handling here. 8 Resume Exit_SomeName ' Pick up again and quit. 9 End Sub|Function
The labels (lines 4 and 6) must be in the current procedure, and must be unique.
For a task where several things could go wrong, replace lines 7~8 with more detail:
Select Case Err Case 9999 ' Whatever number you anticipate. Resume Next ' Use this to just ignore the line. Resume Exit_SomeName ' Use this to give up on the proc. Case Else ' Any unexpected error. Call LogError(Err, Error$, "SomeName()") Resume Exit_SomeName End Select
The Case Else in this example calls a custom procedure to write the error details to a table. This allows you to review the details after the error has been cleared. The table might be named "tLogError" and consist of:
Field Name Data Type Description ErrorLogID AutoNumber Primary Key ErrNumber Number Integer. The Access-generated error number. ErrDescription Text Size=255. The Access-generated error message. ErrDate Date/Time System Date and Time of error. Default: =Now() CallingProc Text Name of procedure that called LogError() UserName Text Name of User.
Below is the procedure for writing to this table. If you wish to go further, you could extend it to count errors recorded recently and suppress the display of the same message repeatedly, or give up retrying locking errors.
Sub LogError (ByVal iErrNumber As Integer, ByVal strErrDescription As String, strCallingProc As String) On Error GoTo Err_LogError ' Purpose: Generic error handler. ' Logs errors to table "tLogError". ' Arguments: iErrNumber - value of Err ' strErrDescription - value of Error$ ' strCallingProc - name of sub|function that generated the error. ' Author: Allen Browne, allen@allenbrowne.com, June 1997. Dim NL As String * 2 ' New Line Dim sMsg As String ' String for display in MsgBox Dim db As Database ' Current database Dim rst As Recordset ' The tLogError table sMsg = "Error " & iErrNumber & ": " & strErrDescription MsgBox sMsg, 32, strCallingProc Set db = CurrentDB() Set rst = db.OpenRecordset("tLogError") rst.AddNew rst![ErrNumber] = iErrNumber rst![ErrDescription] = Left$(strErrDescription, 255) rst![ErrDate] = Now rst![CallingProc] = strCallingProc rst![UserName] = CurrentUser() rst.Update rst.Close Exit_LogError: Exit Sub Err_LogError: sMsg = "An unexpected situation arose in your program." & NL sMsg = sMsg & "Please write down the following details:" & NL & NL sMsg = sMsg & "Calling Proc: " & strCallingProc & NL sMsg = sMsg & "Error Number " & iErrNumber & NL & strErrDescription & NL & NL sMsg = sMsg & "Unable to record because Error " & Err & NL & Error$ MsgBox sMsg, 16, "LogError()" Resume Exit_LogError End Sub
Home | Index of tips | Top |