运行时错误91对象变量或未用类设置的块变量

Question

我第一次尝试从SQL服务器获取记录集并将数据从记录集中传递到类中。这将是一个更大项目的一部分，通过将记录集存储到一个字典中，我可以根据用户输入的搜索条件调用该字典，这肯定也会让我陷入困境。我使用Answer from this question作为入门指南，但由于我现在正在学习如何使用类模块；我不知道为什么要获得运行时错误91(在下面的代码中标识)。我注意到，似乎没有任何东西传递给我在clsCustInfo中指定的变量。谢谢你的帮助。

请注意：On Error Resume Next用于函数测试以查看数据存储在哪个服务器上时发生的错误。

下面的是我的类模块中的内容.

'CustomerInfo.cls
Private CustomerId As String
Private cName As String
Private cAddress1 As String
Private cAddress2 As String
Private cCity As String
Private cState As String
Private cZip As String * 5
Private cDoB As String
Private TableName As String
Private ErrNumber As Long
Public Property Get custID() As String
    custID = CustomerId
End Property
Public Property Let custID(value As String)
    custID = value
End Property
Public Property Get custName() As String
    custName = cName
End Property
Public Property Let custName(value As String)
    custName = value
End Property
Public Property Get custAddress1() As String
    custAddress1 = cAddress1
End Property
Public Property Let custAddress1(value As String)
    custAddress1 = value
End Property
Public Property Get custAddress2() As String
    custAddress2 = cAddress2
End Property
Public Property Let custAddress2(value As String)
    custAddress2 = value
End Property
Public Property Get custCity() As String
    custCity = cCity
End Property
Public Property Let custCity(value As String)
    custCity = value
End Property
Public Property Get custState() As String
    custState = cState
End Property
Public Property Let custState(value As String)
    custState = value
End Property
Public Property Get custZip() As String
    custZip = cZip
End Property
Public Property Let custZip(value As String)
    custZip = value
End Property
Public Property Get custDoB() As String
    custDoB = cDoB
End Property
Public Property Let custDoB(value As String)
    custDoB = value
End Property
Public Property Get tName() As String
    tName = TableName
End Property
Public Property Let tName(value As String)
    tName = value
End Property
Public Property Get eNumber() As Long
    eNumber = ErrNumber
End Property
Public Property Let eNumber(value As Long)
    eNumber = value
End Property

下面是标准模块中的：

Option Explicit
Const CONNSTR = REDACTED FOR PUBLIC VIEWING

Const ConnectionError As Long = -2147467259
Sub CIFGrab()
    Const bhschlp8 As String = "bhschlp8.jhadat842"
    Const cncttp08 As String = "cncttp08.jhadat842"

    Application.ScreenUpdating = False


'\\\\DATABASE OPERATIONS////

    Dim tDBGrabRecord As clsCustInfo

    tDBGrabRecord.tName = getCIFDBGrabTestRecord(cncttp08)  <---ERROR 91 Happens on this line

    If tDBGrabRecord.eNumber = ConnectionError Then tDBGrabRecord = getCIFDBGrabTestRecord(bhschlp8)


End Sub
Function getCIFDBGrabTestRecord(ByVal tName As String) As clsCustInfo
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String
    Dim tDBGrabRecord As clsCustInfo

    On Error Resume Next

    conn.Open CONNSTR

    SQL = getCIFDBGrabSQL(tName)

    rs.Open SQL, conn

    tDBGrabRecord.eNumber = Err.Number

    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        If Not tDBGrabRecord.eNumber = ConnectionError Then
            With tDBGrabRecord
                .custID = Trim(rs.Fields("cifNum").value)
                .custName = Trim(rs.Fields("custName").value)
                .custAddress1 = Trim(rs.Fields("stAdd1").value)
                .custAddress2 = Trim(rs.Fields("stAdd2").value)
                .custCity = Trim(rs.Fields("City").value)
                .custState = Trim(rs.Fields("State").value)
                .custZip = Trim(rs.Fields("Zip").value)
                .custDoB = Trim(rs.Fields("DoB").value)
                .tName = tName
            End With
            rs.MoveNext

            With tDBGrabRecord
                Debug.Print "CIF:", .custID, "Name:", .custName, "Street 1:", .custAddress1, _
                            "Street 2:", .custAddress2, "City:", .custCity, "State:", .custState, _
                            "Zip:", .custZip, "DoB:", .custDoB
            End With
        End If
    End If

    rs.Close
    conn.Close

    getCIFDBGrabTestRecord = tDBGrabRecord


