8.1. When Events Fire
Events are at the heart of event-driven programming—which is no surprise. What can be surprising to novice programmers is the sheer number of events available to use. They all beg to have some code behind them. In reality though, very few events are used on a consistent basis. Most of them have absolutely no code behind them, and never will in normal usage. The trick is to know which ones are important and commonly used, and which ones are obscure and hardly ever used. They all look equally important in Access Help.
8.1.1. Common Form Events
To cut to the chase, here's a list of commonly used events and how you might want to use them. If you know how to use this basic set of events, you're most of the way there to understanding event-driven programming in Access VBA.
Form—On Open: The Open event for a form fires before the Load event, and before the recordset is evaluated for the form. This means you can use this event to change the recordset (by changing the Where or Order By clause) before the form continues to load. This event can be cancelled, which means the form will not continue to the Load event.
Form—On Load: The Load event for a form fires after the recordset for the form has been evaluated, but before the form is displayed to the user. This offers you an opportunity to make calculations, set defaults, and change visual attributes based on the data from the recordset.
Form—Before Update: If you want to perform some data edits before the user's changes are updated in the database, this is the event to use. All the field values are available to you, so you can do multifield edits (such as HireDate must be greater than BirthDate). If something doesn't pass your validity checks, you can display a message box and cancel this event. This event also fires before a new record is inserted, so you can place edits for both new and changed records here.
Form—On Double Click: This is a strange one. If you build a continuous form to display records in a read-only index format, your users will expect to drill down to the detail of the record by double-clicking anywhere on the row. But what if they double-click the record selector (the gray arrow at the left side of each row)? The event that fires is the Form's double-click event. By using this event, you can run the code that opens your detail form. This gives your user a consistent experience and the confidence that your applications work no matter what they do.
Form—On Unload: This event can be used to check data validity before your form closes. It can be cancelled, which will redisplay your form without closing it.
Form—On Current: This is one of the most overused events by novice programmers, but it does have some good uses. It fires every time your form's "current" record changes, that's the one that the record selector (the gray arrow on the left side of each record) points to. It also fires when your form first loads and positions to the first record in your recordset. One good place to use On Current is on a continuous form where one of the buttons below is valid for some records but not for others. In the On Current event, you can test the current record and set the Enabled property of the button to True or False as appropriate. Because this event fires so often, it can be hard to control and cause performance issues. Use it sparingly.
Form—On Delete: This event fires after each record is deleted, but before the delete is actually finalized. This allows you to ask an Are You Sure message and cancel the delete of just that record if the user wants to. Use this in conjunction with the Before Delete Confirm event.
Form—Before Delete Confirm: This event fires before a group of deletes is finalized. If you cancel this event, none of the records are actually deleted. This event also has a Response parameter; it can be used to suppress the normal Access message asking the user if they want to delete the group of records.
8.1.2. Common Control Events
Here are some events on form controls (such as text boxes, combo boxes, command buttons, etc.) that are commonly used.
Control—On Click: This one is obvious; it fires when the control (most likely a command button) is clicked. This is where you put the code to run when the user clicks a button.
Control—Before Update: The Before Update event is very useful for controls that contain values, such as text boxes and combo boxes. It fires just before a change to the control is committed, so you have a chance to edit the new value of the field. If this event is cancelled, the control will revert to its previous value.
Control—After Update: This event fires after a change to the control is made. This is a handy time to control the next field to receive the focus or to update other fields in response to this one. This technique is explained in the section Cascading Combo Boxes in Chapter 14, "SQL and VBA."
Control—On Double Click: This event fires when a control is double-clicked. This is useful when you want to provide a method of drilling down to a detail form from a read-only index form. Make sure you add the code to open the detail form to every double-click event of every field in the detail section.
8.1.3. Common Report Events
Here are some events on reports that are commonly used. These events can run code to customize and display reports so that they are much more flexible for your users.
Report—On Open: This fires before the recordset is evaluated for the report. Just as with forms, you can use this event to change the recordset (by changing the Where or Order By clause) before the report continues to load. This can be especially useful when you use a form to prompt the user for selection criteria before the report continues to load. This technique is described in detail in Chapter 14. This event can be cancelled by setting the Cancel parameter to True, which will prevent the report from continuing to open.
Report—On Activate: This event fires after the On Open event, and just as the report window is displayed to the user. The main thing this event is used for is to maximize the Access windows using DoCmd.Maximize. This allows the user to see more of the report. However, you'll probably want to restore the Access windows to their previous sizes when the report closes, which brings us to the On Close event.
Report—On Close: As you might guess, this event fires when the report closes. A common line of code to include here is DoCmd.Restore to restore the sizes of your form windows that were maximized in the On Activate event.
Report—On No Data The On No Data event fires after the On Open event when the report evaluates the recordset and discovers that there are no records. This can easily happen if you allow your user to specify the criteria for the report and they choose a combination of values that doesn't exist in the database. In the On No Data event, you can display a message box to the user, then set the Cancel parameter to True, which will close the report.
8.1.4. Asynchronous Execution
Sometimes, Access runs two areas of your VBA code simultaneously, even though you've placed the code into different events or even in different forms and reports. This ability for Access to start running one procedure of code before another one is finished is called asynchronous execution. Most of the time asynchronous execution happens without you (or your user) really noticing, but it can sometimes cause problems, so you should know when it happens and what to do about it.
8.1.4.1. OpenForm
The most common asynchronous execution you'll encounter is when you open a form using the OpenForm command. Most of the time you won't notice it, but here's what really happens: When the OpenForm statement runs, the form you ask for starts to open, along with all of its Open, Load, and On Current events. However, your code after the OpenForm command also continues to run at the same time. Usually, not much happens at this point, so there's no harm done.
There are times, however, when you would like the execution of the code in the calling form to stop until the user is done with the form you open. This can happen when you are prompting the user for selection criteria during the Open event of a report (see Chapter 14), or when you open a form to add a new record from an index form.
In this latter case, you normally want to requery the index form to show the record that was just added, but you have to wait for the user to finish adding it. If you perform a requery right after the OpenForm, your code will continue merrily along and requery your first form, only within milliseconds after your second form has started to open. No matter how fast your user is, that's not enough time for them to add the new record. So your requery will run before the new record is added, and the new record will not appear on your index form.
There is a simple solution to the normal asynchronous execution of the OpenForm command. It's called Dialog Mode.
8.1.4.2. Dialog Mode to the Rescue
To prevent asynchronous execution when a form opens, use Dialog Mode. Instead of
DoCmd.OpenForm FormName:="frmMyForm"Specify Dialog Mode instead:
DoCmd.OpenForm FormName:="frmMyForm", windowmode:=acDialog
Dialog Mode accomplishes two things:
It opens the form in Modal Mode, which prevents the user from clicking on any other Access windows until they are done with this form.
It stops the execution of the calling code until the newly opened form is either closed or hidden.
This second feature of Dialog Mode is what is so helpful in preventing Access from trying to run two areas of your code at once.
Notice that the code stops until the form is closed or hidden. This is the basis for many clever uses of Dialog Mode where values from the called form are used elsewhere. If you just hide the form (by setting its Visible property to False), the values on the form are still there and ready for you to reference, even though the code in the calling form now continues to run. This is the technique for gathering selection criteria and building SQL statements, which is described in Chapter 14.