Keep object in memory while is form is open (access)-CodePudding

I am writing an access application, and I just spent time setting up a class that would make the application easy to understand and efficient.

However, when I need the object for the second part, the object is no longer in memory.

I have a user click on a button that sets up the object and does some tests:

Public this_renewal As Renewal

Private Sub cmdMA_Click()
    
        Set this_renewal = Factory.CreateRenewal(cMA)
        
        Call BranchLabelVisibility(True)
        Me.lblBranchToAdd.Caption = this_renewal.Abb
        Call DateLabelVisibility(True)
        Me.lblYearToAdd.Caption = this_renewal.Year
        Me.lblMonthToAdd.Caption = this_renewal.Month
        Call TestMonth
    
End Sub

then the user decides if they want to import this_renewal file (which is the object in memory) and clicks the import button if they do which runs this code:

Private Sub cmdImport_Click()

    DoCmd.SetWarnings False
    
    Call FixExcelFile
    
    Dim strTableName As String
    Dim strImportName As String
    strTableName = this_renewal.Table
    'strImportName = Left(fnGetFileName(Me.tbFileToImport), 8)

    Call ImportTable
    
    'Count and Display Values of the two Tables
    Call TableLabelVisibility(True)
    Call GetTableValues
    
    'Create Backup of Original
    Call CreateBackup
    
    'Run Queries to Update Data of Import
    Call AppendQuery
    Call UpdateMonth
    Call UpdateStatus
    Call UpdateUnderWriter
    
    Call ShowResults
    
    DoCmd.SetWarnings True

End Sub

The problem is when the first button stops running the object is out of memory and I have no access to it when the user clicks the second button. Is there anyway I can keep the object in memory so I can use all of its properties?

Here is the CreateRenewal Function:

 Public Function CreateRenewal(strFileName As String) As Renewal

    Dim renewal_obj As Renewal
    Set renewal_obj = New Renewal
    
    Call renewal_obj.InitiateProperties(strFileName)
    Set CreateRenewal = renewal_obj

End Function

and the InititateProperties within the renewal object:

Public Sub InitiateProperties(ByVal strFileName As String)

    strRenewalFile = strFileName
    strRenewalFullFileName = fnGetFullFileName()
    strRenewalFileAndPath = cPath & strRenewalFullFileName

    strBranchLetter = fnGetLetterFromFile(strRenewalFile)

    strAbb = DLookup("BranchAbb", "tblBranches", "BranchLetter = '" & strBranchLetter & "'")
    strBranchName = DLookup("Branch", "tblBranches", "BranchAbb = '" & strAbb & "'")
    
    If Len(Mid(strRenewalFullFileName, 10, 2)) = 1 Then
        strRenewalMonth = "0" & Mid(strRenewalFullFileName, 10, 2)
    Else
        strRenewalMonth = Mid(strRenewalFullFileName, 10, 2)
    End If
    strRenewal2DigitYear = Mid(strRenewalFullFileName, 12, 2)
    strRenewalYear = "20" & strRenewal2DigitYear
    
    strRenewalTable = strAbb & " " & strRenewalYear & " Renewals"

End Sub

If I step through the code during the cmdMA_Click event the object is created. It is after that click event finishes and I trigger the next click event that the I receive "Object Variable or With Variable not set" for the object. Within the original click even I have no issue.

CodePudding user response：

As the relevant code for Factory.CreateRenewal(cMA) is not shown I crerated an example how to re-produce the error.

I have a class this_renewal with the following code

Option Explicit

Public Function myTest() As String
    myTest = "Test"
End Function

Then I created a class Factory with Attribute VB_PredeclaredId = True and the following code

Option Explicit

Public Function CreateRenewal(cMA As String) As this_renewal

    ' Doing nothing here
    ' result will be that CreateRenewal will return Nothing
    ' which will lead to the error
    '  "Object Variable or With Variable not set"

End Function

In a Userform I have two buttons with the code

Option Explicit
Public this_renewal As this_renewal

Private Sub btn1_Click()
    Set this_renewal = Factory.CreateRenewal("cMA")
End Sub

Private Sub btn2_Click()
    Debug.Print this_renewal.myTest
End Sub

If I changed the code in the Factory class to

Public Function CreateRenewal(cMA As String) As this_renewal
    Set CreateRenewal = New this_renewal
End Function

everything would work fine in case I would press btn1 first and then btn2 secondly.

CodePudding user response：

If that class var is public to the form's code and created? Then it will and should persist for the life time of that form being open. You certainly cannot on a global level, or in other forms reference that class variable you created in that form.

So you can either move the var declare to a standard public module (out of the form), or in fact in other forms do this:

In our form - declare the class at form level module - as public

Option Compare Database
Option Explicit

Public this_renewal As Renewal

So, now say your form runs code to setup the object, as you have like this:

Set this_renewal = Factory.CreateRenewal(cMA)

Now, if you open any other form? Well you have two choices to get at your class.

You can reference the form above, and do this in code:

dim this_renewal As Renewal
set this_renewal = forms!ThatFirstFormabove.this_renewal.

So, that class instance ONLY exists in the context of your form in which you declare the var (this_renewal).

You can also do it this way. Say in that first form (with public this_renewal).

You would in the target form, ALSO declare this_renewal at the forms level code.

Then you would and could do this:

docmd.OpenForm("frmB")
set forms("frmB").this_renewal = me.this_renewal

So, either you pass the class, or reference it directly from the current form that has the class, or you move the var declare of the class out of the forms module code, and place it in a global standard plane jane module. Say in module1.

If you do that, then all forms and all code can use that instance of the class.