Calling a Procedure from an Event

At this stage of the game, your database contains a standard module that contains a Sub procedure named SkipLabels(). Because you haven't yet actually tied the SkipLabels procedure to any event, there's nothing in the database yet to take advantage of SkipLabels().

Recall that earlier in the chapter, I show a form with a control named LabelsToSkip (it stores the number of labels to be skipped over) as well as a Cancel and a Print button. If the user clicks Cancel, you just want Skip-LabelsForm to close without doing anything. If the user clicks the Print button, you want the form to call SkipLabels with the appropriate label report name and number of labels.



When you want an event procedure on a form to call a standard procedure, use the syntax

Call procedureName (arguments)

where procedureName is the name of the procedure to call, and arguments are values for whatever required arguments the procedure is expecting. SkipLabels() requires at least two arguments: the name of the labels report and the number of labels to skip. Here's how you could get the Print button in

SkipLabels form to call SkipLabels () when clicked:

1. Open SkipLabelsForm (or whatever form you created) in Design view and click the button that will call SkipLabels.

2. On the Event tab of the Properties sheet, click the On Click event for the Print button.

3. Click the Build button and choose Code Builder. You'll be taken to the VBA editor with the cursor inside the event procedure.

4. Type the following into the procedure:

Call SkipLabels("[YourReportName]", [LabelsToSkip].Value)

substituting YourReportName with the name of the report in your database that prints labels.

For example, if your database contains a report named Avery 8462 Labels, you'd type Call SkipLabels("Avery 8462 Labels", [LabelsTo-Skipj.Value) as shown in the second procedure, PrintBttn_Click() in Figure 8-9.

Figure 8-9:

PrintBttn _Click() procedure called the Skip-Labels Sub.

SKipLabelsSub FormSkipLabelsForm (Code)


|SpinLlpButton jciick


Option Compare Database

DoCmd.Close acForm, "SkipLabelsForm" End Sub


Call SkipLabels("Avery 8462 Labels", End Sub

[LabelsToSkip] .Value)


S ^

The first procedure in that figure — CancelBttn_Click() — in that class module just closes SkipLabelsForm without doing anything and is tied to the On Click event of the form's Cancel button.

The syntax for calling a custom VBA function from an Event procedure is =functionName (arguments), which is clearly different from calling a Sub procedure with Call procedureName (arguments). I talk more about custom functions in Chapter 11.

5. Choose FileOSave and Return to Microsoft Access from the VBA editor's menu bar.

The button's On Click event shows Event Procedure, as usual. Now you can close and save the form and then reopen it in Form view to try it out.

You'll be able to do some fancier things with SkipLabelsForm in later chapters. For example, you can allow the user to choose any one of several label formats, or you can let the user specify a filter condition by using simple options on a form. But for now, if you've gotten this far, you're doing great. You created a Sub procedure named SkipLabels() that you can easily drop into just about any database you create.



0 0

Post a comment