Understanding how events trigger VBA code

You can create an event procedure that runs when a user performs any one of the many different events that Access recognizes. Access responds to events through special form and control properties. Reports have a similar set of events, tailored to the special needs and requirements of reports.

Figure 26.1 shows the Property Sheet for frmProducts. This form has many event properties. Each form section (page header, form header, detail, page footer, form footer) and every control on the form (labels, text boxes, check boxes, and option buttons, for example) has its own set of events.

In Figure 26.1, notice that the Property Sheet is open on the Event tab. Access forms include 50 events, and each form section includes a number of events, as well as each control on the form. As you select a form section or a control on the form, the Event tab in the Property Sheet changes to show you the events for that object.

In Figure 26.1, all the events with existing event procedures contain [Event Procedure], which indicates that the property has associated VBA code that executes whenever this event is triggered. The events may also contain [Embedded Macro], the name of a non-embedded macro, or the name of a function.

Creating event procedures

In Access, you execute event procedures through an object's event properties.

Access provides event properties you use to tie VBA code to an object's events. For example, the On Open property is associated with a form or report opening on the screen.

You add an event procedure to a form or report by selecting the event property (Before Update, for this example) in the object's Property Sheet. If no event procedure currently exists for the property, a drop-down arrow and builder button appear in the property's box, as shown in the Before Update event property in Figure 26.1.

The drop-down list exposes a list that contains the single item [Event Procedure]. Selecting this option and then clicking the builder button takes you to the VBA code editor with an event procedure template already in place (see Figure 26.2).

Notice the general format of the event procedure's declaration:

Private Sub Object_Event()

The Object portion of the procedure's name is, of course, the name of the object raising the event, while the Event portion is the specific event raised by the object. In Figure 26.2, the object is Form, and the event is BeforeUpdate. Some events support arguments, which appear within the parentheses at the end of the declaration.

In case you're wondering, you can't change the name or the arguments of an event procedure and expect it to continue working. Access VBA relies on the Object_Event naming convention to tie a procedure to an object's event.

Form event procedures

When working with forms, you can create event procedures based on events at the form level, the section level, or the control level. If you attach an event procedure to a form-level event, whenever the event occurs, the action takes effect against the form as a whole (such as when you move to another record or leave the form).

To have your form respond to an event, you write an event procedure and attach it to the event property in the form that recognizes the event. Many properties can be used to trigger event procedures at the form level.

Control event procedures

Controls also raise events. Control events are often used to manipulate the control's appearance or to validate data as the user makes changes to the control's contents. Control events also influence how the mouse and keyboard behave while the user works with the control. A control's BeforeUpdate event fires as soon as focus leaves the control (more precisely, BeforeUpdate fires just before data is transferred from the control to the recordset underlying the form, enabling you to cancel the event if data validation fails), whereas a form's BeforeUpdate does not fire until you move the form to another record. (The form's BeforeUpdate commits the entire record to the form's data source.)

This means that a control's BeforeUpdate is good for validating a single control while the form's BeforeUpdate is good for validating multiple controls on the form. The form's BeforeUpdate would be a good place to validate that values in two different controls are in agreement with each other (such as a zip code in one text box and the city in another text box), instead of relying on the BeforeUpdate in each of the controls.

You create event procedures for control events in exactly the same way you create procedures for form events. You select [Event Procedure] in the Property Sheet for the event, and then add VBA code to the event procedure attached to the event. Table 26.5 shows each control event property, the event it recognizes, and how it works. As you review the information in Table 26.5, keep in mind that not every control supports every type of event.

Report event procedures

Just as with forms, reports also use event procedures to respond to specific events. Access reports support events for the overall report itself and for each section in the report. Individual controls on Access reports do not raise events.

Attaching an event procedure to the report runs code whenever the report opens, closes, or prints. Each section in a report (header, footer, and so on) also includes events that run as the report is formatted or printed.

Several overall report event properties are available. Table 26.6 shows the Access report events. As you can see, the list of report events is much shorter than the form event list.

Even though users do not interact with reports as they do with forms, events still play a vital role in report design. Opening a report containing no data generally yields erroneous results. The report may display a title and no detail information. Or, it may display #error values for missing information. This situation can be a little scary for the user. Use the NoData event to inform the user that the report contains no data. NoData fires as a report opens and there is no data available in the report's RecordSource. Use the NoData event procedure to display a message box describing the situation to the user and then cancel the report's opening. Figure 26.3 shows a typical NoData event procedure.

The Report_NoData event illustrated in Figure 26.3 first displays a message box to advise the user that the report contains no data. Then the event procedure cancels the report's opening by setting the Cancel parameter to True. Because the Cancel parameter is set to True, the report never appears on the screen and is not sent to the printer.

