Working with Multiple Form Instances

Finally, there are some things that you can do in VBA code that you simply cannot do from the Access user interface. One of these is to create more than one copy of the same form. As you learned in Chapter 8, a form object is just an instance of the corresponding form class. Like any other class, this class can be instantiated more than once. But the Access designers didn't choose to make this capability available to end users.

To review the code for creating a single instance of a form, see "Creating Your Own Objects," p. 124.

Creating multiple form instances in code is just an extension of the technique for creating a single form instance in code. For example, this code creates two instances of the Projects form:

 Sub CreateTwoForms()   ' Create and display two copies   ' of the Projects form   Dim frm1 As New Form_Projects   frm1.Visible = True   frm1.Move 0, 0   Dim frm2 As New Form_Projects   frm2.Visible = True   MsgBox "Click OK to continue" End Sub 

As you can see, the code uses two different form variables to represent the two forms. It also moves the first form to the upper-left corner of the work area (otherwise, the second form would exactly cover the first). Figure 10.7 shows the result of running this code.

Figure 10.7. Two instances of the same form open in Access.

Multiple form instances behave much like any other form: you can set their properties, navigate their data, and so on. Each instance is a separate member of the Forms collection. However, because you can only create multiple instances in VBA, you need to maintain a form variable for each instance to keep it visible onscreen.

For information on maintaining a variable beyond a single procedure, see "Measuring the Lifetime of a Variable or Constant," p. 136.

CASE STUDY: Working with Two Instances of the Same Form

This case study demonstrates a practical use of the capability to create multiple instances of forms, as well as runtime switching of record sources. In the TimeTrack application, the various forms are not well integrated. For example, if the user opens both the Clients and Projects forms, they each show all records in their respective tables. But suppose you want to see just the projects for the current client? You can handle that by basing the Projects form on a query that draws a parameter from the Clients form. As an added twist, though, the code in this case study enables you to open as many of these client-specific project forms as you like.

For starters, here's a query to grab the projects for the current client. To build this object, you can open a new query in Design view, switch to SQL view, and just type in the SQL:

 SELECT Projects.* FROM Projects WHERE (((Projects.ClientID)=[Forms]![Clients]![ClientID])) ORDER BY Projects.ProjectName; 

Save the query as qryprojectsForClient. The next step is to slightly modify the code behind the Projects form, by adding a new procedure:

 Public Sub LoadClient()   ' Called by the client form to change   ' the recordsource of this form   Me.RecordSource = "qryProjectsForClient"   Me.Refresh End Sub 

Remember, a public procedure of a class becomes a method of the class or in this case, of the form that's based on the class. This means that you can call the procedure from the Clients form, as you see in a moment.

The Refresh method of a form tells it to refresh its records from the database. This is useful any time you change the form's RecordSource property and want to show the new data.

With the changes to the Projects form saved, you can modify the Clients form. Add a new command button named cmdProject. Here's the code for this button:

 Dim arrProjectForms() As Form_Projects Dim intProjectFormsCount As Integer Private Sub cmdProject_Click()   ' Open the project form, showing   ' projects for the current client   ' Add a new form variable to the array   intProjectFormsCount = intProjectFormsCount + 1   ReDim Preserve arrProjectForms( _    1 To intProjectFormsCount)   ' Create the new form   Set arrProjectForms(intProjectFormsCount) = _    New Form_Projects   ' Tell it we're opening from here   arrProjectForms(intProjectFormsCount).LoadClient   ' And display the new instance   arrProjectForms(intProjectFormsCount).Visible = _    True End Sub 

The first two declaration statements go in the module's Declarations section. This code shows how you can keep track of an arbitrary number of form variables: just put them in an array. Each time the user clicks the button, the code creates a new instance of the form and puts it in the array for safekeeping. It then calls the LoadClient method of the new form to set its RecordSource, and makes it visible.

VBA array functions are covered in Chapter 7, "Working with Arrays," p. 105.

Thus, you can show one client on the Clients form and use cmdProjects to open a form showing the projects for just that client. Then, without closing the first projects form, you can move to a different client and repeat the procedure to see the projects for the new client. Figure 10.8 shows the result, with two different client-specific project forms already open.

Figure 10.8. Using multiple instances for child forms.

When you close the Clients form, the array goes out of scope. As a result, all the child Projects forms close too.