• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

Accessing form variables in VBA

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!



  • 2
1 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!

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

AikendrumAuthor Commented:
Thanks all, problem solved.


Glad to help, good luck with your project!

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now