Database Reference
In-Depth Information
In this data macro, the For Each Record block specifies that actions in the block will be executed for
each record in the Part table. A block can contain one or more actions to perform the indicated task. In this
case, the block applies to each record in the Part table that satisfies a condition. The Where condition speci-
fies that the actions will be executed only for those records in the Part table whose part number matches the
part number on the new order line. Notice that Access adds square brackets around the table and field names
used in the Where condition. The alias
is an optional short name for the new record; it is common to
use the table name as the alias, which is how the alias is handled by Access in this macro.
The Edit Record action specifies the action to take on the specified record. In this case, the data macro
will edit the current record. The alias
“
Part
”
146
“
Part
”
is used again as a reference to the new record. It is a require-
ment for the aliases in a block to match.
Many actions require additional information, called arguments, to complete the action. If you select an
action that requires arguments, the arguments will appear along with the action and you can make any nec-
essary changes to them. The SetField action uses two arguments to change the contents of a field. The Name
argument includes the name of the field being changed, which is the OnOrder field in the Part table
(Part.OnOrder). The Value argument includes the expression for making the update, which is to add the value
in the OnOrder field in the Part table to the NumOrdered field in the new record in the OrderLine table
([Part].[OnOrder]
[OrderLine].[NumOrdered]).
The End EditRecord statement ends the EditRecord block. If you needed to change multiple fields on the
same order line, you could add additional SetField actions to the EditRecord block.
Figure 4-30 shows the data macro associated with the After Update event for the OrderLine table. It is
similar to the data macro for the After Insert event, but with one difference. The Value argument indicates
that the new value is the result of adding the current value of OnOrder ([Part].[OnOrder]) and the value of
NumOrdered ([OrderLine].[NumOrdered]) on the new order line, and then subtracting the old value of
NumOrdered ([Old].[NumOrdered]).
þ
Value of NumOrdered
before the update
New value (previous value
in OnOrder ield plus new value in
NumOrdered ield minus old
value in NumOrdered ield)
FIGURE 4-30
Macro Designer window for the After Update event associated with the OrderLine table
