Store records in an instance of a class to make available anywhere in project throughout the life of the program

Question

I have a form with a combo box that I populate with records from a access db (I use linked tables). 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. Right now I'm calling a funtion from a private sub, the function returns the DAO recordset data which is what I want but the question I have is, how do I pass the data from the function recordset to a public DAO.Recordset, assuming if this was possible it would hold the recordset data throughout the life of the running program. When I run the code and it finishes Public Rs() in the Watch output window says; Recordset(0 to -1) and No Variables, I'm not sure how to correct this.

Here's my code:

Private Sub cmdGetRecordset_Click()
    Dim strDescription As String  
    Dim strModel As String  
    Dim rst As DAO.Recordset  

    Set rst = getdevices()  

    Do While Not rst.EOF  
        strDescripton = rst!DESC  
        strModel = rst!MODEL  

        Debug.Print strDescription & "  " & strModel  

        rst.MoveNext  
    Loop  
    'rst.Close'  
    'Set rst = Nothing'  
End Sub

''''''

Option Compare Database

Public Rs() As DAO.Recordset

Function getdevices() As DAO.Recordset
    Dim Rs As Object  
    Dim CurDatabase As Object  

    connectDatabase  
    Set CurDatabase = CurrentDb  
    Set Rs = CurDatabase.OpenRecordset("SELECT * FROM tblCDA") 
    Set getdevices = Rs
    closeDatabase
End Function

Sorcefyre Sorcefyre · Accepted Answer · 2015-01-14T16:43:03

I don't fully understand your goal to be honest. Depending what you're trying to do there are a number of ways to tackle the issue.

1) Are you just pre-populating controls when the form loads?

Either run a series of queries in the Form_Load event each time the form is loaded and pass the results to each combobox as it's source OR create a temporary table with the values when the database is opened and query the temp table.

2) Are you trying to capture a persistent state of what the user selected when moving between forms or reports?

Use a temp table or dynamic array to capture the user selections (values of the comboboxes) and query the table or iterate through the array when referencing a value somewhere else.

3)Are you trying to dynamically change combobox values based on user selections?

Sadly I think having to requery the source data is the only option here. You could get all of the data from various tables and store it in memory using arrays and then iterate through the arrays as needed to change the combobox values but that would be a substantial memory footprint depending on the data.

Store records in an instance of a class to make available anywhere in project throughout the life of the program

1 Answers