Validation “Macro”

Another interesting action, is the “CancelEvent” action.
With this action, we can verify that the entries satisfy certain conditions and when that is not the case, the event is cancelled.
For example:
If the “Company” field in the “Add Customer” form is left blank, “Access” will give us this warning and not add new record for a Customer until we have a value in the “Company” field .
For this, I first open the “Add Customer” form in the “Design View”.

Important:

No field in the form should be selected.
Click the “Property Sheet” button in the “Ribbon”.
In the upper section of the “Properties window”, select “Form”.
Select the “Before Update” field under the “Event” tab and click the button with three dots.
I choose the “Before Update” field, because this action forshould happen before the record is updated, that is checking if a certain condition is met.
In this case, it is entering a name in the company field.

When we clicked on the button with three dots, the “Choose Builder” window opens.
Select “Macro Builder” and click the OK button.
This opens the “Macro” window.

Click the “Conditions” in the “Macro” window.
Select the top field in the “Condition” column and click the “Builder” in the “Macro” window.
In the “Expression Builder” double-click the left part of the form in which the condition for the field has to be entered. In this case, it is the “Add Customer” form.
And in the middle part, double-click the field, in this case “Company”.
Then type: Is Null.
That is,
when the “Company” field , in the “Add Customer” form has no value.
This is the condition.
Click the OK button to return to the “Macro” window.

Select the top field in the “Action” column, click the downward pointing arrow and choose the action “CancelEvent”.
For the second action I want to open a window when the first condition is met i.e, when the “Company” field is empty.
So I type the three dots in the “Condition” column, so “Access” knows that it must do this after the first action.
And choose action as “Msg Box”.
For this action we can set 4 arguments.
The message that the user gets when he has not entered a company name .
Whether you want to hear a beep.
The type of the window, you can choose between four different windows, or no window.
And if you wish, type a title for this window here.

And last action I would like is, when nothing is entered in the “Company”, to move the cursor to that empty Company field.
So in the “Condition” column, I type the three dots again and in the “Action” column I choose “GoToControl”.

In the “Control Name” textbox in the “Action Argument” section, I type the control name in square brackets , the control where the cursor should go to.
In this case, this is the [Company] field.

Save the “Macro” by clicking the “Save” button in the “Quick Access” toolbar.
Close the “Macro” window and try in “Form view” once a new customer is entered without the “Company” name.
The alert window is displayed only when you click thea “New (blank) record” button, or on closing the form .

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats