Accessing form variables in VBA

Posted on 2006-05-17
Last Modified: 2012-06-21
Hi All,

One thing I've always struggled with in Access is how to firstly keep form instances alive when instantiating from VBA (i.e. not using DoCmd), and secondly, how to access form-level variables after the form has been closed by the user.

For example, I want to open a form from code, have the user input some data, close the form, then I read that data before the form instance is disposed of.

I've got a handle on the form lifetime problem - that's solved thanks to some handy tips I found on the 'net. However, I'm still clueless as to how I can get my hands on form-level data once the form instance is closed by the user.

Here's some example code to illustrate:

Calling Sub:
Public Sub DemonstrateMyProblem()
       Dim pfrmTemp As New Form_frmSomething, pstrSomeInput As String
       pstrSomeInput = pfrmTemp.mstrSomeVariable
       Debug.Print pstrSomeInput
End Sub

And in the form itself:
Dim mThisForm As Form_frmSomething, mstrSomeVariable As String

Private Sub Form_Load()
        Set mThisForm = Me 'This is the trick I found for keeping a form instance alive
End Sub

Private Sub SomeControl_AfterUpdate
        mstrSomeVariable = SomeControl.Value
End Sub

Private Sub Show()
       Set mThisForm = New Form_frmTemp
       mThisForm.Visible = True
End Sub

Private Sub Form_Unload()
      Set mThisForm = Nothing
End Sub

So, as you can see, I want to show the form, it keeps itself alive, then I read some variable from it when it's closed. In the unload event I'm destroying the instance of the form so it would make sense that I can't read from it once it's been closed. However, even if I removed that and destroyed the form instance from the calling sub, then how do I pause code execution so the variable isn't read immediately (i.e. before the form is used and/or closed).

Any tips gratefully received!



Question by:Aikendrum
    LVL 58

    Accepted Solution

    Hello Aikendrum

    I guess you come from VB and try to do the same in Access? Don't ;)

    First, let's see the normal way to handle "pop-up" forms:

    Private Sub cmdCallPopup_Click
        DoCmd.OpenForm "fmodPopup", WindowMode:=acDialog
        ' code pauses while that form is visible...
        If SyCmd(acSysCmdGetObjectState, acForm, "fmodPopup") Then
            ' the form still exists!
            MsgBox Forms!fmodPopup!txtSomeMessage
            ' we don't need it anymore
            DoCmd.Close acForm, "fmodPopup"
        End If
    End Sub

    The popup form will use two buttons: [OK] and [Cancel], the latter being equivalent with just closing the form.

    Private Sub cmdOK_Click()
        Me.Visible = False
    End Sub

    Private Sub cmdCancel_Click()
        DoCmd.Close acForm, Me.Name
    End Sub

    Notice that closed forms do not exist in memory. They are stored in Access "documents", but in a serialized form, and nothing on them is accessible.

    So, this would be the basics. Now for the advanced stuff, you can in fact create several instances of a form, but not several "dialog" instances. The only way to get a modal dialog form (i.e. interrupting the calling VB code) is through DoCmd.OpenForm.

    For "ghost" instanciated forms, you need to hold them alive through an object variable, like any other object or class module.

        With New Form_fmodPopup
            ' this creates an instance of a VB class module
            ' this module is "linked" to an access form, which is also instanciated
            ' (but hidden)
            .Form.Visible = True
            ' the associated form of the module is visible
            ' do something with it...
        End With
        ' the implicit "with" object goes out of scope,
        ' the class module gets caught by the garbage collector,
        ' both the class module and the form are removed from memory

    If you want to "keep a form alive", do *not* use the self-reference trick. For a form, you still have a way to handle it, so that you can still destroy it when needed, but if it's a hidden modal form, it's not listed in the Windows menu, and you can't get to it anymore...

    Instead, create your own collection.

    Global colMyForms As Collection
        Set frm = New Form_fmodPopup
        colMyForms.Add frm

    You now have the Forms collection for "official" forms and colMyForms for your own duplicates.

    In access, this is merely a neat trick. When to use it?

    a) As living materialization of another class object. For example, your "export to HTML" class can open a form this way to show the progress. In case of an error, a code reset, or even the normal termination, the "ghost" form goes out of scope and is destroyed together with your main class.
    It's also a great debugging tool if your recursively create objects, of course.

    b) In very rare cases, when you need a second instance of the same form on screen.

    The examples you show above do not enter in these narrow cases, and you have no good reason to use this trick. Use the offician forms collection instead.

    Hope this helps!

    LVL 65

    Expert Comment

    If u want to retrieve values from a form that is now closed

    why dont you create a new module and define public variables (globals)

    Ensure you have a function in there that initialises them -

    then you can simply access and set that variable whenever u need to

    A simple open form would suffice, but in that code, you need to ensure you set your globals up

    If u dont want variables, u could perhaps store them in a temp table
    Depending on how many values u want to store, u can create a bounded form, which goes straight to add, no edits or deletes allowed
    the form code will clear the contents (on form_load event handler)

    now u can create yourself a class or module which returns the values stored in the table


    Author Comment

    Thanks all, problem solved.


    LVL 58

    Expert Comment

    Glad to help, good luck with your project!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now