Part II: Programming Microsoft Access
560
l (^) ForEachRecord: The ForEachRecord action is a looping construct. Given the name
of a table or query, ForEachRecord can perform an operation on every record in the
recordset. The action can be an update using the SetField action (described in the next
section), can copy data, or can perform a mathematical operation on the data in the
recordset. The ForEachRecord block has a macro action included within the block to
make it easy to specify the action you want this block to perform. And, you can stack mul-
tiple macro actions within the ForEachBlock to perform more-complex operations.
l LookupRecord: The LookupRecord action is quite simple and easy to understand.
LookupRecord returns a record found in a table and provides a macro action area for
specifying the actions you want to perform on the returned record.
Data actions
The next topic to discuss are the actions a data macro can take. You’ve already read that a data
macro consists of one or more actions that are executed as a single unit in response to a table
event. You need a good understanding of the variety of macro actions available to data macros.
Not all these actions are available to every table event. The BeforeChange and BeforeDelete
(described in the “Before events” section, later in this chapter) support only a subset of these
actions because many actions are computationally intensive (such as updating or adding records),
and the “before” events are meant to be very fast and lightweight.
Here are the data macro actions:
l (^) DeleteRecord: As its name implies, DeleteRecord deletes a record in a table (with-
out confirmation from the user). Obviously, DeleteRecord must be carefully used to
prevent deleting valuable data from the application. A typical use of DeleteRecord
would be as part of an archiving operation, where data is a table is copied into another
table (perhaps a linked SQL Server table) and then deleted from the current table.
l CancelRecordChange: EditRecord and CreateRecord both make irrevocable
changes to a record. CancelRecordChange, in conjunction with an If block, allows a
data macro to cancel the changes made by EditRecord and CreateRecord before the
changes are committed to the database.
l ExitForEachRecord: The ForEachRecord loops through a recordset returned from
a table or query, enabling the data macro to make changes to the recordset’s data or scan
the data for “interesting” values. There are many situations where a data macro may need
to escape from a ForEachRecord loop before it has run to the end of its recordset. For
example, consider a data macro that searches for a certain value in a table, and once the
value is found, there is no need to continue the loop. The ExitForEachRecord is typi-
cally executed as part of an If block (also discussed in the next section) and is executed
only when a certain condition is true.
l LogEvent: Every Access 2010 application includes a hidden USysApplicationLog
table (this table is hidden by virtue of the USys prefix in its name).
USysApplicationLog is used to record data macro errors and can be used to log other
information as well. The LogEvent macro action is specifically designed to add a record