Many Access events are accompanied by parameters, such as the Cancel parameter you see in Figure 26.3. In this case, setting Cancel to True instructs Access to simply ignore the process that triggered the event and to prevent the triggering of subsequent events. Because NoData was triggered as part of the report's opening process, setting Cancel to True prevents the report from being sent to the printer or being displayed on the screen. You'll see many examples of event property procedure parameters throughout this book.

Report section event procedures

In addition to the event properties for the form itself, Access offers three specialized event properties to use with report sections. Table 26.7 shows each event and how it works.

Use the Format event to apply special formatting to controls within a section before the section is printed. Format is useful, for example, to hide controls you don't want to print because of some condition in the report's data. The event procedure runs as Access lays out the section in memory but before the report is sent to the printer.

You can set the On Format and On Print event properties for any section of the report. However, On Retreat is not available for the page header or page footer sections. Figure 26.4 shows the Property Sheet's event tab for a report. Notice that the drop-down list at the top of the Property Sheet shows that the report is selected, so the events in the Event tab relate to the report itself and not an individual control on the report.

In addition to the NoData event, other report events are frequently programmed. Figure 26.5 shows how to add code to a report section's Format event to control the visibility of controls on the report.

The Detail0_Format event procedure illustrated in Figure 26.5 first checks the value of the txtQtyInStock control. If the value of txtQtyInStock is less than 10, lblLowStock is displayed; otherwise, the warning control is hidden.

You'll see many examples of using events and event procedures to manipulate forms, reports, and controls throughout this book.

Looking at common event sequences

Here are the sequences of events for the most frequently encountered form scenarios:

• Opening and closing forms:
  • When a form opens: Open (form) → Load (form) → Resize (form) → Activate (form) → Current (form) → Enter (control) → GotFocus (control)
  • When a form closes: Exit (control) → LostFocus (control) → Unload (form) → Deactivate (form) → Close (form)
• Changes in focus:
  • When the focus moves from one form to another: Deactivate (form1) → Activate (form2)
  • When the focus moves to a control on a form: Enter → GotFocus
  • When the focus leaves a form control: Exit → LostFocus
  • When the focus moves from control1 to control2: Exit (control1) → LostFocus (control1) → Enter (control2) → GotFocus (control2)
  • When the focus leaves the record in which data has changed, but before entering the next record: BeforeUpdate (form) → AfterUpdate (form) → Exit (control) → LostFocus (control) → Current (form)
  • When the focus moves to an existing record in Form view: BeforeUpdate (form) → AfterUpdate (form) → Current (form)
• Changes to data:
  • When data is entered or changed in a form control and the focus is moved to another control: BeforeUpdate → AfterUpdate → Exit → LostFocus
  • When the user presses and releases a key while a form control has the focus: KeyDown → KeyPress → KeyUp
  • When text changes in a text box or in the text-box portion of a combo box: KeyDown → KeyPress → Change → KeyUp
  • When a value that is not present in the drop-down list is entered into a combo box's text area: KeyDown → KeyPress → Change → KeyUp → NotInList → Error
  • When data in a control is changed and the user presses Tab to move to the next control:
    • Control1: KeyDown → BeforeUpdate → AfterUpdate → Exit → LostFocus
    • Control2: Enter → GotFocus → KeyPress → KeyUp
  • When a form opens and data in a control changes: Current (form) → Enter (control) → GotFocus (control) → BeforeUpdate (control) → AfterUpdate (control)
  • When a record is deleted: Delete → BeforeDelConfirm → AfterDelConfirm
  • When the focus moves to a new blank record on a form and a new record is created when the user types in a control: Current (form) → Enter (control) → GotFocus (control) → BeforeInsert (form) → AfterInsert (form)
• Mouse events:
  • When the user presses and releases (clicks) a mouse button while the mouse pointer is on a form control: MouseDown → MouseUp → Click
  • When the user moves the focus from one control to another by clicking the second control:
    • Control1: Exit → LostFocus
    • Control2: Enter → GotFocus → MouseDown → MouseUp → Click
  • When the user double-clicks a control other than a command button: MouseDown → MouseUp → Click → DblClick → MouseUp

Writing simple form and control event procedures

Writing simple procedures to verify a form or control's event sequence is quite easy. Use the preceding information to determine which event should be harnessed in your application. Unexpected behavior often can be traced to an event procedure attached to an event that occurs too late—or too early!—to capture the information that is needed by the application.

The Chapter26.accdb example database includes a form named frmEventLogger that prints every event for a command button, a text box, and a toggle button in the Debug window. The form is not bound to a recordset, so the list of events will be slightly different than for a bound form. It is provided to demonstrate just how many Access events are triggered by minor actions. For example, clicking the command button one time, and then tabbing to the text box and pressing one key on the keyboard fires the following events:

