Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Managing Multiple Instances of a Form

Want to compare two or more clients on screen at the same time? Though rarely used, the feature was introduced in Access 97. The New keyword creates an instance of a form with a couple of lines of code, but managing the various instances takes a little more effort. A sample database demonstrates the code in this article.

Creating Instances

A simple but inadequate approach is to place a command button on the form itself. For a form named frmClient with a command button named cmdNewInstance, you need just 5 lines of code in the form’s module:

Dim frmMulti As Form
Private Sub cmdNewInstance_Click()
    Set frmMulti = New Form_frmClient
    frmMulti.SetFocus
End Sub

Open the form and click the command button. A second client form opens on top of the first, and can display a different client. The second instance also has the command button, so you can open a third instance, and so on.

However, these forms are not independent of each other. Close the first one, and they all close. Click the New Instance button on the second one, and the third and fourth instances are replaced. Since the object variable frmMulti is declared in the class module of the form, each instance can support only one subsequent instance, so closing a form or reassigning this variable destroys all subsequent instances that may be open.

You also have difficulties keeping track of an instance. The Forms collection will have multiple entries with the same name so Forms.frmClient is inadequate. The index number of the Forms collection such as Forms(3) won’t work either: these numbers change as forms are opened and closed.

Managing Instances

To solve the dependencies, create a collection in another module. Add to the collection as each new instance is opened, and remove from the collection when it is closed. Each instance is now completely independent of the others, depending only on your collection for survival.

To solve the problem of the instance’s identity, use its hWnd the unique handle assigned to each window by the operating system. This value should be constant for the life of the window, though the Access 97 Help File warns: Caution: Because the value of this property can change while a program is running, don't store the hWnd property value in a public variable. Presumably, this comment refers to reusing this value when a form may be closed and reopened. The following example uses the hWnd of the instance as the key value in the collection.

The first line below creates the collection where we can store independent instances of our form. The function OpenAClient() opens an instance and appends it to our collection. This code is in the basPublic module of the sample database:

Public clnClient As New Collection    'Instances of frmClient.
Function OpenAClient()
    'Purpose:    Open an independent instance of form frmClient.
    Dim frm As Form

    'Open a new instance, show it, and set a caption.
    Set frm = New Form_frmClient
    frm.Visible = True
    frm.Caption = frm.Hwnd & ", opened " & Now()

    'Append it to our collection.
    clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)

    Set frm = Nothing
End Function

Function CloseAllClients()
    'Purpose: Close all instances in the clnClient collection.
    'Note: Leaves the copy opened directly from database window/nav pane.
    Dim lngKt As Long
    Dim lngI As Long

    lngKt = clnClient.Count
    For lngI = 1 To lngKt
        clnClient.Remove 1
    Next
End Function

The second function CloseAllClients() demonstrates how to close these instances by removing them from our collection. But if the user closes an instance with the normal interface, we need to remove that instance from our collection. That’s done in the Close event of form frmClient like this:

Private Sub Form_Close()
    'Purpose: Remove this instance from clnClient collection.
    Dim obj As Object        'Object in clnClient

    Dim blnRemove As Boolean  'Flag to remove it.

    'Check if this instance is in the collection.
    For Each obj In clnClient
        If obj.Hwnd = Me.Hwnd Then
            blnRemove = True
            Exit For
        End If
    Next

    'Deassign the object and remove from collection.
    Set obj = Nothing
    If blnRemove Then
        clnClient.Remove CStr(Me.Hwnd)
    End If
End Sub

Note that CloseAllClients() demonstrates how removing the object from the collection closes the instance. If a form is opened directly from the database window/nav pane, this copy is not closed. In a production environment, you probably don’t allow users near the database window/nav pane. To handle that case as well, replace the code with a loop to DoCmd.Close acForm frmClient until no copies are left. (Remember to force a save before closing if Dirty: a bug in Close silently discards your edits if there is any reason why the record cannot be saved, such as a required field missing.)

To see the code in action, download the sample database in Access 97 format (24KB, zipped) or Access 2000 format (19KB, zipped), in a module named basPublic. It also contains the frmClient form with its Close procedure, command buttons for calling OpenAClient() and CloseAllClients(), and a search form that uses the form's hWnd to return to the instance that called it.