Store the records in an instance of a class and call the class from a Sub (VBA 2010)

Vote count:

0

I have a form with a combo box that I populate with records from a access db (user interface seperate from database). What I'd like to do is store the records in an instance of a class throughout the life of the program rather than querying the table each time the user selects the combo box. The code fails in the calling Sub, it may be due to incorrect coding in the class.

This is how I'm connecting to the database:

Option Explicit

Dim DBCONT As Object

Public Function connectDatabase() Set DBCONT = CreateObject("ADODB.Connection") Dim strDBPath As String strDBPath = "C:\Users\rob\Documents\Cyber Security\Database\BackEnd.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

'THIS IS MY ATTEMPT AT STORING THE RECORDS AS AN INSTANCE OF A CLASS:

Option Explicit

Public Rs() As ADODB.Recordset

Function connection_test() As ADODB.Recordset

connectDatabase

Dim Rs As ADODB.Recordset

Dim CurDatabase As Object

Set CurDatabase = CurrentDb

    With Rs
        CurDatabase.OpenRecordset ("tblCDA")
    End With

Set connection_test = Rs

closeDatabase

Set Rs = Nothing

End Function

SUB THAT CALLS THE CLASS:

Private Sub cboSysDesignation_Click()

connection_test

Dim rsDevice As Object

Set rsDevice = connection_test(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

asked 36 secs ago

rgraham

1

Store the records in an instance of a class and call the class from a Sub (VBA 2010)