MS-Access / Getting Started

---

Working with Named Data Macros

So far in this tutorial, you've been studying data macros attached to specific table events. Access 2010 also supports creating named data macros. A named data macro is a data macro attached to a table, but not to a specific event. Named data macros execute only when called from another data macro, a user interface macro, or from Visual Basic code. Logic that is in a named data macro can interact with data in any table, require parameters before executing, and return data to the calling data macro, user interface macro, or Visual Basic code procedure. You can attach named data macros to any local client table or web table. You could, for example, attach all named data macros to one table or attach different named data macros to different tables based on their purpose. The Back Office Software System sample web database includes more than a dozen named data macros attached to various tables. In the next sections, you'll explore some examples of these named data macros, as well as create a new named data macro.

Creating Named Data Macros

In the Back Office Software System sample data copy web database (BOSSDataCopy.accdb), a table called tblErrorLog is used to record any application errors that might occur during the operation of any client objects in this hybrid application. The database administrator can monitor this table for any errors occurring in the operation of the database and then analyze the information recorded in the fields for debugging purposes. The database administrator would like an easy method to delete all the records in this error table after examining the errors and fixing the underlying issues. We can create a named data macro for this purpose, which can then be called from other areas of the application. To create a new named data macro for this purpose, we'll attach it to the tblErrorLog table for easy reference. Open the BOSSDataCopy.accdb sample web database, and then open the tblErrorLog table in Datasheet view. Next, click the Table contextual ribbon tab under Table Tools. Finally, click the Named Macro button in the Named Macros group and then click the option called Create Named Macro.

Access opens the Logic Designer with an empty macro design surface. You'll notice one difference immediately on the macro design surface that you did not see when creating data macros attached to table events in the preceding sections. At the top of the macro design surface, you can see a section called Parameters. Named data macros allow you to create parameters, which you can use to pass information into the data macro. Creating parameters for named data macros is optional, but Access always displays the Parameters block at the top of the macro design surface whenever you are working with named data macros. (We'll explore parameters for named data macros later in this tutorial.) The list of program flow constructs, data blocks, and data actions that you can use in named data macros is the same for After events except with the addition of one more data action called SetReturnVar. (We'll discuss the SetReturnVar action later in this tutorial.)

Let's first add a Comment block to this named data macro to document its purpose. Drag a Comment block from the Action Catalog onto the macro design surface. Enter the following text into the new Comment block:

Loop through all records in the client error table and delete them.

To delete all the records in the tblErrorLog table, we need to use the ForEachRecord data block. Drag a ForEachRecord data block from the Action Catalog to beneath the Comment block, or select ForEachRecord from the Add New Action box at the bottom of the macro design surface. Access creates a new ForEachRecord block.

The ForEachRecord data block takes three arguments:

• For Each Record In Required argument. A name of a table, query, or SQL statement to look up a record in.
• Where Condition Optional argument. An expression that Access uses to select records from the table, query, or SQL statement.
• Alias Optional argument. A substitute or shorter name for the table.

You'll notice in the lower-right corner of the ForEachRecord data block there is an Update Parameters link. If you are using a query for your data source that includes parameters, you can update the parameters using this link. We'll discuss using parameters with named data macros later in this tutorial.

You cannot use the CreateRecord action inside a ForEachRecord block in Access 2010. This is a design limitation that prohibits you from creating many records at one time. You can, however, work around this limitation by using a combination of a ForEachRecord block and an After Update event.

Both the tblLaborPlanDetails and tblSchedule tables in the Back Office Software System web database have an After Update event that checks to see if a Boolean field in the table is True. If the field is True, the After Update event runs a CreateRecord action to create new records in the tblSchedule table. We use a named data macro to loop through the records that we want to copy in the tblSchedule or tblLaborPlanDetails tables and set those Boolean fields to True. The After Update events fire and create new records. We then execute another named macro to reset the Boolean field back to False for all the records that we previously updated. You can study the After Update events and named data macros attached to tblLaborPlanDetails and tblSchedule to see examples of this functionality.

Click the drop-down list on the For Each Record In argument and select the tblErrorLog table. We don't need to provide anything for the Where condition or Alias arguments because we don't need to restrict our logic to a subset of the records in the tblErrorLog table; we want to delete all the records. Now, tab into or click the Add New Action box just beneath the Alias argument and then select the DeleteRecord action from the box. Access creates a DeleteRecord action inside the ForEachRecord block.

The DeleteRecord action only has one argument-Alias-and it's an optional argument. We don't need to provide an alias for this example.

That's all the logic we need in this named data macro to delete all the records in the tblErrorLog table. When Access executes this named data macro, it comes into the ForEachRecord data block, finds the first record in the table if any exist, and then deletes the record. Access then repeats the loop by going to the next record in the table and deleting that record as well. Access continues this process until it cannot find any more records in the tblErrorLog table. If there were actions following the ForEachRecord loop, Access would execute those actions after it deleted the last record in the table.