Office 2010 - Access

Lesson 68: Data Macros

68/76 Lessons 

Data Macros

is a new feature in “Access” 2010 which allows us to perform an action when you add, delete or update a record.

“Data Macros” are not listed under the “Macros” object-type in the “Navigation Pane” but, are stored in the table. In earlier versions of “Access” you could also do this but, everything had to happen in the “Properties window” of the object, in a form or report. Now, all of this can be done in the table directly.

An example:

What I want in this example is, when I type the subject name in a ‘box’, I should automatically get the “Salary” for that subject.

Choose the “Table” tab in the “Ribbon” and click the “Before change” button.


This opens the “Macro Design” window.

We can add actions by double-clicking in the “Action Catalog” (1) or, by clicking the downward pointing arrow button (2) and choosing the action from the drop-down menu.

The action that we need in this example is, the IF action. That is, “IF” a certain value is met.

Then type in the “IF” criteria.

For example, if the “Subject” field equals “French”, something must be entered in the “Salary” box automatically.

As soon as you start typing the field name, “Access” will help you to show the available field names. Double-click this field name, that you wish to use in your criteria.

Type the equal sign (=) and type the condition, in this case “French” in quotation marks (“”)

Next, we specify the field we need to set, when this condition is met.

Type “field” in the search bar (1) of the window “Action Catalog” and “Access” will show you the possible actions.

Double-click the “Set Field” (2).

This action places the “Set Field” in the macro window (3).

In the upper section of the “Set Field”, type the name of the field to be filled. In this case, this is the “Salary” (1) field.

Again, “Access” will help you when you start typing.

In the “Value” (2) field, give the value that “Access” has to complete when the condition is met.

Everything is now inserted as for our first condition.

Click the text “Add Else” (3) if you want to set additional conditions.

In the example below I added another condition for the subject “Mathematics”.

You already know how do this. If this is not the case, read the above section again.

Close the “Macro” window and save it when you are prompted.

When you are in the table for the “Subject”column and type “French” or “Mathematics”, then the “Salary” will automatically be entered, even when you use this table in a form or report.


You've completed Lesson 68