Tuesday, August 28, 2018

CancelEvent Macro Action

CancelEvent Macro Action

You can use the CancelEvent action to cancel the event that caused Access to run the macro containing this action. The macro name is the setting of an event property such as BeforeUpdate, OnOpen, OnUnload, or OnPrint.

Note: The CancelEvent macro action is not available in Access web apps.

Setting

The CancelEvent action doesn't have any arguments.

Remarks

In a form, you typically use the CancelEvent action in a validation macro with the BeforeUpdate event property. When a user enters data in a control or record, Access runs the macro before adding the data to the database. If the data fails the validation conditions in the macro, the CancelEvent action cancels the update process before it starts.

Often, you use this action with the MsgBox action to indicate that the data has failed the validation conditions and to provide helpful information about the kind of data that should be entered.

The following events can be canceled by the CancelEvent action.

ApplyFilter

Dirty

MouseDown

BeforeDelConfirm

Exit

NoData

BeforeInsert

Filter

Open

BeforeUpdate

Format

Print

DblClick

KeyPress

Unload

Delete


Note: You can use the CancelEvent action with the MouseDown event only to cancel the event that occurs when you right-click an object.

If a control's OnDblClick event property setting specifies a macro containing the CancelEvent action, the action cancels the DblClick event.

For events that can be canceled, the default behavior for the event (that is, what Access typically does when the event occurs) occurs after the macro for the event runs. This enables you to cancel the default behavior. For example, when you double-click a word that the insertion point is on in a text box, Access normally selects the word. You can cancel this default behavior in the macro for the DblClick event and perform some other action, such as opening a form containing information about the data in the text box. For events that can't be canceled, the default behavior occurs before the macro runs.

Note: If a form's OnUnload event property specifies a macro that carries out a CancelEvent action, you won't be able to close the form. You must either correct the condition that caused the CancelEvent action to be carried out or open the macro and delete the CancelEvent action. If the form is a modal form, you won't be able to open the macro.

To carry out the CancelEvent action in a Visual Basic for Applications (VBA) module, use the CancelEvent method of the DoCmd object.

Example

Validate data by using a macro

The following validation macro checks the postal codes entered in a Suppliers form. It shows the use of the StopMacro, MsgBox, CancelEvent, and GoToControl actions. A conditional expression checks the country/region and postal code entered in a record on the form. If the postal code is not in the right format for the country/region, the macro displays a message box and cancels saving the record. It then returns you to the Postal Code control, where you can correct the error. This macro should be attached to the BeforeUpdate property of the Suppliers form.

Condition

Action

Arguments: Setting

Comment

IsNull([CountryRegion])

StopMacro

If CountryRegion is Null, postal code can't be validated.

[CountryRegion] In ("France","Italy","Spain") And Len([Postal Code]) <> 5

MsgBox

Message: The postal code must be 5 characters.

Beep: Yes

Type: Information

Title: Postal Code Error

If the postal code isn't 5 characters, display a message.

...

CancelEvent

Cancel the event.

GoToControl

Control Name: PostalCode

[CountryRegion] In ("Australia","Singapore") And Len([Postal Code]) <> 4

MsgBox

Message: The postal code must be 4 characters.

Beep: Yes

Type: Information

Title: Postal Code Error

If the postal code isn't 4 characters, display a message.

...

CancelEvent

Cancel the event.

GoToControl

Control Name: PostalCode

([CountryRegion] = "Canada") And ([Postal Code] Not Like"[A-Z][0-9][A-Z] [0-9][A-Z][0-9]")

MsgBox

Message: The postal code is not valid. Example of Canadian code: H1J 1C3

Beep: Yes

Type: Information

Title: Postal Code Error

If the postal code isn't correct for Canada, display a message. (Example of Canadian code: H1J 1C3)

...

CancelEvent

Cancel the event.

1 comment:

  1. This is great! I hate Hotmail and was surfing the net looking for a solution and here it is. I took a lot away from this blog. Thank you!
    Google bellen

    ReplyDelete