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

Question

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 click event, 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

Here is the CreateRenewal Function that I have in a regular module called factory. I got this idea from another thread on how to initialize a class with attributes: VBA: Initialize object with values?

 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 that calls the InititateProperties within the renewal class:

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

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

If I step through the code during the cmdMA_Click event the object is created and those properties are initialized within the InitiateProperties sub in the class. After that click event finishes and I trigger the next click event (on the next button, on the same form) I receive "Object Variable or With Variable not set" for the object. Within the original click event I have no issue.

why don't you save the user selections into an array before the userform unloads? — Sgdva, Mar 23 '22 at 20:05
It seems that you placed `Public this_renewal As Renewal `in a form module. Place it in a module instead. — Storax, Mar 23 '22 at 20:28
@Storax and then just call the sub that is in the module from the form? Because the form loads upon opening — djblois, Mar 23 '22 at 20:34
@djblois: I guess, yes. But I do not know any details of your application. But by putting `this_renewal` in a modul the variable will not go out of scope when the form is unloaded. — Storax, Mar 23 '22 at 20:36
@Storax I am not worried about the variable going out of the scope when the form is unloaded. I want it it stay in scope as long as the form is open. even if code isn't currently running. — djblois, Mar 23 '22 at 20:37
@djblois : But if the form is open the variable is in memory. Where is `Private Sub cmdImport_Click()` located? If that code is in another form you refer to `this_renewal` in `Private Sub cmdImport_Click()` in the wrong way. — Storax, Mar 23 '22 at 20:39
no it is in the same form but when I went to use the object within the cmdImport_Click() event it says "Object Variable or With Variable not set" They are both within the same form. — djblois, Mar 23 '22 at 20:43
Then `Set this_renewal = Factory.CreateRenewal(cMA)` in `Private Sub cmdMA_Click()` did not work resp. it returned `Nothing`. — Storax, Mar 23 '22 at 20:46
@Storax I added more detail to the original post, if that helps. — djblois, Mar 23 '22 at 20:52
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 referance that class varible you created in that form. So you can either move the var decleare to a standard public module (out of the form), or in fact in other forms go: — Albert D. Kallal, Mar 23 '22 at 22:17
The code behind a form is for private access to the form's controls, that is the reason all control events are marked `Private Sub ...`. If you need access outside the form, use a standard module. — Kostas K., Mar 24 '22 at 12:47

Storax · Answer 1 · 2022-03-23T21:12:21.577

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.

Factory isn't the class. I used the idea of creating a factory module and put a sub in to use to initialize the class with attributes. I think the code I added to the Original questions might clear that up. — djblois, Mar 25 '22 at 17:45
Ok, then I'd suggest you should debug the code with the setting to break on error in the class module_. That might get you closer to the root cause. And if this does not help you could break on [every error](https://bettersolutions.com/vba/error-handling/break-on-all-errors.htm). — Storax, Mar 25 '22 at 19:09

score 0 · Answer 2 · answered Mar 23 '22 at 22:24

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.

Why are you messing with Attribute VB_PredeclaredId = True? Does not that reuqire a saveastext and a loadfromtext to do that? If you do that, then you do NOT need to use new keyword, and it would not make sense. I would remove that attribote, and simple declare a variable (as global - in standard code module as Public MyClass = new SomeClass. After that , you should be good to go. Not at all clear the reason for messing with that Attribute VB_PredeclaredId attribute - it not required. — Albert D. Kallal, Mar 25 '22 at 20:11
I do not know what you mean? Where am I messing with VB_Predeclardid attribute? I do not know what that means. Sorry if I do not understand. — djblois, Mar 25 '22 at 20:41

score 0 · Accepted Answer · answered Mar 28 '22 at 15:25

I figured it out; It is such a stupid thing that happens. So in the last procedure called during the first click event:

Private Sub TestMonth()

    If this_renewal.IsNewMonth Then
        Me.cmdImport.Enabled = True
        Me.lblFileMistake.Visible = False
    Else
        GoTo WrongFile
    End If
    
endSub:
    Exit Sub
    
WrongFile:
    
    Me.cmdImport.Enabled = False
    Me.lblFileMistake.Visible = True
    Me.lblFileMistake.Caption = "Month " & this_renewal.Month & " was already added for branch " & this_renewal.Name & "!!"
    GoTo endSub
    
End Sub

Right where I have 'Exit Sub', it was 'End'. That clears all variables. Once I changed it to 'Exit Sub', it now works correctly.

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

3 Answers3