Access VBA Macro Programming

You can view the events by right-clicking the form in Design mode and then clicking Build

Event in the pop-up. Click Code Builder and this will take you into the module for that form.

In the drop-down in the top left-hand corner of the module pane (that usually reads Detail),

select Form. In the drop-down in the right-hand corner of the module pane, you will be able

to access all the events. The following are the main events you are likely to use:

Activate

The Activate event happens when the user activates the form by putting the focus onto it. It is

not the same as the Open or Load event, since the form may already be displayed but does

not have the focus.

After/Before Delete Confirm

After Delete Confirm and Before Delete Confirm events are fired off before and after a user

deletes a record on the form. A Confirm message box is displayed automatically before the

record deletion is confirmed.

Strangely enough, the Delete Record icon is not shown in the standard ribbon when a

form is displayed. You need to customize the toolbar to show this, or write your own routine

using a command button on the form.

You can disable the warnings by using:

Docmd.SetWarnings False

You can then use these events to display your own warning messages if required and take

your own action:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

x = MsgBox("Are you sure that you wish to delete this record?")

If x = vbNo Then Cancel = True

End Sub

You can also use the Before event to validate the deletion. In one application I wrote, there

was a rule that the user could not delete the only record in a particular table. If there was only

one record left, a message had to be displayed and the action canceled.

If the user deletes a record in a particular table, you may also want to delete related records

in other tables that are orphaned by the first deletion. You can write code in the After Delete

Confirm event to do this. For example, if the user deletes an order, you would also want to

delete the order details that went with it.

After/Before Insert

After Insert and Before Insert events are fired off before and after an insert of a new record.

No warning message is provided, such as in the case of a deletion. So you may wish to

provide an “Are you sure message” on the Before Insert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

x = MsgBox("Are you sure that you wish to create a new record?")

If x = vbNo Then Cancel = True

End Sub

112 Microsoft Access 2010 VBA Macro Programming