VBA Using Global Variables to open Multiple instances of a form in Access 2010

Question

I have a form Cases and session dates that display the patients that I have seen. I am trying to create a method by which I can double click on any client's name and it opens up a different form with more detail. I want to be able to do this with multiple patients, so for example, I could select and have open the more detailed forms for the clients I am going to see today.

The form that displays the summary detail is called CaseAndSessionDetail and the more detailed form ClientDetails.

I am using Ms-Access 2010 running under windows 8.1

I have written/copied a module which I think should do what I want by using a global variable to create a collection of forms:

Option Explicit

'This holds the collection of forms as they are created
Global mcolFormInstances As New Collection

Function OpenFormInstance(FormName As String, WhereCondition As String)
    'Declare for name
    Dim frm As form

    Select Case FormName
    Case "ClientDetails"
        Set frm = New Form_ClientDetails
    Case Else
        Debug.Assert False
    End Select
    If WhereCondition <> "" Then
        frm.Filter = WhereCondition
        frm.FilterOn = True
    End If
    ''make the form visible
    frm.Visible = True
    'Need to add a reference to the form so that it does not
    'immediately close when the for variable goes out of scope
    mcolFormInstances.Add (frm)
End Function

And the function works and selects and opens/display the correct client details form if you set a breakpoint at the last line mcolFormInstances.Add(frm). Without the breakpoint the form is closed again (I suspect because the variable goes out of scope after the function ends.

For reference the function is called by macro on the form "CaseAndSessionDetail"

If Not ISNull([Screen].[ActiveControl] Then 
    Function OpenForm("frmContactDetails" = '"& Ltrim([Screen].[ActiveControl]) & "'") 
EndIF

I suspect that I am not declaring the collection object as a global variable correctly. I have tried declaring it in both forms in a separate module, using Public and Global and have yet to find success.

I realize I am probably overlooking something very simple but would welcome any help

Try removing the parentheses from mcolFormInstances.Add (frm). You're forcing frm to be passed ByVal instead of ByRef. — Comintern
– Comintern, Commented Aug 25, 2018 at 18:44

Comintern · Accepted Answer · 2018-08-25 20:29:23Z

Your issue is simply an obscure syntax mistake on this line:

mcolFormInstances.Add (frm)

The parentheses around the argument frm are not surrounding an argument list - that is why the VBE inserted a space between .Add and (frm) for you when you tried to type mcolFormInstances.Add(frm). These are completely different semantically. Surrounding an expression with parentheses in this context is described in section 5.6.6 of the VBA language specification:

5.6.6 Parenthesized Expressions

A parenthesized expression consists of an expression enclosed in parentheses.

Static semantics. A parenthesized expression is classified as a value expression, and the enclosed expression must able to be evaluated to a simple data value. The declared type of a parenthesized expression is that of the enclosed expression.
 parenthesized-expression = "(" expression ")"
Runtime semantics. A parenthesized expression evaluates to the simple data value of its enclosed expression. The value type of a parenthesized expression is that of the enclosed expression.

What this means is that you are evaluating the run-time expression frm as a simple data type and storing that in your Collection. Regardless of what that value actually is (I'd have to check the evaluation tree to figure out what it evaluates to), you aren't incrementing the reference count on frm, and this allows it to go out of scope and get released as OpenFormInstance is exiting.

Collectives™ on Stack Overflow

VBA Using Global Variables to open Multiple instances of a form in Access 2010

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related