Automating Microsoft Access with VBA

 < Day Day Up > 

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.

     < Day Day Up > 
    Related

    Категории