Handling multiple instances of the same form is not as straightforward as it might seem. The reason is that the default opening behavior of a form is all the same instance of the form. Understanding the concept of an instance is as simple as counting to 1. There is only one form, and every time that form is opened, it is the same form. The form opens and displays the data specified in the Recordsource property.
This limitation is not typically a problem for the average application, as a user is happy to view data in this manner. For example, opening the customer form, based on the Customers table or a query thereof, displays all customers in the data source. The customer can navigate to the desired customer, and perform any maintenance. Now, imagine that there is a data entry form for stock market ticker symbols and their daily closing prices. With only one form, comparing two or more stocks may be rather difficult. Performing a side-by-side comparison of the vital statistics of two or more stocks would be challenging when only one can be displayed at a time.
So, now that a need has been identified, the discussion turns to the execution of multiple instances of the form. (Just in case you are thinking about expanding this same concept for reports, please understand that it is not possible. The report preview window is controlled outside of Access and therefore there is nothing you can do to work around its limitations.) The way to create more instances of a form is to create an object variable based on the desired form. The following code example performs this step:
Private Sub CreateFormInstance()
'Declare the variable to be an existing form Dim frmTestl As Form_Orders
'Set the variable to a new instance of the form Set frmTestl = New Form_Orders
'Show the Form, as setting it does not make it visible End Sub
Notice that the visible property of the form must be explicitly set to True, because the form is not visible by default using this opening method. Something also to note is that using this method would cause the form to only blink across the screen momentarily when the code is run. This occurs because the scope of the variable is local to the procedure in which it is declared. To allow the form to be more persistent requires that the variable be declared in the general declarations section of a module. Variables defined in this Global section will stay in scope throughout the life of the application. The scope in the above procedure is only until the procedure ends, which causes the flicker, as the variable is destroyed at the End Sub statement.
The following code is an example of the creation of multiple instances of the same form that persist throughout the life of the application:
'Declare the first instance Dim frmTest1 As Form_Orders
'Declare the second instance Dim frmTest2 As Form_Orders
Private Sub cmdOpenForms_Click() 'Create the first instance Set frmTest1 = New Form_Orders
'Create the second instance Set frmTest2 = New Form_Orders
'Make both instances visible frmTest1.Visible = True frmTest2.Visible = True
The above code declares the global variables, instantiates them, and makes them visible. Before you run off and try to program your next stock market application, you should understand that this example presents limited usefulness, because it requires knowing how many instances are required in advance of execution. This is also known as hard-coding, which does not bode well in dynamic situations. If the number of times that the form needs to open is unknown (that is, the number of stocks that you would like to compare at once), then the hard-coded solution may include too many instances or not enough.
The next step in the multi-instance ladder is maintaining a collection of form objects, created on demand by the user. To understand the concept of a collection, think about a stamp, marble, or baseball card collection. You have a binder for stamps, bag for marbles, and a shoebox for cards. A collection is the empty container, awaiting items to be placed within it. The following code demonstrates the creation of the collection, and the addition of members to it:
'Create the global collection Dim colTest As New Collection
Private Sub cmdOpenForms_Click()
'Declare an object variable as an existing form Dim frmTest As Form_Orders
'Set the object to a new instance of the form Set frmTest = New Form_Orders
'Add the form instance to the collection colTest.Add frmTest frmTest.Visible = True
Each time the above code is executed a new instance of the form is added to the collection, and then the form is displayed to the user. This may accomplish your goal, but it is very important to understand that every instance that is created requires some amount of memory of the computer. If you continue to create instances, eventually all of the memory is allocated and strange but interesting error messages from Access and Windows are displayed. To avoid creating a form instance landfill inside the machine, proper housekeeping procedures must be implemented.
Housekeeping, with regard to VBA programming, is the proper destruction and reallocation of free memory throughout the life of the application. Just like in your home, if you open it, close it; if you get it dirty, clean it; if you turn it on, then turn it off. The same applies to created objects, because failing to close or destroy them can lead to memory leaks or other undesirable results.
So, if a form instance is closed by a user, then the memory allocated to the use of that object variable is still retained. The collection also maintains the form within its population as well. Therefore, the creator of the instance must make provisions to destroy the instance.
The ideal place to remove an object instance from the collection is within the On Close event procedure of the form instance. Just as the collection has an Add method, it also has a Remove method. It is used to destroy the instance from memory. In order to know which instance to destroy requires knowing where it is within the collection. Collection items can be referred to using an ordinal value, but it only relates to the sequential order in which the forms were instantiated. This is typically not useful when users are allowed to randomly open and close the instances (that is, user opens stock XYZ, ABC, and RRR. He or she decides against ABC and closes the form. It would be time then to destroy the ABC instance.)
To facilitate the specific reference of an instance, you may specify a unique index during the call to the Add method of the Collection object. The Key parameter is a string expression that can optionally be added for the direct reference to the instance within the collection. It is important to note that the supplied text value must be unique from any other Key value within the collection. Otherwise, destroying one instance would destroy all instances with the same name.
Specifying a unique string value presents an interesting challenge, as there needs to be some mechanism to create it. As each form opens, you need to determine something about that form instance that is unique from any other instance. In the case of the stock market example, the stock ticker symbol could be used. If motor vehicles are the basis of the form, then perhaps the VIN number could serve this purpose.
In the following example, multiple instances of an Order form are displayed. As most OrderID values are generated with an autonumber, this value is an obvious choice to use as unique index for the collection. To visually assist the user when multiple instances are opened, the caption can be modified to display the OrderID as well.
Public colOrders As New Collection Private Sub cmdShowOrder_Click() Dim frmOrder As Form_Orders Dim strOrderID As String 'Retrieve the desired OrderID strOrderID = Me!sfrmOrders.Form!txtOrderID 'Open a new Orders form Set frmOrder = New Form_Orders
'Set the form filter criteria frmOrder.Filter = "OrderID = " & strOrderID frmOrder.FilterOn = True
'Set the caption to reflect the unique id frmOrder.Caption = "Order: " & strOrderID
'Add the form instance to the Collection. ' Specify the Caption property for the 'Key' parameter ' The order number is used as the unique index colOrders.Add frmOrder, strOrderID
'Display the form instance frmOrder.Visible = True
The code example above creates the collection in the global declarations section. When the Show Order command button is clicked by the user, a new instance of the form is created in memory. On the subform is the desired OrderID to be displayed on the form. Therefore, when the form is opened, a filter is applied to show only that record. The caption is updated to visually enhance the user experience, by specifying the order that is being displayed. The instance is added to the collection, using the OrderID as the unique key index. Finally, the form instance is displayed. To better understand the use of this code, see Figure 10-6.
Figure 10-6 displays an order selection form. The user places the cursor on the desired record and then clicks the Show Orders button. Each time the button is clicked, a new instance of the form is opened.
The following code demonstrates the ability to destroy a specific instance of a form, within a collection, based on the unique Key value in the collection. For obvious reasons, the execution of this code is from the On Close event procedure of the form instance that is closing.
Private Sub Form_Close()
'Remove the instance of this form from the collection Form_dlgViewOrders.colOrders.Remove CStr(OrderID) End Sub
The above code example executes the Remove method of the colOrders collection. To specify the exact collection, the code module name must be supplied, so that there is no ambiguity as to which collection is to be used. In this case, the View Orders dialog box (dlgViewOrders) contains the collection. The Remove method is executed with the OrderID, converted to a string, and passed as the Key Index parameter.
Was this article helpful?