• cmdButton_MouseDown
• cmdButton_MouseUp
• cmdButton_Click
• cmdButton_KeyDown
• cmdButton_Exit
• cmdButton_LostFocus
• txtText1_Enter
• txtText1_GotFocus
• txtText1_KeyPress
• txtText1_KeyPress
• txtText1_KeyUp
• txtText1_KeyDown
• txtText1_KeyPress
• txtText1_Change
• txtText1_KeyUp

You'll have to open the code editor and display the Immediate window to see these events displayed. From anywhere in the Access environment, press Ctrl+G and the code editor instantly opens with the Immediate window displayed. Then, Alt+Tab back to the main Access screen, open the form, and click on the various controls and type something into the text box. You'll see a long list of event messages when you use Ctrl+G to return to the Immediate window.

Obviously, these are far more events than you'll ever want to program. Notice that, on the command button, both the MouseDown and MouseUp events fire before the Click event. Also, a KeyDown event occurs as the Tab key is pushed, and then the command button's Exit event fires before its LostFocus event. (The focus, of course, moves off the command button to the text box as the Tab key is pressed.)

Also, notice that the text box raises more than one KeyPress event. The first is the KeyPress from the Tab key, and the second is the KeyPress that occurs as a character on the keyboard is pressed. Although it may seem strange that the Tab key's KeyPress event is caught by a text box and not by the command button, it makes sense when you consider what is happening under the surface. The Tab key is a directive to move the focus to the next control in the tab sequence. Access actually moves the focus before passing the KeyPress event to the controls on the form. This means that the focus moves to the text box, and the text box receives the KeyPress raised by the Tab key.

Keep in mind that you write code only for events that are meaningful to your application. Any event that does not contain code is ignored by Access and has no effect on the application.

Also, it's entirely likely that you'll occasionally program the wrong event for a particular task. You may, for example, be tempted to change the control's appearance by adding code to a control's Enter event. (Many developers change a control's BackColor or ForeColor to make it easy for the user to see which control has the focus.) You'll soon discover that the Enter event is an unreliable indicator of when a control has gained focus. The GotFocus and LostFocus events are specifically provided for the purpose of controlling the user interface, while the Enter and Exit events are more “conceptual” in nature and are not often programmed in Access applications.

This small example helps explain, perhaps, why Access supports so many different events. Microsoft has carefully designed Access to handle different categories of events, such as data or user-interface tasks. These events provide you with a rich programming environment. You'll almost always find exactly the right control, event, or programming trick to get Access to do what you need.

Opening a form with an event procedure

Most applications require multiple forms and reports to accomplish the application's business functions. Instead of requiring the users of the application to browse the database container to determine which forms and reports accomplish which tasks, an application generally provides a switchboard form to assist users in navigating throughout the application. The switchboard provides a set of command buttons labeled appropriately to suggest the purpose of the form or report it opens. Figure 26.6 shows the switchboard for the Collectible Mini Cars application.

The Collectible Mini Cars switchboard includes five command buttons. Each command button runs an event procedure when the button is clicked. The Products button (cmdProducts), for example, runs the event procedure that opens frmProducts. Figure 26.7 shows the Properties window for cmdProducts. Figure 26.8 shows the VBA code for the Click event of cmdProducts.

Running an event procedure when closing a form

Sometimes, you'll want to perform some action when you close or leave a form. For example, you might want Access to keep a log of everyone using the form, or you might want to close the form's Print dialog box every time a user closes the main form.

To automatically close frmDialogProductPrint every time frmProducts is closed, create an event procedure for the frmProducts Close event. Figure 26.9 shows this event procedure.

The Form_Close event illustrated in Figure 26.9 first checks to see if frmDialogProductPrint is open. If it is open, the statement to close it executes. Although trying to close a form that isn't currently open doesn't cause an error, it's a good idea to check to see if an object is available before performing an operation on the object.

Using an event procedure to confirm record deletion

Although you can use the Delete button on the Records group of the Home tab of the Ribbon to delete a record in a form, a better practice is to provide a Delete button on the form. A Delete button is more user-friendly because it provides a visual cue to the user as to how to delete a record. Plus, a command button affords more control over the delete process because you can include code to verify the deletion before it's actually processed. Or you might need to perform a referential integrity check to ensure that deleting the record doesn't cause a connection to the record from some other table in the database to be lost.

Use the MsgBox() function to confirm a deletion. cmdDelete's event procedure uses MsgBox() to confirm the deletion, as shown in Figure 26.10.

When the cmdDelete_Click() event procedure executes, Access displays a message box prompt, as shown in Figure 26.11. Notice that the message box includes two command buttons: Yes and No. Access displays the prompt and waits for the user to make a selection. The record is deleted only when the user confirms the deletion by clicking the Yes button.