错误处理在Err.Num 3021上无法正常工作

Question

首先，让我为意大利面代码道歉，因为我不确定处理这样的错误的最佳方法。

我遇到的情况是，我们的数据在一年中在两个不同的数据中心之间移动，所以我需要根据错误号-2147467259测试连接，如果没有与该服务器的连接，这发生在代码顶部的cfRS.Open CIFstr, adoConn上。

我尝试过使用IF语句查看上面的错误编号，但是这些错误都不是成功的，下面是我编写的精彩的意大利面代码。

当我从cfRS.MoveFirst中删除Branson:值时，代码的Branson:部分中的错误号3021发生在Sheet1.Range("B103")上，并且我不知道为什么不使用On Error GoTo Err3021来处理错误。代码顶部的ErrHandler:工作得很好。任何帮助都是非常感谢的。

代码：

Option Explicit
Sub CIFIncoming()
    Dim adoConn As New ADODB.Connection
    Dim cfRS As New ADODB.Recordset
    Dim Name As String, Address1 As String, Address2 As String
    Dim City As String, State As String, Zip As String
    Dim HomePhone As String, CellPhone As String
    Dim BSA As String
    Dim strConn As String
    Dim CIFstr As String, CIF As String

    On Error GoTo ErrHandler

'\\\\BEGIN DATABASE INFORMATION GRAB////
'   1.  Sets the Connection String to the Data Base
'   2.  Opens the connection to the database
'   3.  Sets the SQL String to get the fields from the Data Base
'   4.  Defines the CIF Number to use in the SQL String
'   5.  Opens the Recordset
'   6.  Moves the cursor in the DataBase to first position



    strConn = [REDACTED]

    adoConn.Open strConn

    CIF = UCase(Sheet1.Range("B103").Text)

    CIFstr = "SELECT " & _
             "cfna1, cfna2, cfna3, cfcity, cfstat, LEFT(cfzip, 5), cfhpho, cfcel1, cfudsc6 " & _
             "FROM cncttp08.jhadat842.cfmast cfmast " & _
             "WHERE cfcif# = '" & CIF & "'"


    cfRS.Open CIFstr, adoConn

    cfRS.MoveFirst

'\\\\END DATABASE INFORMATION GRAB////

'\\\\BEGIN WORKSHEET INFORMATION PLACEMENT////
'   1.  Assigns each field from the Database to a variable
'   2.  Moves data from Database to specific cells

    Name = cfRS.Fields(0)        'cfna1
    Address1 = cfRS(1)           'cfna2
    Address2 = cfRS(2)           'cfna3
    City = Trim(cfRS.Fields(3))  'cfcity
    State = Trim(cfRS.Fields(4)) 'cfstat
    Zip = cfRS.Fields(5)         'cfzip
    HomePhone = cfRS.Fields(6)   'cfhpho
    CellPhone = cfRS.Fields(7)   'cfcel1
    BSA = cfRS.Fields(8)         'cfudsc6

    With Sheet1
        .Range("B104") = Name
        .Range("B105") = Address1
        .Range("B106") = Address2
        .Range("B107") = City & ", " & State & " " & Zip
    End With

'\\\\END WORKSHEET INFORMATION PLACEMENT////

'\\\\BEGIN FINAL DATABASE OPERATIONS////
'   1.  Closes connection to Database
'   2.  Sets the Recordset from the Database to Nothing
'   3.  Exits sub when there are no errors

    cfRS.Close
    Set cfRS = Nothing
    Exit Sub
'\\\\END FINAL DATABASE OPERATIONS

ErrHandler:
'THIS HANDLES ERROR 3021
    If Err.Number = 3021 Then
        With Sheet1
            .Range("B104") = vbNullString
            .Range("B105") = vbNullString
            .Range("B106") = vbNullString
            .Range("B107") = ""
        End With
    End If

    If Err.Number = -2147467259 Then GoTo Branson


Branson:
    On Error GoTo Err3021
    CIF = UCase(Sheet1.Range("B103").Text)

    CIFstr = "SELECT " & _
             "cfna1, cfna2, cfna3, cfcity, cfstat, LEFT(cfzip, 5), cfhpho, cfcel1, cfudsc6 " & _
             "FROM bhschlp8.jhadat842.cfmast cfmast " & _
             "WHERE cfcif# = '" & CIF & "'"

    cfRS.Open CIFstr, adoConn

    cfRS.MoveFirst  

'\\\\END DATABASE INFORMATION GRAB////

'\\\\BEGIN WORKSHEET INFORMATION PLACEMENT////
'   1.  Assigns each field from the Database to a variable
'   2.  Moves data from Database to specific cells

    Name = cfRS.Fields(0)        'cfna1
    Address1 = cfRS(1)           'cfna2
    Address2 = cfRS(2)           'cfna3
    City = Trim(cfRS.Fields(3))  'cfcity
    State = Trim(cfRS.Fields(4)) 'cfstat
    Zip = cfRS.Fields(5)         'cfzip
    HomePhone = cfRS.Fields(6)   'cfhpho
    CellPhone = cfRS.Fields(7)   'cfcel1
    BSA = cfRS.Fields(8)         'cfudsc6

    With Sheet1
        .Range("B104") = Name
        .Range("B105") = Address1
        .Range("B106") = Address2
        .Range("B107") = City & ", " & State & " " & Zip
    End With

'\\\\END WORKSHEET INFORMATION PLACEMENT////

'\\\\BEGIN FINAL DATABASE OPERATIONS////
'   1.  Closes connection to Database
'   2.  Sets the Recordset from the Database to Nothing
'   3.  Exits sub when there are no errors

    cfRS.Close
    Set cfRS = Nothing
    Exit Sub
'\\\\END FINAL DATABASE OPERATIONS

Err3021:
'THIS HANDLES ERROR 3021
    If Err.Number = 3021 Then
        With Sheet1
            .Range("B104") = vbNullString
            .Range("B105") = vbNullString
            .Range("B106") = vbNullString
            .Range("B107") = ""
        End With
    End If

End Sub

Answer 1

在你做cfRS.MoveFirst之前，先做If not(cfRS.bof and cfRS.eof) then。

当查询的Where子句是WHERE cfcif# = ''时，则没有行。当您在没有记录时尝试执行cfRS.MoveFirst时，您将得到您的错误3012。因此，在尝试导航记录集并对其进行操作之前，您需要确保有记录。