MS访问、VBA和错误处理

如果使用“erl”,它将显示错误之前的最后一个标签(例如10、20或30)?

Private Sub mySUB()
On Error GoTo Err_mySUB
10:
    Dim stDocName As String
    Dim stLinkCriteria As String
20:
    stDocName = "MyDoc"
30:
    DoCmd.openform stDocName, acFormDS, , stLinkCriteria    
Exit_mySUB:
    Exit Sub
Err_mySUB:
    MsgBox Err.Number & ": " & Err.Description & " (" & Erl & ")"
    Resume Exit_mySUB
End Sub

我的解决方案如下:

1. 安装 MZ-Tools 是一个非常有趣的vba附加组件。不,他们没有给我钱,反正是免费的。
2. 编写一个标准错误处理程序代码,如此代码(请参见mz工具菜单/选项/错误处理程序):

On Error GoTo {PROCEDURE_NAME}_Error
{PROCEDURE_BODY}
On Error GoTo 0
Exit {PROCEDURE_TYPE}

{PROCEDURE_NAME}_Error:
debug.print "#" & Err.Number, Err.description, "l#" & erl, "{PROCEDURE_NAME}", "{MODULE_NAME}"

然后,通过单击MZ工具菜单中的相应按钮,可以将此标准错误代码自动添加到所有进程和功能中。您会注意到,我们在这里引用了vba(2003版)的一个未记录的值/属性,即“erl”,它代表“错误行”。你明白了!如果您要求mz工具自动为代码行编号,“erl”将给出错误发生的行的编号。您将在即时窗口中对错误进行完整的描述,例如:

#91, Object variable or With block variable not set, l# 30, addNewField, Utilities

当然,一旦您认识到系统的兴趣,您可以考虑一个更复杂的错误处理程序,它不仅在调试窗口中显示数据,而且还将:

1. 在屏幕上显示为消息
2. 在错误日志文件中自动插入包含错误说明的行 或
3. 如果正在使用Access或连接到数据库,请自动将记录添加到tbl_错误表中!

这意味着在用户级别生成的每个错误都可以存储在一个文件或表中,在机器或网络的某个地方。我们在说什么? 构建自动化错误报告系统 使用VBA?

好吧,有几个工具可以满足你的要求 MZ Tools 和 FMS Inc 别忘了。

基本上,它们包括添加:

On Error GoTo ErrorHandler

到每个过程的顶部 最后他们写了:

ErrorHandler:
  Call MyErrorhandler Err.Number, Err.Description, Err.LineNumber

标签,通常是对全局错误处理程序的调用,可以在其中显示和记录自定义错误消息

你总是可以像 Chip Pearson 做。vba可以通过 Microsoft Visual Basic for Applications Extensibility 5.3 Library . 我已经编写了一些类模块,使自己更容易工作。它们可以在 Code Review SE .

我用它来插入 On Error GoTo ErrHandler 语句以及与我的错误处理模式相关的适当标签和常量。我还使用它来同步常量和实际的过程名(如果函数名发生变化的话)。

没有必要购买DJ提到的工具。这是我的免费代码:

