Responding to Form Events

Your code isn't limited to responding to events that happen in form controls. You can also write code that responds to things that happen to the form as a whole. Some common examples include writing code that executes as soon as a form opens or each time the user scrolls from one record to the next in a table. Things that happen to the event as a whole are form events.

You can see all the form events whenever you're designing a form in Design view. Choose Form from the drop-down list near the top of the Properties sheet (as shown in Figure 6-4) and then click the Events tab. The On Current event (also shown in Figure 6-4) occurs each time the user moves to another record in the form. To write a procedure that executes each time the On Current event occurs, click the On Current property and click the Build (ellipsis) button that appears to the right. In the Choose Builder dialog box, choose Code Builder and then click OK.

Form object selected

Figure 6-4:

Form events in the Properties sheet.

Form object selected

Figure 6-4:

Form events in the Properties sheet.

On current event

The VBA editor opens, and you see the form's class module in the Code window. The name of the event procedure that you created is Form_OnCurrent(). The word Form in this context means the entire form, and OnCurrent refers to the event. The lines that start and end the procedure look like these:

Private Sub Form_Current() End Sub

Any code that you place between those lines is executed each time the user scrolls to a new record in the form. As it turns out, this would be a handy addition to the Payment Methods example described earlier. Currently, only one event enables and disables credit card controls — changing the contents of the PaymentMethod control. The controls won't change when scrolling through records, even when they should.

To remedy the situation, you can use the same code that you used in the PaymentMethod_OnChange() procedure to enable and disable controls in the Form_Current() procedure. Listing 6-2 shows an example where the Form_Current() procedure moves the cursor to a control named PaymentMethod and then enables or disables credit card controls on the form based on the contents of the PaymentMethod control.

Listing 6-2: Form_Current() Procedure

Private Sub Form_Current()

'Move cursor to PaymentMethod field.


If [PaymentMethod] = "Credit Card" Then

'Enable controls for entering credit

card info.

CCType.Enabled = True

CCNameOnCard.Enabled = True

CCNumber.Enabled = True

CCExpireMonth.Enabled = True

CCExpireYear.Enabled = True


'Move the cursor to ShippingMethod co



'Disable controls for entering credit

card info.

CCType.Enabled = False

CCNameOnCard.Enabled = False

CCNumber.Enabled = False

CCExpireMonth.Enabled = False

CCExpireYear.Enabled = False

End If

End Sub

After writing the code and choosing Close and Return to Microsoft Access, the On Current event in the Properties sheet shows [Event Procedure]. To test the code, switch to Form view (assuming that the form was bound to a table that contains multiple records).

You don't need to study all the details of every event for every control. There's just too many of them. Here's a quick rundown of some of the more commonly used form events for executing VBA code:

^ Load Event (On Load): Occurs as soon as a form opens in Form view and displays the first record

^ On Current (Current): Occurs when the form is open in Form view and the user scrolls to a new record in the underlying table or query

^ After Insert Event (Afterlnsert): Occurs when the user adds a new record to the underlying table (but not when code or a macro adds a new record to the table)

^ Delete Event (Delete): Occurs as soon as a user deletes a record

^ On Close (Close): Occurs after a form is closed and cleared from the screen

The first-listed name (like Load Event) in the preceding list is the name as it appears in the Properties sheet. The second name (like On Load) is the VBA name that's added to the procedure name automatically when you tie code to an event. For example, as you can read earlier in this chapter, tying code to a form's On Current event creates a procedure named Form_Current(). If you create a procedure that executes as soon as a form loads, its name is Form_Load().

Keep in mind that the event to which you tie a procedure simply defines when the procedure runs. You define what the procedure does, when called, by writing the VBA code within the procedure.

0 0

Post a comment