将记录存储在类的实例中，并从 Sub 调用该类

Question

我有一个带有组合框的表单，我用访问数据库（独立于数据库的用户界面）中的记录填充该组合框。我想做的是在程序的整个生命周期中将记录存储在类的实例中，而不是在用户每次选择组合框时查询表。调用 Sub 中的代码失败，可能是由于类中的编码不正确。

这就是我连接到数据库的方式：

Option Explicit
Dim DBCONT As Object
Public Function connectDatabase()
    Set DBCONT = CreateObject("ADODB.Connection")
    Dim strDBPath As String
    strDBPath = "C:UsersrobDocumentsCyber SecurityDatabaseBackEnd.accdb"
    Dim sConn As String
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & strDBPath & ";" & _
                                "Jet OLEDB:Engine Type=5;" & _
                                "Persist Security Info=False;"
    DBCONT.Open sConn
    'Call closeDatabase
    End Function

Public Function closeDatabase()
    On Error Resume Next
    DBCONT.Close
    Set DBCONT = Nothing
    On Error GoTo 0
End Function

这是我尝试将记录存储为类的实例。分配"设置 GETDEVICES = Rs"时收到错误消息类型不匹配，知道为什么吗？

Option Explicit

Public Rs() As ADODB.Recordset

Function getdevices() As ADODB.Recordset
    connectDatabase
    Dim Rs As Object
    Dim CurDatabase As Object
    Set CurDatabase = CurrentDb
        Set RS = CurDatabase.OpenRecordset ("SELECT * FROM tblCDA")
    Set getdevices = Rs
    closeDatabase
    Set Rs = Nothing
End Function

调用类的子：

Private Sub cboSysDesignation_Click()

Dim rsDevice As Object
Set rsDevice = getdevices(Rs)  'FAILS AT THIS POINT!!! "Object variable or With block variable not set"
DeviceName = cboSysDesignation.Value
rsDevice.MoveFirst
    Do Until DeviceName = rsDevice.Fields("DeviceID")
        rsDevice.MoveNext
    Loop
        txtSystemDescription.SetFocus
        If rsDevice!DESC <> "" Then
        txtSystemDescription.Value = rsDevice!DESC
        Else
        txtSystemDescription.Value = ""
        End If
        txtSystemEngineer.SetFocus
        If rsDevice!ENGINEER <> "" Then
        txtSystemEngineer.Value = rsDevice!ENGINEER
        Else
        txtSystemEngineer.Value = ""
        End If

Set rsDevice = Nothing
End Sub

Answer 1

你写道："分配"设置 getdevices = Rs"时，我收到错误消息类型不匹配 知道为什么吗？

让我们逐步完成您的代码...

Function getdevices() As ADODB.Recordset
    connectDatabase
    Dim Rs As Object
    Dim CurDatabase As Object
    Set CurDatabase = CurrentDb

CurrentDb是一个DAO.Database对象。

    Set RS = CurDatabase.OpenRecordset ("SELECT * FROM tblCDA")

DAO.Database.OpenRecordset方法返回 DAO 记录集。

    Set getdevices = Rs

getdevices被宣布为As ADODB.Recordset但Rs DAO.Recordset。 它们的类型不匹配。