Recipe 2.11 Open Multiple Instances of a Form

2.11.1 Problem

In an application, you have a form showing information about a customer. You would like to be able to open another copy of the form so you could move to a different row, compare values, perhaps copy from one row to another, or just look at more than one customer's record at once. As far as you can tell, you can have only one open copy of a form at a time.

2.11.2 Solution

In older versions of Access, you were limited to having only a single copy of a form open at any time. Starting with Access 95, you can open multiple instances of a form, under complete program control. There's no user interface for this functionality, however, so you must write code to make it happen. This solution demonstrates how to create, handle, and delete multiple instances of a form using the New keyword and user-defined collections.

Follow these steps to convert your own forms to allow for multiple instances:

To see this functionality in action, load and run frmCustomers from 02-11.MDB. Once it's open, click View Another Customer. This will create a new instance of the original form, with its own set of properties and current row. You can create as many new forms as you like and move from row to row on any or all of them. When you're done, click Close All Extra Copies, which will run code to delete all the extra forms. Figure 2-18 shows the original form, along with three extras.

Figure 2-18. Clones of frmCustomers with their own current rows

2.11.3 Discussion

Working with multiple instances of forms requires three skills: creating the new forms, storing their references, and deleting them. All three topics center around user-defined collections. These collections allow you to add and delete items at will, based on either their position in the collection or a string value that uniquely identifies each element. This example uses each form's hWnd property (its window handle) to identify the form in the collection.

In Access, each form stored in the database can be viewed as its own "class" of form: it's an object that you can replicate in memory, using the New keyword. The following statement will create a new instance of the form named frmCustomers:

Set frm = New Form_frmCustomers

Form_frmCustomers is the object type, and its name originates from its type (Form) concatenated with the actual class name (frmCustomers). Once you've executed this line of code, frm refers to a new, invisible form. You can set its properties, if you like, or make it visible with the following statement:

frm.Visible = True

If you want to refer to your new form later, you'll need to store a reference to it somewhere. In the example code, we used a user-defined collection. When you create a new instance of the form, the code adds that form reference to the collection so you can find the form, under program control, when you need to refer to it again.

Life Span of a Form The variables that refer to the newly created forms must have a life span longer than that of the procedure that created the forms. In this case, the form references are stored in a module-level collection, so their lifetime is the same as the database itself. When you create a new instance of a form, if the variable referring to that form goes out of scope, Access destroys the new form instance. Because you'll want your forms to hang around longer than that, make sure your form variables have a static, module, or global scope.

In this example, the acbAddForm subroutine creates and stores the new form references. As it creates a new form (when requested to do so by that button click on frmCustomers), it adds the form reference to the collection of forms. A collection's Add method allows you to add the item and optionally store a unique string value describing the value at the same time. In this case, the code stores the form's hWnd property, converted to a string, as its unique identifier. The code also increments a variable that keeps track of the number of instances and places the new form at a convenient location before making it visible. This is the acbAddForm subroutine:

Private colForms As New Collection
Private mintForm As Integer

Const acbcOffsetHoriz = 75
Const acbcOffsetVert = 375

Public Sub acbAddForm( )
    Dim frm As Form
    
    Set frm = New Form_frmCustomers
    
    ' You have to convert the key to a string, so tack a "" onto
    ' the hWnd (which uniquely identifies each form instance) 
    ' to convert it to a string.
    colForms.Add Item:=frm, Key:=frm.Hwnd & ""
    
    ' Build up the caption for each new instance.
    mintForm = mintForm + 1
    frm.Caption = frm.Caption & " " & mintForm

    ' The numbers used here are arbitrary and are really useful
    ' only for this simple example.
    frm.SetFocus
    DoCmd.MoveSize mintForm * acbcOffsetHoriz, mintForm * acbcOffsetVert
    ' Finally, set this form to be visible.
    frm.Visible = True
End Sub

Sub acbRemoveForm(frm As Form)
    ' All the forms call this from their Close events. Since
    ' the main form isn't in the collection at all, it'll cause
    ' an error. Just disregard that.
    On Error Resume Next
    colForms.Remove frm.Hwnd & ""
    Err.Clear
End Sub

Eventually you'll want to close down all the extra instances of your form. This is quite simple: once you delete the form reference, Access will close the form for you. Therefore, in reaction to the Close All Instances button you created on your form, Access runs this subroutine:

Public Sub acbRemoveAllForms( )
   Dim varForm As Variant

   ' Reset the static variables.
   mintForm = 0
   For Each varForm In colForms
      colForms.Remove 1
   Next varForm
End Sub

This subroutine first resets the total number of instances back to 0, then walks through the collection of form instances one at a time, removing the first item each time. Because Access renumbers the collection each time you remove an item, this is the simplest way to remove all the items.

To keep things neat, we instructed you to attach to the form's Close event code that removes the specific form from the collection of forms when you close that form. Though this example doesn't need that functionality, you may find that in other situations you do need your collection to reflect accurately the forms that are currently loaded (if you want to list all the open forms, for example). There is one wrinkle here, however: when you ask the application to close all extra instances, Access closes each form, one by one. This, in turn, triggers the Close event for each of those forms. The Close event calls code attached to that event that attempts to remove the form from the collection of forms, but that form has already been removed from the collection. Therefore, the acbRemoveForm subroutine, shown here, disables error handling; attempting to remove an already removed form won't trigger an error.

Public Sub acbRemoveForm(frm As Form)
   ' All the forms call this from their Close events. Since 
   ' the main form isn't in the collection at all, it'll cause
   ' an error. Just disregard that.
   On Error Resume Next
   colForms.Remove frm.hWnd & ""
   Err.Clear
End Sub

Extra instances of forms aren't really treated exactly the same as their originals. For example, all the copies of a form share the same name as the original, so if you attempt to use the syntax:

Forms!frmCustomers

or:

Forms("frmCustomers")

to refer to an instance of a form, you'll be able to access only the original form. Access does add each instance to the Forms collection, but you can access them only by their ordinal positions in the collection. If you loop through the Forms collection to close all open forms, the code will close the instances, too.

Form instances have their own properties and their own current rows, but any changes you make to a form instance are not saved. That is, all instances of a form other than the original are read-only. That's not to say that the data on the form is read-only—it's the design of the form instance that's read-only.

You'll find multiple instances of forms to be a useful addition to your programming arsenal. They allow users to view multiple rows with their forms in Form View (the proverbial "have their cake and eat it, too" situation), and from there to copy/cut/paste data from one row to another. Your responsibility as the developer is to carefully manage the creation, storage, and deletion of these forms, because the Access user interface provides no help.