Using Functions to Respond to Events

You can create your own Main Switchboard form by adding additional command buttons to the form for opening other forms. It's much more efficient, however, to use a single procedure to perform a set of identical tasks in which only the name of the form changes. Minimizing the amount of code in a form speeds opening of the form and minimizes the size of your database file.

Access lets you call a function and pass one or more parameter (argument) values to the function in response to events. A function (not a subprocedure) is required, despite the fact that Access disregards the return value, if the function returns a value. Control Wizards won't write the function code for you, nor will the Code Builder create a function stub. You must write the function yourself before calling it from an event. The OpenForms function in preceding Listing 28.1 is an example of using a function as an event handler.

You can easily change code written by the Command Button Wizard into a general-purpose function that opens any form whose name you pass as an argument. Figure 28.17 shows a simple modification of the cmdOpenCustomers_Click subprocedure (refer to Figure 28.13) to substitute a user-defined function for the event handler. When you replace Sub with Function in the first line, the VBA interpreter automatically changes Exit Sub to Exit Function and End Sub to End Function. You change the name of the function, add the strFormName parameter (the variable the Wizard adds to identify the form), pass the value of the strFormName parameter to the OpenForm action, and eliminate code that's not needed for the function, such as Dim stDocName As String.

Figure 28.17. A few changes to the code of the cmdOpenCustomers _Click subprocedure converts it to a function that opens the form that you specify in the On Click event property value of the command button.

Tip

If you don't change the name of the subprocedure or pass the cursor through the line containing the Function reserved word when converting from a subprocedure to a function, you receive a compile error. The VBA interpreter holds the existing subprocedure name in memory until the line is reinterpreted. Thus, creating a function of the same name results in a duplicate procedure name in the same class module if you press the Enter key when making the change. Duplicate procedure names aren't permitted within in the same module, nor are duplicate names of Public procedures permitted within the same project.

If you encounter compilation errors after changing the type or name of a function or procedure, see the "Calling Procedures and Functions in Class Modules" topic of the "Troubleshooting" section near the end of this chapter.

The syntax to enter in the event text box for executing a function is as follows:

 = FunctionName([Argument1[, Argument2[, ...]]) 

The arguments are optional, but unless you pass an argument value, such as a form name, there's no advantage to using a function call as an event handler. Arguments must be passed as literal values, such as "FormName" or a numeric value. Figure 28.18 shows the entry you type in the On Click text box to open the Customers form, =cmdOpenForm("Customers"). To add buttons to open other forms, copy the command button to the Clipboard, paste the copy to your form, and change the Customers caption to the name of the form you want to open.

Figure 28.18. Replacing a subprocedure with a function requires the explicit calling syntax shown here for the On Click event.