Error Checking sometimes crashes

[Djblois]

I use this code in my form to check for errors, fix if it can and log if it cannot. It works sometimes and other times it crashes.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    If DLookup("[Logging]", "userTable", "[UserLogin] = '" & strUserLogin & "'") = True Then
    
        On Error GoTo err_handler
        
        If DLookup("[Advanced Logging]", "userTable", "[UserLogin] = '" & strUserLogin & "'") = True Then
    
            LogActions "subfrmAppts-Form_Error"
    
        End If
    
    End If

    Response = acDataErrContinue
    If DataErr = 2113 Or DataErr = 2279 And Screen.ActiveControl.Name = "tbSchTime" Then
        SystemError "You made a mistake when typing in the Scheduled Time.  It needs to be in the hh:mm tt format. " & _
        "Also, make sure that the hour does not exceed 12 and the minutes do not exceed 59."
        'Me.tbSchTime = Null
    ElseIf DataErr = 2113 Or DataErr = 2279 And Screen.ActiveControl.Name = "tbArrTime" Then
        SystemError "You made a mistake when typing in the Arrival Time.  It needs to be in the hh:mm tt format. " & _
        "Also, make sure that the hour does not exceed 12 and the minutes do not exceed 59."
        'Me.tbArrTime.Value = Null
    ElseIf DataErr = 2113 Or DataErr = 2279 And Screen.ActiveControl.Name = "tbDepTime" Then
        SystemError "You made a mistake when typing in the Departure Time.  It needs to be in the hh:mm tt format. " & _
        "Also, make sure that the hour does not exceed 12 and the minutes do not exceed 59."
        'Me.tbDepTime.Value = Null
    Else
        FormError "subfrmAppts-Form_Error", DataErr
    End If
    Exit Sub
    
err_handler:
    
    FormError "subfrmAppts-Form_Error", DataErr
    
End Sub

Sub SystemError(strMessage As String)

    DoCmd.OpenForm "frmGeneralError"
    Forms!frmGeneralError.Form.lblError.Caption = strMessage

End Sub

Sub FormError(strSub As String, lngErrCode As Integer, Optional strControl As String)

    If strControl = "" Then
        LogError strSub, lngErrCode, "Not Logged"
    Else
        LogError strSub, lngErrCode, strControl
    End If
    DoCmd.OpenForm "frmGeneralError"
    Forms!frmGeneralError.Form.lblError.Caption = strSub & " - " & lngErrCode
    End

End Sub

Sub LogError(strSub As String, lngErrCode As Integer, strErrMessage As String, Optional strControl As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    
    Set cnn = CurrentProject.Connection
    
    
    strSQL = "INSERT INTO tblLog (ErrorNum,  ErrMessage, UserName, ErrTime, BuildNum, CurrentSub, CurrentControl, Server) "
    
    strSQL = strSQL & " VALUES ( " & lngErrCode & " "
    strSQL = strSQL & " , '" & strErrMessage & "' "
    strSQL = strSQL & " , '" & strUserLogin & "' "
    strSQL = strSQL & " , #" & Format(Now, "MM/DD/YYYY HH:NN:SS") & "# " ' Make sure now is in MM/DD/YYYY HH:NN:SS format to prevent issues
    strSQL = strSQL & " , '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1")
    strSQL = strSQL & Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00")
    strSQL = strSQL & Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "' "
    strSQL = strSQL & " , '" & strSub & "' "
    strSQL = strSQL & " , '" & strControl & "' "
    strSQL = strSQL & " ," & Chr(34) & GetServerName() & Chr(34) & ")"
    
    'strSQL = strSQL & "VALUES ( " & lngErrCode & ", '" & strUserLogin _
        & "', #" & Now & "#, '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
        Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
        Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "', '" & strSub & "', '" & _
        strControl & Chr(34) & GetServerName() & Chr(34) "' ) "
        
    cnn.Execute strSQL, , adExecuteNoRecords

End Sub

I am on Access 2007 and cannot find why it crashes. But here is a screenshot of the error:

 

[MarkK]

I wouldn't handle the Form_Error() event as a 'catch-all' like that. For one thing, just glancing at your code it's not immediately clear what errors you're intending to catch. If it's data entry in a control called 'tbSchTime' I'd be more likely to handle the BeforeUpdate or AfterUpdate events for that control.
What I suspect is happing for you though is that an error is occurring in a routine called by the event handler and that error goes unhandled. Try putting error handling in each of your SystemError(), FormError(), and LogError() routines and see if you catch anything.
Finally, this kind of thing ...