End Function
Function getCIFDBGrabSQL(ByVal TableName As String) As String
    Dim SelectClause As String
    Dim FromClause As String
    Dim WhereClause As String
    Dim JoinClause As String

    SelectClause = "SELECT " & _
                   "cfcif# AS cifNum, cfna1 AS custName, " & _
                   "cfna2 AS stAdd1, cfna3 AS stAdd2, " & _
                   "cfcity AS City, cfstat AS State, " & _
                   "left(cfzip,5) AS Zip, " & _
                   "date(digits(decimal(cfdob7 + 0.090000, 7, 0))) AS DoB"
    FromClause = "FROM " & TableName & ".cfmast cfmast"

    WhereClause = "WHERE cfdead = '" & "N" & "'"

    getCIFDBGrabSQL = SelectClause & vbNewLine & FromClause & vbNewLine & WhereClause

End Function

Answer 1

像这样的东西应该管用--我重新考虑了一下。

已编译但未测试。

Option Explicit

Const CONNSTR = "REDACTED FOR PUBLIC VIEWING"

Sub CIFGrab()

    Const bhschlp8 As String = "bhschlp8.jhadat842"
    Const cncttp08 As String = "cncttp08.jhadat842"

    Dim tDBGrabRecord As clsCustInfo

    'passing in all potential table names/sources in array
    Set tDBGrabRecord = getCIFDBGrabTestRecord(Array(bhschlp8, cncttp08))

    If tDBGrabRecord Is Nothing Then
        MsgBox "Failed to get record", vbExclamation
    Else
        'work with tDBGrabRecord
    End If

End Sub



Function getCIFDBGrabTestRecord(arrNames) As clsCustInfo

    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL As String, nm, okSql As Boolean
    Dim tDBGrabRecord As clsCustInfo

    conn.Open CONNSTR

    'try each provided name: exit loop on successful query
    For Each nm In arrNames

        SQL = getCIFDBGrabSQL(CStr(nm))

        On Error Resume Next
        rs.Open SQL, conn 'try this name
        If Err.Number = 0 Then okSql = True
        On Error GoTo 0 'cancel on error resume next

        If okSql Then
            If Not rs.EOF Then
                Set tDBGrabRecord = New clsCustInfo 'create an instance to populate
                With tDBGrabRecord
                    .custID = Trim(rs.Fields("cifNum").value)
                    .custName = Trim(rs.Fields("custName").value)
                    .custAddress1 = Trim(rs.Fields("stAdd1").value)
                    .custAddress2 = Trim(rs.Fields("stAdd2").value)
                    .custCity = Trim(rs.Fields("City").value)
                    .custState = Trim(rs.Fields("State").value)
                    .custZip = Trim(rs.Fields("Zip").value)
                    .custDoB = Trim(rs.Fields("DoB").value)
                    .tName = CStr(nm)

                    Debug.Print "CIF:", .custID, "Name:", .custName, "Street 1:", .custAddress1, _
                            "Street 2:", .custAddress2, "City:", .custCity, "State:", .custState, _
                            "Zip:", .custZip, "DoB:", .custDoB
                End With
                'rs.MoveNext  'surely this is not needed here?
            End If

            Exit For 'done trying names
        End If
    Next nm

    If rs.State = adStateOpen Then rs.Close
    If conn.State = adStateOpen Then conn.Close

    Set getCIFDBGrabTestRecord = tDBGrabRecord

End Function

Function getCIFDBGrabSQL(ByVal TableName As String) As String
    Dim SelectClause As String
    Dim FromClause As String
    Dim WhereClause As String
    Dim JoinClause As String

    SelectClause = "SELECT " & _
                   "cfcif# AS cifNum, cfna1 AS custName, " & _
                   "cfna2 AS stAdd1, cfna3 AS stAdd2, " & _
                   "cfcity AS City, cfstat AS State, " & _
                   "left(cfzip,5) AS Zip, " & _
                   "date(digits(decimal(cfdob7 + 0.090000, 7, 0))) AS DoB"
    FromClause = "FROM " & TableName & ".cfmast cfmast"

    WhereClause = "WHERE cfdead = '" & "N" & "'"

    getCIFDBGrabSQL = SelectClause & vbNewLine & FromClause & vbNewLine & WhereClause

End Function