IN THIS CHAPTER
When working with a database system, you may perform the same tasks repeatedly. Instead of doing the same steps each time, you can automate the process with VBA macros.
Database management systems continually grow as you add records to a form, build new queries, and create new reports. As the system grows, many of the database objects are saved for later use—for a weekly report or monthly update query, for example. You tend to create and perform many tasks repetitively. Every time you add contact records, you open the same form. Likewise, you print the same form letter for contacts that have purchased a vehicle in the past month.
You can add VBA code throughout your application to automate these tasks. The VBA language offers a full array of powerful commands for manipulating records in a table, controls on a form, or just about anything else. This chapter continues the previous chapters' discussions of working with procedures in forms, reports, and standard modules.
This chapter focuses on the Access event model, a vitally important aspect of Access development. As you'll see in this chapter, Access provides a wide variety of events to trigger your code in response to user actions.
An Access event is the result or consequence of some user action. An Access event occurs when a user moves from one record to another in a form, closes a report, or clicks a command button on a form. Even moving the mouse generates a continuous stream of events.
Access applications are event driven, and Access objects respond to many types of events. Access events are hooked into specific object properties. For example, checking or unchecking a check box triggers a MouseDown
, a MouseUp
, and a Click
event. These events are hooked into the check box through the OnMouseDown
, OnMouseUp
, and OnClick
properties, respectively. You use VBA to compose event procedures that run whenever the user clicks the check box.
Access events can be categorized into seven groups:
In all, Access supports more than 50 different events that can be harnessed through VBA event procedures.
Of these types of events, by far the most common are the keyboard and mouse events on forms. As you'll see in the following sections, forms and most controls recognize keyboard and mouse events. In fact, exactly the same keyboard and mouse events are recognized by forms and controls. The code you write for a mouse-click event on a command button is exactly the same sort of code that you might write for the mouse-click on a form.
In addition, most Access object types have their own unique events. The following sections discuss the most commonly programmed events, but Microsoft has a habit of introducing new event capabilities with each new version of Access. Also, many ActiveX controls you might use in your Access applications may have their own unique and special events. When using an unfamiliar control or a new type of object in your Access applications, be sure to check out what events and properties are supported by the control or object.
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.
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.
Certain events are raised by many different Access objects. Microsoft has taken great care that these events behave exactly the same way, regardless of the object raising them. Table 26.1 lists several of the events most commonly used by Access developers. Most of these events apply to forms and all the different controls you might add to an Access form.
Table 26.1 Events Common to Multiple Object Types
Event | Event Type | When the Event Is Triggered |
Click |
Mouse | When the user presses and releases (clicks) the left mouse button on an object |
DblClick |
Mouse | When the user presses and releases (clicks) the left mouse button twice on an object |
MouseDown |
Mouse | When the user presses the mouse button while the pointer is on an object |
MouseMove |
Mouse | When the user moves the mouse pointer over an object |
MouseUp |
Mouse | When the user releases a pressed mouse button while the pointer is on an object |
MouseWheel |
Mouse | When the user spins the mouse wheel |
KeyDown |
Keyboard | When the user presses any key on the keyboard when the object has focus or when the user uses a SendKeys macro action |
KeyUp |
Keyboard | When the user releases a pressed key or immediately after the user uses a SendKeys macro action |
KeyPress |
Keyboard | When the user presses and releases a key on an object that has the focus or when the user uses a SendKeys macro action |
Not surprisingly, these events are all associated with the mouse and the keyboard because these are the user's primary means of inputting information and giving directions to an application. Not every object responds to every one of these events, but when an object responds to any of these events, the event exhibits exactly the same behavior.
Access supports many, many different events. In fact, one of Access's fundamental strengths is the wide variety of events available to developers. You can control virtually every aspect of an Access application's behavior and data management through event procedures. Although Microsoft makes no formal distinction between types of events, the following sections categorize events and event procedures into groups based on the type of object (forms, reports, and so on) that raise the events within the group.
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.
Access forms respond to many, many events. You'll never write code for most of these events, because of their specialized nature. There are, however, some events that you'll program over and over again in your Access applications. Table 26.2 lists some of the most fundamental and important Access form events. Not coincidentally, these are also the most commonly programmed Access form events.
Table 26.2 Essential Form Events
Event | When the Event Is Triggered |
Open |
When a form is opened, but the first record is not yet displayed. |
Load |
When a form is loaded into memory. |
Resize |
When the size of a form changes. |
Unload |
When a form is closed and the records unload, but before the form is removed from the screen. |
Close |
When a form is closed and removed from the screen. |
Activate |
When an open form receives the focus, becoming the active window. |
Deactivate |
When a different window becomes the active window, but before it loses focus. |
GotFocus |
When a form with no active or enabled controls receives the focus. |
LostFocus |
When a form loses the focus. |
Timer |
When a specified time interval passes. The interval (in milliseconds) is specified by the Timer Interval property. |
BeforeScreenTip |
When a screen tip is activated. |
Table 26.2 lists a number of events that deal with the form as a whole, such as when it's opened or closed.
Access forms also respond to a number of mouse and keyboard events, as shown in Table 26.3.
Table 26.3 Form Mouse and Keyboard Events
Event | When the Event Is Triggered |
Click |
When the user presses and releases (clicks) the left mouse button |
DblClick |
When the user presses and releases (clicks) the left mouse button twice on a form |
MouseDown |
When the user presses the mouse button while the pointer is on a form |
MouseMove |
When the user moves the mouse pointer over an area of a form |
MouseUp |
When the user releases a pressed mouse button while the pointer is on a form |
MouseWheel |
When the user spins the mouse wheel |
KeyDown |
When the user presses any key on the keyboard when a form has focus or when the user uses a SendKeys macro action |
KeyUp |
When the user releases a pressed key or immediately after the user uses a SendKeys macro action |
KeyPress |
When the user presses and releases a key on a form that has the focus or when the user uses a SendKeys macro |
In addition, the Key Preview property is closely related to form keyboard events. This property (which is found only in forms) instructs Access to allow the form to see keyboard events before the controls on the form. By default, the controls on an Access form receive events before the form. For example, when you click a button on a form, the button—not the form—sees the click, even though the form supports a Click
event. This means that a form's controls mask key events from the form, and the form can never respond to those events. You must set the Key Preview property to Yes
(true) before the form responds to any of the key events (KeyDown
, KeyUp
, and so on).
The primary purpose of Access forms is to display data. Not surprisingly then, Access forms have a number of events that are directly related to a form's data management. You'll see these events programmed over and over again in this book, and you'll encounter event procedures written for these events virtually every time you work on an Access application. These events are summarized in Table 26.4.
Table 26.4 Form Data Events
Event | When the Event Is Triggered |
Current |
When you move to a different record and make it the current record |
BeforeInsert |
After data is first entered into a new record, but before the record is actually created |
AfterInsert |
After the new record is added to the table |
BeforeUpdate |
Before changed data is updated in a record |
AfterUpdate |
After changed data is updated in a record |
Dirty |
When a record is modified |
Undo |
When a user has returned a form to a clean state (the record has been set back to an unmodified state); the opposite of OnDirty |
Delete |
When a record is deleted, but before the deletion takes place |
BeforeDelConfirm |
Just before Access displays the Delete Confirm dialog box |
AfterDelConfirm |
After the Delete Confirm dialog box closes and confirmation has happened |
Error |
When a runtime error is produced |
Filter |
When a filter has been specified, but before it is applied |
ApplyFilter |
After a filter is applied to a form |
The Current
event fires just after the data on a form is refreshed. Most often this occurs as the user moves the form to a different record in the recordset underlying the form. The Current
event is often used to perform calculations based on the form's data or to format controls. For example, if a certain numeric or date value is outside an expected range, the Current
event can be used to change the text box's Back Color property so the user notices the issue.
The BeforeInsert
and AfterInsert
events are related to transferring a new record from the form to an underlying data source. BeforeInsert
fires as Access is about to transfer the data, and AfterInsert
is triggered after the record is committed to the data source. For example, you could use these events to perform a logging operation that keeps track of additions to a table.
The BeforeUpdate
and AfterUpdate
events are frequently used to validate data before it's sent to the underlying data source. As you'll see later in this chapter, many form controls also support BeforeUpdate
and AfterUpdate
. A control's update is triggered as soon as the data in the control is changed.
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.
Table 26.5 Control Events
Event | When the Event Is Triggered |
BeforeUpdate |
Before changed data in the control is updated to the underlying recordset |
AfterUpdate |
After changed data is transferred to the form's recordset |
Dirty |
When the contents of a control change |
Undo |
When the control is returned to a clean state |
Change |
When the contents of a text box change or a combo box's text changes |
Updated |
When an ActiveX object's data has been modified |
NotInList |
When a value that isn't in the list is entered into a combo box |
Enter |
Before a control receives the focus from another control |
Exit |
Just before the control loses focus to another control |
GotFocus |
When a nonactive or enabled control receives the focus |
LostFocus |
When a control loses the focus |
Click |
When the left mouse button is pressed and released (clicked) on a control |
DblClick |
When the left mouse button is pressed and released (clicked) twice on a control or label |
MouseDown |
When a mouse button is pressed while the pointer is on a control |
MouseMove |
When the mouse pointer is moved over a control |
MouseUp |
When a pressed mouse button is released while the pointer is on a control |
KeyDown |
When any key on the keyboard is pressed when a control has the focus or when a SendKeys macro action is used |
KeyPress |
When a key is pressed and released on a control that has the focus or when a SendKeys macro action is used |
KeyUp |
When a pressed key is released or immediately after a SendKeys macro is used |
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.
Table 26.6 Report Events
Event | When the Event Is Triggered |
Open |
When the report opens but before printing |
Close |
When the report closes and is removed from the screen |
Activate |
When the report receives the focus and becomes the active window |
Deactivate |
When a different window becomes active |
NoData |
When no data is passed to the report as it opens |
Page |
When the report changes pages |
Error |
When a runtime error is produced in Access |
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.
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.
Table 26.7 Report Section Events
Event | When the Event Is Triggered |
Format |
When the section is pre-formatted in memory before being sent to the printer. This is your opportunity to apply special formatting to controls within the section. |
Print |
As the section is sent to the printer. It is too late to format controls in a report section when the Print event fires. |
Retreat |
After the Format event but before the Print event. Occurs when Access has to back up past other sections on a page to perform multiple formatting passes. Retreat is included in all sections except headers and footers. |
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.
Sometimes even a fairly simple action on the part of the user raises multiple events in rapid succession. As an example, every time the user presses a key on the keyboard, the KeyDown
, KeyPress
, and KeyUp
events are raised, in that order. Similarly, clicking the left mouse button fires the MouseDown
and MouseUp
events, as well as a Click
event. It's your prerogative as a VBA developer to decide which events you program in your Access applications.
Events don't occur randomly. Events actually fire in a predictable fashion, depending on which control is raising the events. Sometimes the trickiest aspect of working with events is keeping track of the order in which events occur. It may not be intuitive, for example, that the Enter
event occurs before the GotFocus
event (see Table 26.5) or that the KeyDown
event occurs before the KeyPress
event (see Table 26.3).
Here are the sequences of events for the most frequently encountered form scenarios:
Open
(form) → Load
(form) → Resize
(form) → Activate
(form) → Current
(form) → Enter
(control) → GotFocus
(control)Exit
(control) → LostFocus
(control) → Unload
(form) → Deactivate
(form) → Close
(form)Deactivate
(form1) → Activate
(form2)Enter
→ GotFocus
Exit
→ LostFocus
Exit
(control1) → LostFocus
(control1) → Enter
(control2) → GotFocus
(control2)BeforeUpdate
(form) → AfterUpdate
(form) → Exit
(control) → LostFocus
(control) → Current
(form)BeforeUpdate
(form) → AfterUpdate
(form) → Current
(form)BeforeUpdate
→ AfterUpdate
→ Exit
→ LostFocus
KeyDown
→ KeyPress
→ KeyUp
KeyDown
→ KeyPress
→ Change
→ KeyUp
KeyDown
→ KeyPress
→ Change
→ KeyUp
→ NotInList
→ Error
KeyDown
→ BeforeUpdate
→ AfterUpdate
→ Exit
→ LostFocus
Enter
→ GotFocus
→ KeyPress
→ KeyUp
Current
(form) → Enter
(control) → GotFocus
(control) → BeforeUpdate
(control) → AfterUpdate
(control)Delete
→ BeforeDelConfirm
→ AfterDelConfirm
Current
(form) → Enter
(control) → GotFocus
(control) → BeforeInsert
(form) → AfterInsert
(form)MouseDown
→ MouseUp
→ Click
Exit
→ LostFocus
Enter
→ GotFocus
→ MouseDown
→ MouseUp
→ Click
MouseDown
→ MouseUp
→ Click
→ DblClick
→ MouseUp
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.
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.
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.
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.