Validate Data by Using a Macro — Example
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 and postal code entered in a record on the form. If the postal code is not in the right format for the country, 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.
Click the action name to see the arguments for each action.
| Condition | Action | Comment |
|---|---|---|
| Attached to the BeforeUpdate property of the Suppliers form. | ||
| IsNull([Country]) | StopMacro | If Country is Null, postal code can't be validated |
| [Country] In ("France","Italy","Spain") And Len([Postal Code]) <> 5 | MsgBox | If the postal code isn't 5 characters, display a message and... |
| ... | CancelEvent | ... cancel the event. |
| GoToControl | ||
| [Country] In ("Australia","Singapore") And Len([Postal Code]) <> 4 | MsgBox | If the postal code isn't 4 characters, display a message and... |
| ... | CancelEvent | ... cancel the event. |
| GoToControl | ||
| ([Country] = "Canada") And ([Postal Code] Not Like"[A-Z][0-9][A-Z] [0-9][A-Z][0-9]") | MsgBox | If the postal code isn't correct for Canada, display a message and... |
| ... | CancelEvent | ... cancel the event. |