Using the Command Button Wizard is a great way to create most general-purpose command buttons on your forms. However, a command button created with the wizard may not do exactly what you want it to do. Rather than creating a command button from scratch, you can use the Command Button Wizard to create the button and an event procedure for its Click event, and then customize the way the button works by editing the event procedure. For example, you can use the Command Button Wizard to create a button that opens a form, and then you can edit the event procedure so that it opens the form for read-only access instead of for editing data.
The event procedure that the Command Button Wizard creates includes simple Visual Basic statements that perform the action you specify. In addition, the wizard adds a simple method of error handling to respond appropriately to run-time errors. If an error occurs, the error-handling code displays the Microsoft Access error message and then exits the event procedure. The following illustration shows the event procedure the Command Button Wizard creates for a command button that opens a form.
To change or enhance the way the button works, you edit the lines of code that are highlighted in the previous illustration. Unless you want to change the way the event procedure handles errors, you don’t need to edit any other lines in the procedure.
See Also For more information on error handling in Visual Basic, see Chapter 8, “Handling Run-Time Errors.”
You may have noticed that the Command Button Wizard’s event procedure uses a Dim statement to declare a variable that holds the name of the form the event procedure opens. A variable is a placeholder that temporarily stores data, such as the name of an object or the value in a field, while your procedure runs. Once you declare a variable and assign it a value, you can use the variable throughout your procedure to refer to the value.
' Creates a variable that can hold a string value.
Dim strDocName As String
strDocName = "ProductsPopup" ' Assigns the form name to it.
DoCmd.OpenForm strDocName ' Opens the ProductsPopup form.You don’t have to use a variable to open a form. The following line of code performs the exact same action as the previous three lines.
DoCmd.OpenForm "ProductsPopup"If you refer to a form only once in a procedure, it’s probably easiest to just use the form name directly, as in the preceding line of code. Use a variable when you want to perform more than one operation on an object. In these cases, using a variable makes your code easier to write and maintain. If you want to change the form that the procedure opens, for example, you just change the form name once in the statement that assigns it to the variable.
See Also For more information on creating and using variables, see Chapter 4, “Working with Variables, Data Types, and Constants.”
Customers sometimes ask Northwind sales representatives for further information about a product on their order. You can provide a command button on the Orders form to open a pop-up form that shows details about the product selected in the Orders subform.
Step One: Create the button that opens the form The Orders sample application includes a form named ProductsPopup that displays details about Northwind products. You can open the Orders form in Design view and use the Command Button Wizard to add a command button named Details that opens the ProductsPopup form. The Command Button Wizard creates the command button and writes an event procedure for the command button’s Click event.
However, the pop-up form displays details about a value on the subform, not the main form. Because the wizard can’t filter based on a subform value, you’ll need to modify the command button’s event procedure yourself so the pop-up form shows information from the correct product.
See Also For more information on creating the ProductsPopup form, see “Using Pop-up Forms and Dialog Boxes” later in this chapter.
Step Two: Open the event procedure In Design view, right-click the command button and then click Build Event on the shortcut menu. Microsoft Access opens the form module and displays the button’s Click event procedure.
Step Three: Change the code so it filters records You want to filter the records in the ProductsPopup form to show the record whose ProductID is equal to the ProductID of the current record in the Orders subform. To do this, you need to apply a filter to the form when you open it.
The Command Button Wizard automatically creates a variable in the Click event procedure that makes it easy for you to set a filter for the form you’re opening. The following line of code sets the strLinkCriteria variable to the appropriate filter string.
strLinkCriteria = "ProductID = Forms!Orders!OrdersSubform!ProductID"
Add this line of code to the Click event procedure that opens the form, as shown in the following example:
Dim strDocName As String
Dim strLinkCriteria As String
strDocName = "ProductsPopup"
strLinkCriteria = "ProductID = Forms!Orders!OrdersSubform!ProductID"
DoCmd.OpenForm strDocName, , , strLinkCriteria
This event procedure passes the filter string you added (strLinkCriteria) as the wherecondition argument of the OpenForm method that opens the form. Microsoft Access then automatically sets a filter on the ProductsPopup form and makes the form display the correct record.
Compile your edited code and test it to make sure it works as expected. Now when you click the command button on the Orders form, the ProductsPopup form opens, showing values in the record for the current product in the Orders subform.
Note When the Details_Click event procedure opens the ProductsPopup form, the ProductsPopup form becomes the active form. In addition to the code that opens the ProductsPopup form, the Details_Click event procedure contains code that moves the focus from the pop-up form to the Orders form, so that sales representatives can continue filling out the order without having to click the Orders form. This code uses the SetFocus method, first to set the focus to the Orders form, and then to set the focus to the Orders subform rather than to the Details command button (the last control on the form to have the focus). To see this code, open the Orders form in Design view. Right-click the Details command button, and then click Build Event on the shortcut menu.
You can keep the ProductsPopup form synchronized with the Orders form, so that when you move from record to record in the Orders subform, the ProductsPopup form always shows details about the current product in the subform. To do this, write an event procedure for the subform’s Current event that sets the pop-up form’s Filter property. (You can also do this by writing a macro you specify as the subform’s OnCurrent event property setting.)
To see this event procedure in the Orders sample application, look at the Form_Current event procedure for the Orders subform. Open the Orders subform in Design view, and then click Code on the View menu to open its form module. In the Object box, click Form, and then click Current in the Procedure box.
See Also For more information on keeping two forms synchronized, see “Synchronizing Records by Changing the Filter” later in this chapter.