Automating Microsoft Access with VBA

Combining controls into forms adds another layer of complexity. Forms have their own events, and these are sometimes interlaced with control events. By now, you should understand how to determine the order of events: just add code to print information to the Immediate window whenever an event occurs. So we dispense with the code here, and just explain the events. You can always write your own code to check the work here.

Navigation Events

When you open a form (for example, by calling the DoCmd.OpenForm method), five distinct events occur in this order:

1. Open

2. Load

3. Resize

4. Activate

5. Current

Of these events, the Open event marks the earliest point in the form's lifecycle where you can run code. The Load event occurs when the data is loaded into the form. The Resize event is fired every time a form is resized (including resizing from nonexistence to its initial size). The Activate event indicates that the form (or a control on the form) has the focus, whereas the Current event is fired whenever a new record is displayed in the form.

TIP

If you want to perform some operation for each record that the users navigate to, regardless of how they get there, put your code in the Current event handler.

Closing a form leads to a simpler sequence of events:

1. Unload

2. Deactivate

3. Close

As you can probably guess by now, these three events represent unloading the data, removing the focus from the form, and finally closing the form.

If you move from one form to another, the Deactivate event for the first form is followed by the Activate event for the second form. Note that you won't see the other form events (Open, Load, Current, and so on) if all you're doing is switching focus between forms.

Data Events

Forms have a rich set of data events. After all, forms are where you usually add, edit, and delete records. Let's look at each of those sequences in turn. When you navigate to the empty record on a form and start typing, the very first keystroke triggers five events in order, not counting the keystroke events for the control that you learned about earlier in the chapter:

1. Current

2. Enter for the control

3. GotFocus for the control

4. BeforeInsert

5. AfterInsert

Note the before-and-after pattern of the last two events. The BeforeInsert event is a notification that a record is being created, and at this time the form contains the data that's about to be saved to the new record. You can modify this data during this event, before it is saved. The AfterInsert event is a notification that a record was successfully created, and it contains the data that was actually saved.

You've already seen the events that occur when you're editing data in a single control (BeforeUpdate and AfterUpdate for the control). As you move around a single record and edit data in various controls, each control in turn fires its BeforeUpdate and AfterUpdate events. Then, when you save the data from the record (either by selecting Records, Save Record, or by moving to another record), the BeforeUpdate and AfterUpdate events for the entire form are fired. As with the other before-and-after pairs, the first of these lets you modify the data before it is saved to the table, and the second lets you see the data that was saved.

When you delete an entire record, Access fires three events:

1. Delete

2. BeforeDelConfirm

3. AfterDelConfirm

Each of these events has a specific purpose, of course. The Delete event occurs when the user deletes a record (for example, by clicking on the record selector and then pressing the Del key), but before anything is deleted. The BeforeDelConfirm event occurs just before Access displays the dialog box asking the user to confirm the deletion. In this event, you can cancel the rest of the sequence, preventing the record from being deleted. The AfterDelConfirm event marks the end of the deletion sequence.

Behind the Scenes: Data Buffers

As you edit data on a form, the data is actually stored in several areas in memory (these areas are called buffers). Let's trace the life cycle of an edit, using Figure 11.2 as a reference. In this figure, the boxes show the buffers where data can be stored, and the arrows show the flow of data.

When you first navigate to a record on a form, the data for that record is pulled from the underlying table to the form. This data is stored in a buffer containing a single record. Each control on the form then displays data from that buffer. As soon as you start typing into a control, the data for that single control is pulled into a second buffer.

If you decide to tab to another control, the data from the control buffer is written back to the form buffer, triggering the control's BeforeUpdate and AfterUpdate events. Alternatively, you can press Esc, discarding your changes and wiping out the control buffer. In this case, the control's update events are not fired.

As you edit controls, the entire edited record is still held in the form-level buffer. None of the changes is written back to the database at this point. If you press Esc twice, the form-level buffer is simply discarded. On the other hand, if you save the record, the BeforeUpdate and AfterUpdate events for the form are fired, and the changes are written back to the underlying table.