> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Evolution of a Function: ShowForm() Part 2
- URL: https://nolongerset.com/showform-part-2/
- Published: 2023-07-14T03:59:00.000Z
- Updated: 2026-05-08T12:34:26.000Z
- Description: As our ShowForm function evolves, we introduce a very basic capability to create and manage multiple form instances.
- Author: Mike Wolfe
- Tags: ShowForm, #Import 2026-05-20 02:59

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

- *[Series Introduction](https://nolongerset.com/evolution-of-a-function-showform/)*
- [*Part 1: Minimum Viable Product (MVP)*](https://nolongerset.com/showform-part-1/)

---

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

Here is an example of the concept in action:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/07/image-27.png)

Four instances of the **frmMSysObjects** form all open at the same time.

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](https://nolongerset.com/lightweight-forms-just-dont/))
- Each new instance of the form must be assigned to a different object variable
- The object variables must remain "[in scope](https://nolongerset.com/scope-vs-extent-in-vba/)" (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](https://nolongerset.com/setpredeclaredid/) set to Yes. Classes that rely on [predeclared instantiation](https://nolongerset.com/creating-class-instances/#predeclared-instantiation) do not need to be "created" before first use.

> With [predeclared instantiation](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/189fb41b-cc3a-4999-a6d2-ba89f72d2870), 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/07/image-28.png)

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/07/image-51.png)

These changes highlight two tenets of [building generic code solutions](https://nolongerset.com/building-generic-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:

```vba
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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/07/image-52.png)

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:

```vba
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

```vba
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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2023/07/image-29.png)

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](https://designer.microsoft.com/)*

***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...*