Access Cookbook - Ken Getz [57]
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