Evolution of a Function: ShowForm() Part 2

This is Part 2 of a series on the evolution of my ShowForm() function.

• Series Introduction
• Part 1: Minimum Viable Product (MVP)

Creating multiple instances of a form is a tricky thing to do in Access.

Here is an example of the concept in action:

Note that in the screenshot above, all four instances of the frmMSysObjects form are based on a single form design object named frmMSysObjects.

This is a handy technique if you want to allow your users to open multiple copies of a form to do side-by-side comparisons.

Making it Work

To make it work, there are a few rules you need to be aware of:

• The form must have a code-behind module (i.e., no "lightweight" forms)
• Each new instance of the form must be assigned to a different object variable
• The object variables must remain "in scope" (when the object variable is released, the form is closed)

Form code-behind modules are special types of class modules.  

They behave like class modules that have their Predeclared ID attribute set to Yes.  Classes that rely on predeclared instantiation do not need to be "created" before first use.

With predeclared instantiation, VBA creates a default instance of the class with the same name as the class.  As with implicit instantiation, the default instance gets created the first time it is used.  [In fact], you don't need to declare an object variable at all [to call the methods of the class].

The problem is that you can only ever have a single predeclared instance of a class.

Keeping Form Object Variables in Scope

The most common and straightforward way to maintain these object variables in scope is to assign them to a collection for this purpose.

While there are a variety of ways to improve upon this basic concept, those won't show up until later versions of the function.  For now, we need a reliable way to determine whether a given form should support multiple instances and a place to maintain the active form object variables.  We do this by:

• Using FormName to decide whether to create multiple instances of a form
• Saving form object variables in a module-level Collection variable
• Removing form object instances from the collection variable on form close

Code Changes from Part 1

The screenshots below use TortoiseHg to highlight the differences between versions 1 and 2 of the ShowForm function and the newly added function RemoveForm() which removes items from the form handle collection:

These changes highlight two tenets of building generic code solutions:

1. Build only what you need at first; don't try to anticipate future use cases
2. Maintain backward compatibility as you add features

Note how we have hard-coded the name of the form that will support multiple instances:

Select Case FormName
Case "frmMSysObjects"
    Set Frm = New Form_frmMSysObjects
End Select

In addition, we had to include a call to our new RemoveForm() function in the frmMSysObjects's Form Close property sheet event:

This code is decidedly NOT generic.  However, it gets us the functionality we need now without trying to predict what we might need in the future.

What if the form does not support multiple instances?  In that case, the functionality is identical to what was there in version 1.  The following code makes sure of that:

If Frm Is Nothing Then
    If FormIsOpen(FormName) Then DoCmd.Close acForm, FormName
    DoCmd.OpenForm FormName, acNormal, , Where, , , OpenArgs
Else

ShowForm() Part 2: Actual Code

Private FormCollection As Collection

'https://nolongerset.com/showform-part-2/
'Requires Private FormCollection As Collection  in module declaration section
Function ShowForm(FormName As String, _
                  Optional Where As String = "", _
                  Optional OpenArgs As Variant)
Dim Frm As Form
    On Error GoTo Err_ShowForm

    If Right(Where, 1) <> "=" Then
        Select Case FormName
        Case "frmMSysObjects"
            Set Frm = New Form_frmMSysObjects
        End Select
        If Frm Is Nothing Then
            If FormIsOpen(FormName) Then DoCmd.Close acForm, FormName
            DoCmd.OpenForm FormName, acNormal, , Where, , , OpenArgs
        Else
            If FormCollection Is Nothing Then Set FormCollection = New Collection
            If Len(Where) > 0 Then
                Frm.FilterOn = True
                Frm.Filter = Where
            End If
            If Not IsMissing(OpenArgs) Then Frm.OpenArgs = OpenArgs
            Frm.Visible = True
            FormCollection.Add Frm, CStr(Frm.hWnd)
        End If
    End If

Exit_ShowForm:
    Exit Function
Err_ShowForm:
    MsgBox Err.Description, , "Error " & Err.Number
    'LogError Err.Number, Err.Description, "ShowForm", "FormFunctions Module"
    Resume Exit_ShowForm
End Function

'Purpose: Maintains the module-level FormCollection
'           by removing window handles from the collection
'           as their associated forms are closed
'Usage: in Form's Close property sheet event: =RemoveForm([Hwnd])
Function RemoveForm(hWnd)
Dim Obj As Object, DoRemove As Boolean
    
    On Error GoTo Err_RemoveForm

    For Each Obj In FormCollection
        If Obj.hWnd = hWnd Then
            DoRemove = True
            Exit For
        End If
    Next Obj
        
    If DoRemove Then
        Set Obj = Nothing
        FormCollection.Remove CStr(hWnd)
    End If

Exit_RemoveForm:
    Exit Function
Err_RemoveForm:
    Select Case Err.Number
    Case Else
        MsgBox Err.Description, , "Error " & Err.Number
        'LogErr Err, Errors, "FormFunctions", "RemoveForm"
    End Select
    Resume Exit_RemoveForm
End Function

'https://nolongerset.com/why-so-lazy-access/
Function FormIsOpen(FormName As String) As Boolean
    FormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0)
End Function

Sample Usage

To create the screenshot shown at the top of this article, we simply call our updated ShowForm() function four times with four different Where clauses:

Note that you will need to manually move each form after it is opened because they all open up directly on top of each other (if you are using the Overlapping Windows option).  That's a problem we will address in a future version of the ShowForm() function.

Cover image created with Microsoft Designer

UPDATE [2023-07-26]: Added RemoveForm() companion function to maintain the module-level FormCollection collection.  It was in my code repository all along; I simply forgot to include it in the first version of this article.  I knew something wasn't quite right...