Public Sub InsertErrHandling(modName As String)
    Dim Component As Object
    Dim Name As String
    Dim Kind As Long
    Dim FirstLine As Long
    Dim ProcLinesCount As Long
    Dim Declaration As String
    Dim ProcedureType As String
    Dim Index As Long, i As Long
    Dim LastLine As Long
    Dim StartLines As Collection, LastLines As Collection, ProcNames As Collection, ProcedureTypes As Collection
    Dim gotoErr As Boolean

    Kind = 0
    Set StartLines = New Collection
    Set LastLines = New Collection
    Set ProcNames = New Collection
    Set ProcedureTypes = New Collection

    Set Component = Application.VBE.ActiveVBProject.VBComponents(modName)
        With Component.CodeModule

            ' Remove empty lines on the end of the code
            For i = .CountOfLines To 1 Step -1
                If Component.CodeModule.Lines(i, 1) = "" Then
                  Component.CodeModule.DeleteLines i, 1
                Else
                    Exit For
                End If
            Next i

            Index = .CountOfDeclarationLines + 1
            Do While Index < .CountOfLines
                gotoErr = False
                Name = .ProcOfLine(Index, Kind)
                FirstLine = .ProcBodyLine(Name, Kind)
                ProcLinesCount = .ProcCountLines(Name, Kind)
                Declaration = Trim(.Lines(FirstLine, 1))
                LastLine = FirstLine + ProcLinesCount - 2
                If InStr(1, Declaration, "Function ", vbBinaryCompare) > 0 Then
                    ProcedureType = "Function"
                Else
                    ProcedureType = "Sub"
                End If
                Debug.Print Component.Name & "." & Name, "First: " & FirstLine, "Lines:" & ProcLinesCount, "Last: " & LastLine, Declaration
                Debug.Print "Declaration: " & Component.CodeModule.Lines(FirstLine, 1), FirstLine
                Debug.Print "Closing Proc: " & Component.CodeModule.Lines(LastLine, 1), LastLine

                ' do not insert error handling if there is one already:
                For i = FirstLine To LastLine Step 1
                    If Component.CodeModule.Lines(i, 1) Like "*On Error*" Then
                        gotoErr = True
                        Exit For
                    End If
                Next i
                If Not gotoErr Then
                    StartLines.Add FirstLine
                    LastLines.Add LastLine
                    ProcNames.Add Name
                    ProcedureTypes.Add ProcedureType
                End If

                Index = FirstLine + ProcLinesCount + 1
            Loop

            For i = LastLines.Count To 1 Step -1
                If Not (Component.CodeModule.Lines(StartLines.Item(i) + 1, 1) Like "*On Error GoTo *") Then
                    Component.CodeModule.InsertLines LastLines.Item(i), "ExitProc_:"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 1, "    Exit " & ProcedureTypes.Item(i)
                    Component.CodeModule.InsertLines LastLines.Item(i) + 2, "ErrHandler_:"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 3, "    Call LogError(Err, Me.Name, """ & ProcNames.Item(i) & """)"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 4, "    Resume ExitProc_"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 5, "    Resume ' use for debugging"

                    Component.CodeModule.InsertLines StartLines.Item(i) + 1, "    On Error GoTo ErrHandler_"
                End If
            Next i
        End With
End Sub

将其放入模块中,并在每次将新函数或Sub添加到类似于此的窗体或模块时从即时窗口调用它(Form1是窗体的名称):

MyModule.InsertErrHandling "Form_Form1"

它将改变您在Form1中的ODE:

Private Function CloseIt()
    DoCmd.Close acForm, Me.Name
End Function

对此:

Private Function CloseIt()
    On Error GoTo ErrHandler_
        DoCmd.Close acForm, Me.Name
ExitProc_:
Exit Function
ErrHandler_:
    Call LogError(Err, Me.Name, "CloseIt")
    Resume ExitProc_
    Resume ' use for debugging
End Function

现在在模块中创建一个子项,该子项将显示错误对话框,您可以在其中添加插入错误到文本文件或数据库:

Public Sub LogError(ByVal objError As ErrObject, moduleName As String, Optional procName As String = "")
    On Error GoTo ErrHandler_
    Dim sql As String
    MsgBox "Error " & Err.Number & " Module " & moduleName & Switch(procName <> "", " in " & procName) & vbCrLf & " (" & Err.Description & ") ", vbCritical
Exit_:
    Exit Sub
ErrHandler_:
    MsgBox "Error in LogError procedure " & Err.Number & ", " & Err.Description
    Resume Exit_
    Resume ' use for debugging
End Sub

如果进程中已经有“on error”语句,则此代码不会进入错误处理。