Sub LogError(strSub As String, lngErrCode As Integer, strErrMessage As String, Optional strControl As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    
    Set cnn = CurrentProject.Connection
    
    
    strSQL = "INSERT INTO tblLog (ErrorNum,  ErrMessage, UserName, ErrTime, BuildNum, CurrentSub, CurrentControl, Server) "
    
    strSQL = strSQL & " VALUES ( " & lngErrCode & " "
    strSQL = strSQL & " , '" & strErrMessage & "' "
    strSQL = strSQL & " , '" & strUserLogin & "' "
    strSQL = strSQL & " , #" & Format(Now, "MM/DD/YYYY HH:NN:SS") & "# " ' Make sure now is in MM/DD/YYYY HH:NN:SS format to prevent issues
    strSQL = strSQL & " , '" & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1")
    strSQL = strSQL & Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00")
    strSQL = strSQL & Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "' "
    strSQL = strSQL & " , '" & strSub & "' "
    strSQL = strSQL & " , '" & strControl & "' "
    strSQL = strSQL & " ," & Chr(34) & GetServerName() & Chr(34) & ")"
        
    cnn.Execute strSQL, , adExecuteNoRecords

End Sub

... can be replaced with parameter queries that enhance reliability and readability ....

Property Get Version() As String
   Version = CurrentDb.OpenRecordset( _
      "SELECT VersionNum & '.' & Format(VersionMinNum, '00') & '.' & Format(BuildNo, '00') " & _
      "FROM tblVersion " & _
      "WHERE VersionID = 1").Fields(0)
End Property

Sub LogError(strSub As String, lngErrCode As Integer, strErrMessage As String, Optional strControl As String)
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.CreateQueryDef(, _
      "INSERT INTO tblLog " & _
         "( ErrorNum,  ErrMessage, UserName, ErrTime, BuildNum, CurrentSub, CurrentControl, Server ) " & _
      "VALUES " & _
         "( p0, p1, p2, p3, p4, p5, p6, p7 )")
   With qdf
      .Parameters(0) = lngErrCode
      .Parameters(1) = strErrMessage
      .Parameters(2) = strUserLogin
      .Parameters(3) = Now()  'format this when you retrieve it
      .Parameters(4) = Version
      .Parameters(5) = strSub
      .Parameters(6) = strControl
      .Parameters(7) = GetServerName()
      .Execute , dbFailOnError
   End With

End Sub

And parameters also handle their own delimiters automatically which is sweet.
Cheers,

 

[Djblois]

I will change the way I log errors - thank you very much.

let me ask you one question - are you saying I should not use the Form_Error() event? I put error checking in almost every routine but I thought the Form_Error() catches some that the others do not. Am I right?

 

[ghudson]

I never use the form error event. I always have error trapping in each event which allows me to customize the way I want to handle the errors per event.

Private Sub cboFindFirstName_AfterUpdate()
On Error GoTo Err_cboFindFirstName_AfterUpdate

    Me.FilterOn = False
    Me![txtAutonumber].SetFocus
    DoCmd.FindRecord Me![cboFindFirstName], acEntire, True, acSearchAll, True, acCurrent, True
    Call btnClear_Click
    Me.txtHidden.SetFocus
    
Exit_cboFindFirstName_AfterUpdate:
    Exit Sub

Err_cboFindFirstName_AfterUpdate:
    If Err.Number = 2142 Then 'The FindRecord action requires a Find What argument
        Call btnClear_Click
        Exit Sub
    ElseIf Err.Number = 2498 Then 'An expression you entered is the wrong data type for one of the arguments
        Call btnClear_Click
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "cboFindFirstName_AfterUpdate()"
        Resume Exit_cboFindFirstName_AfterUpdate
    End If
    
End Sub

 

[MarkK]

Yes, you are right. Form_Error() is commonly the only option for errors in data and errors that occur outside your code. It would cause an error, for instance, if in design view you bound your form to a table that doesn't exist. In this case Access raises an error outside of your code, so you can't trap it or handle it or anything and in this case you'd have to handle the Form_Error() event.