Learn Access Now! Chapter 19 Previous Section in Chapter 20 Next Section in Chapter 20 Chapter 21
Chapter 20: Using Visual Basic for Applications
(This is section 5 of 6 in this chapter)
Attaching Your Procedure to a Form
You now know how to create procedures you can use in your database. You will now want to attach your procedure to a form so a user can click their mouse on a command button to execute the procedure. The steps you must perform to attach your procedure to a form are similar to those you used in Chapter 18 where you attached a macro to a form.
The first step is to load the form from which you want to use a subroutine. For example, display the form Design window for the Review Accounts form, as shown in Figure 20-8.
Figure 20-8 The form you want to use.
Select the Command button and display the Event tab of the Properties dialog box. Scroll through the properties until you see the event you want to trigger the VBA code. In this case, use the On Click event. If you use the pull-down arrow to the right of this property box, you will notice that you have two choices:
- Event procedure. This is a VBA procedure which you develop to run in response to this event.
- Display Ancillary. This is a macro stored in the Customers database. If you had other macros defined in the database, they would be listed, as well.
You want to run an event procedure, so make that selection and click your mouse on the Builder icon to the right of the property box. Access will then display the VBA Editor with a Module window specific to this Command button, as shown in Figure 20-9.
Figure 20-9 The Module window for the Command button.
If you click your mouse on the pull-down arrow to the right of the toolbar Object box, you will find that you can pick any other object in the form. If you click your mouse on the pull-down arrow to the right of the toolbar Procedures box, you will find you can pick a number of pre-defined procedures. Each of these procedures represents a specific event that can occur in relation to the object in the Object box.
With the current settings in the Procedure and Objects boxes, you will develop a procedure that VBA executes when someone clicks their mouse on the Change Reps object (which is the button on the form).
At this point, you need to develop a procedure that will ask the user for a ZIP Code and a new salesperson number. To do so, enter the subroutine shown in Figure 20-10.
Figure 20-10 The subroutine to call the ChangeSalesByZip function.
This procedure uses the InputBox function to display two questions to the user. The function first asks for a ZIP Code and then for a replacement salesperson ID. With this information available, the code invokes the ChangeSalesByZip function. When the function is complete, the code displays a message box to indicate the outcome of the change.
After you type in this subroutine, click your mouse on the Save tool and then close the VBA Editor. When you subsequently use the Review Accounts form, the Change Reps button will be active. If you click your mouse on it, you will see the first of the two questions, as shown in Figure 20-11.
Figure 20-11 Being prompted for a ZIP Code.
After you answer this question, the code will ask you for a new salesperson number. After you type in your response, your program will update the records in the table.
Learn Access Now! Chapter 19 Previous Section in Chapter 20 Next Section in Chapter 20 Chapter 21