ABC Blocks


Database and Website Design, Development, and Deployment--All in the Service of Your Mission

Here is some code that automatically builds procedures, along with copyright and error handling. This code was written to avoid typing exactly the same thing every time you create a new procedure. This is immensely helpful--because it's all standardized. We use a select structure in the error trapping portion of each procedure, as shown below. In this case, there are no errors expected, so there's only a "case else" case. If there were known errors that sometimes came up there would be more cases in the select structure.

In any case, here's a typical (though tiny) procedure from one of our databases, illustrating a call to the error trapping routine:

Public Sub CloseFrmAccountList(fFAL As Form_frmAccountList) On Error GoTo Error fFAL!cmdClose.Tag = "AllowClose" DoCmd.Close acForm, "frmAccountList" Forms!frmswitchboard.Visible = True Exit Sub Error: Select Case Err.Number Case Else Call ErrorTrap(Err.Number, Err.Description, "CloseFrmAccountList") End Select End Sub

Here's the ErrorTrap routine in that database. This was written a few years ago and hasn't been modified since then. At some point we'll make this code fully generic, so the directories it uses are pulled from a table or a control on an always-open form.

It's pretty simple. First it saves the error information to a text file and then it puts up a message box to the user. If it fails to find the proper file on the server it writes to a file on the C:\ drive. If there's any other error in this routine, it tries again, and on the second error it just punts and tells the user to write everything down and go the database administrator.

Some people do a similar thing but write the error information to a table in their database. The reason we write the error log to a file instead is that if something serious goes wrong and you can't get into the back end of your database, you wouldn't be able to see the error table! You don't really need much functionality in this error log, and you can just link to the text file from any database, so there's no need to be opening another mdb every time there's an error.

Public Function ErrorTrap(iErrNum As Integer, sErrDesc As String, sProcName As String, _ Optional sFormName As String, Optional lngID As Long, Optional sSql As String) On Error GoTo ErrorInErrorTrap Dim sMsg As String Dim sFile As String Dim iCount As Integer Dim dNow As Date DoCmd.SetWarnings True Application.Echo True DoCmd.Close acForm, "frmMessage" dNow = Now 'add a record to the Error file sFile = FreeFile Open sPathToBackEnd & "BRError.txt" For Append Lock Write As #sFile Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql) Close #sFile 'show the user a message sMsg = iErrNum & ": " & sErrDesc & vbCrLf & vbCrLf & sProcName & vbCrLf & Nz(sFormName) _ & Nz(lngID) & vbCrLf & Nz(sSql) MsgBox sMsg, vbExclamation, "Error in " & sProcName Call DoCmd.Hourglass(False) Exit Function ErrorInErrorTrap: If Err.Number = 68 Then 'device unavail--probably b/c can't write to the remote Error file sFile = FreeFile Open "C:\BnkRecSW\Prdction\Tools\BRError.txt" For Append Lock Write As #sFile Write #sFile, dNow, sUser, sProcName, iErrNum, sErrDesc, sFormName, lngID, Nz(sSql) Close #sFile End If If iCount < 2 Then DoEvents iCount = iCount + 1 Resume Else MsgBox "There has been an Error in the Bank Reconciliation Application." _ & " Please write down the following information and contact the Technical Support" _ & " in charge of this application" _ & vbCrLf & iErrNum & vbCrLf & sErrDesc & vbCrLf & sProcName & vbCrLf _ & sUser & vbCrLf & dNow & vbCrLf & sFormName & vbCrLf & lngID & vbCrLf & Nz(sSql), _ vbCritical, "Error" End If Call DoCmd.Hourglass(False) End Function