Adding Event-Handling Code with the Command Button Wizard

Event-handling subprocedures represent the most common approach to handling events that are generated by control objects and Recordset objects bound to forms and reports. Command buttons are the most common control object to initiate user-generated events. The easiest way to create a simple VBA event-handling subprocedure is to add a command button to a form with the Command Button Wizard. The Command Button Wizard writes most or all the code for the most commonly used ...Click event handlers.

Designing a Button to Open Another Form

To add a command button and its associated event-handling code for opening the Customers form, follow these steps:

1. Open Northwind.mdb, if necessary, and open a new form in Design view.

2. Click the Toolbox button, if necessary, to display the Toolbox.

3. Make sure the Control Wizard toggle button is depressed, and then click the Command Button tool and add a small button on the form. The first dialog of the Command Button Wizard opens.

4. Select Form Operations in the Categories list and Open Form in the Actions list (see Figure 28.8). Selecting Form Operations displays a sample button with a small form icon. Click Next.

   Figure 28.8. Select the category and action for the command button in the first Command Button Wizard dialog.

   

5. Select Customers (or another form) in the What Form Would You Like the Command Button to Open? list (see Figure 28.9), and click Next.

   Figure 28.9. Select the name of the form to open in the second Wizard dialog.

   

6. If the form to open is bound to a table or query, you can allow the form to display all records or add a filter to display only a single record based on the value of a field in your new form. The next section describes how to apply a filter. For this example, accept the Open the Form and Show All the Records option (see Figure 28.10), and click Next.

   Figure 28.10. In the third Wizard dialog, select whether to apply a filter to the form's Recordset.

   

7. You can select from a variety of bitmapped icons for the button by marking the Show All Pictures check box. Alternatively, select the Text option button and type a caption such as Open Customers Form for the command button in the text box (see Figure 28.11). Click Next.

   Figure 28.11. The fourth Wizard dialog lets you select between a text caption or a bitmap image for the command button.

   

8. Replace the default Command0 with a name for the command button that's related to the purpose of the button, such as cmdOpenCustomersForm, in the text box (see Figure 28.12). The cmd prefix is the naming convention for command buttons. Click Finish to add the event-handling code to the class module for the new form and close the last Command Button Wizard dialog.

   Figure 28.12. The last Wizard dialog prompts you for the Name property of the new command button.

   

9. Click the Code button to display the class module for your new form. Figure 28.13 shows the subprocedure added to the module by the Control Wizard. Click the View Microsoft Access button to return to your new form in Design view.

   Figure 28.13. The Wizard generates the code shown here for the cmdOpenCustomers-Form command button you designed.

   

10. Click the Save button and save your form as frmCommandWiz or the like.

11. Close or minimize the Visual Basic Editor, change to Form view, and test the button's action.

The event-handling subprocedure you created in the preceding steps is bound to the On Click event of the cmdOpenCustomers button. In Form Design view, select the command button, click the Properties button on the toolbar to display the properties sheet for the button, and click the Event tab. The [Event Procedure] entry for the On Click event property specifies the cmdOpenCustomers_Click event handler. When you open the drop-down list for the On Click event, [Event Procedure] and the names of all the macros, if any, in your database appear (see Figure 28.14).

Figure 28.14. Select between a new or existing event-handling procedure or an existing Access macro to respond to an event.

Note

You can create a simple event-handling subprocedure stub for any event of an existing control by clicking the Builder button for the event and then double-clicking the Code Builder item in the Choose Builder list. In this case, it's up to you to fill in the code to handle the event. You can bypass the Choose Builder step and go directly to the Editor window by marking the Always Use Event Procedures check box in the Forms/Reports page of the Options dialog.

Adding a Button to Display a Specific Record on Another Form

The example of the preceding section simply opens the Customers form. In many cases, you need to open a form to display data based on the selected (current) record of the form with the command button frmCommandWiz from the preceding section for this example. Applying a filter to the Recordset of the form you want to open (Customers) requires a link between a field of the Record Source of frmCommandWiz and the corresponding field of the Customers form. For this example, the link is the CustomerID field.

To add a filter (WHERE) criterion as the value of the stLinkCriteria argument of the DoCmd.OpenForm method, do this:

1. With frmCommandWiz in Design view, select the form, open the Properties dialog, click the Data tab, and select the Orders table as the value of the Record Source property, which opens the table's field list.

2. Drag the OrderID and CustomerID fields to the form to add a text box and combo list.

3. Follow steps 2 5 of the preceding section.

4. In the third Wizard dialog, select the Open the Form and Find Specific Data to Display option. Click Next.

5. In the added Wizard dialog that didn't appear in the preceding section's steps, select CustomerID in the left and right lists (see Figure 28.15). Click Next.

   Figure 28.15. When you specify a filter on the Recordset of the form to open, this Command Button Wizard dialog lets you specify the field on which to link the two forms.

   

6. Continue with steps 7 11 of the preceding section, but specify Display Customer Data as the button caption, specify cmdDisplayCustomerData as the name of the button, and click Finish. Figure 28.16 shows the Customers form with a filter applied by the CustomerID value of the first Orders record.

   Figure 28.16. The Customers form displays the record specified by the frmCommandWiz form.

   

The Command Button Wizard generates the value of the filter argument and assigns it as the value of the stLinkCriteria variable with the following statement:

 stLinkCriteria = "[CustomerID]= " & " ' " & Me![CustomerID] & " ' " 

What the Wizard doesn't do is lock the opened form to prevent editing the customer data or adding a new record. To open the form in read-only mode, add acFormReadOnly as the last argument value of the OpenForm method:

 DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormReadOnly 

For more information on the syntax of the DoCmd.OpenForm method, see "Arguments of DoCmd Methods," p. 1212.

Tip

Don't let users change values of records in the source table(s) when you bind a navigation form to a table or query. Set the Allow Edits, Allow Deletions, and Allow Deletions properties on the Data page of the form's Properties window to No. Doing this prevents inadvertent changes to the Orders table by selecting a another customer in the CustomerID combo box. Another way to prevent users from making changes in combo boxes is to set the List Rows property value to 1 on the Format page.

Note

The frmCommandWiz form is included in the Events28.mdb sample database located in the \Seua11\Chaptr28 folder of the accompanying CD-ROM.