Attaching Logic to Data Using Data Macros in Access 2010 Applications

Office Visual How To

Summary:  Learn how to use Microsoft Access 2010 to add support for data macros and add business rules using data macros.

Applies to: Access 2010 | Access Services | Office 2010

Published:  September 2010

Provided by:  Ken Getz, MCW Technologies, LLC

Overview

This Visual How To shows how to add support for denormalizing the Orders and Orders Details tables in the Northwind sample database, adding a column to the Orders table that maintains the total amount purchased in the order. Although denormalizing data is generally not optimal behavior, it can increase performance when you create reports.

Code It

Many data-handling procedures share common duties, varying only in the specific details, such as the particular primary key for the row that you want to modify. You could repeat the same steps in multiple data macros. However, if you can find common steps, you can extract those steps and put them in a named data macro that you can call from other data macros. To handle data events, you can create a named data macro, and then call it from a data event, or you can create a macro and attach it to a data event in a table. If you think ahead, you can often save work by creating named macros.

Given that the intention of the example is to keep a running total of the total amount in a given order, you must add a macro to the After Update, After Insert, and After Delete data events on the Order Details table─in each case, the macro updates the value of a new field in the Orders table, a Currency field named OrderTotal.

In addition, the Northwind sample database includes data in the Order Details table, and you need some way to roll up that data and initialize the OrderTotal field in the Orders table─this means that you must create a separate data macro that you can call to perform the initialization of the OrderTotal field. In this Visual How To, you start by creating the sample database, and then adding this stand-alone named macro first. Then, you will create a named macro that you can call from the After Update, After Insert, and After Delete events of the Order Details table.

Create the Sample Database

To create the sample database, use the following procedure.

To create the sample database

  1. Start Access 2010, click File and then click New.

  2. In the list of available templates, under Office.com Templates, select Samples.

  3. From the list of sample databases, select Northwind 2007.

  4. Select an appropriate path, and then click the Download button to download and install a clean copy of the sample database.

  5. If prompted, click the Enable Content button in the Message Bar to enable the VBA code in the sample database.

  6. Accept the default login ID in the Login Dialog form, and then click Login.

At this point, you should have downloaded and installed a fresh copy of the Northwind 2007 sample database, and it should be loaded in Access 2010. Close the default Home form.

Modify the Orders Table

To test the data macros, you must add a new field to the Orders table. To modify the Orders table, use the following procedure.

To modify the Orders table

  1. In the Navigation Pane, expand the drop-down at the top of the pane, and select Object Type from the list of available options.

  2. Expand the Tables tab, right-click the Orders table, and select Design View.

  3. At the bottom of the list of fields, in the Field Name column, enter OrderTotal. In the Data Type field, enter Currency.

  4. Save and close the designer.

Update the OrderTotal Field

Given the OrderTotal field in the Orders table, you must add a data macro that can initialize the value of the field, given the current set of rows in the Order Details table. The macro must iterate through all the rows in the Order table, and for each row, find the corresponding set of rows in the Order Details table. Given those rows, the macro must calculate the sum of the Quantity * [Unit Price] fields, and store this sum in the current row in the Orders table.

In the Navigation Pane, double-click the Orders table to open the table. In the ribbon, select the Table tab. Then in the Named Macros group, select Named Macro. Finally, select Create Named Macro (see Figure 1).

Figure 1. Create a named macro

Create a Named Macro

In the Macro designer, in the combo box, select ForEachRecord─this action enables you to iterate through all the rows of a data source. In the combo box next to For Each Record In, select Orders. Figure 2 shows the macro designer at this point.

Figure 2. Iterate through all the rows

Iterate through all the rows

In the combo box within the ForEachRecord area, select SetLocalVar, and create a variable named varTotalPurchased with a value of 0, as shown in Figure 3.

Figure 3. Create a local variable that will tally the results

Create a local variable that will tally the result

In the empty combo box within the ForEachRecord macro action, select ForEachRecord (so you can loop through all the rows of the order in the Order Details table). Enter values as shown in Figure 4 by using the Access IntelliSense as you type. Be aware of the Alias field─this value creates a named set of rows so that you can refer to the set of rows. The name is arbitrary.

Figure 4. Select the set of matching rows in the Order Details table

Select set of matching rows in Order Detail Table

Within the new ForEachRecord action, in the combo box, select SetLocalVar, and enter the expressions shown in Figure 5. This action accumulates the total purchased for each item in the Order Details table.

Figure 5. Accumulate the total for the order

Accumulate total for order

To complete the macro, and update the value in the Orders table, click to select the outermost ForEachRecord macro action. In the combo box at the bottom of the action, select EditRecord. Within the action, select SetField in the combo box. Set the Name text box to Orders.OrderTotal, and the Value text box to varTotalPurchased, as shown in Figure 6.

Figure 6. Set the value of the OrderTotal field using the local variable

Set value of OrderTotal field using local variable

Now, you have created a named data macro that iterates through all the rows in the Orders table. For each row in the table, the macro finds all the matching rows in the Order Details table, accumulates the product of the Quantity and Unit Price fields, collects the sum in a variable named varTotalPurchased, and finally copies the value of the local variable back into the OrderTotal field in the Orders table.

To create a macro to run the data macro

  1. In the Ribbon, click Save, name the macro UpdateTotals, and then click Close.

  2. To run the macro and initialize all the OrderTotal fields, in the Ribbon select Create, and in the Macros & Code group, select Macro.

  3. In the Add New Action dropdown menu, select RunDataMacro, then select the Orders.UpdateTotals macro, as shown in Figure 7, save the macro as UpdateTotals, and then click Run.

  4. After the macro has finished, select the Orders table, and verify that the OrderTotal field now contains the total amount purchased in the order.

You can create a totals query to verify the results.

Figure 7. Create a macro to run the data macro

Create a macro to run the data macro

Create the UpdateTotal Named Macro

Each of the data events (After Insert, After Update, and After Delete) must handle basically the same task─they must calculate the new amount to be added (or subtracted, in the case of the After Update and After Delete events) from the OrderTotal value, and then make the change. Because all the event macros require shared functionality, it makes sense to create a single named macro that you can call from each of the data events. To get started, open the Order Details table, and create a new named macro, as you did with the Orders table. This macro requires two parameters. Click the Create Parameter link two times, and supply values as shown in Figure 8.

Figure 8. Create macro parameters

Create macro parameters

In the combo box, select the LookupRecord action, and insert the parameters as shown in Figure 9. This action helps you find the row in the Orders table that matches the OrderID value that is passed to the macro.

Figure 9. Locate the correct order

Locate the correct order

Within the LookupRecord action, add an EditRecord action, and within that action, add a SetField action. Set the values for the action parameters as shown in Figure 10. These values set the Orders.OrderTotal field to its old value plus the amount passed to this macro.

Figure 10. Update the OrderTotal field

Update the OrderTotal field

In the ribbon, click Save, save the new named data macro as UpdateTotal, and then click Close.

Handle the After Insert Event

When you insert a new row into the Order Details table, you must add the Quantity * [Unit Price] value into the corresponding row in the Orders table. To handle this, first verify that the Order Details table is open and selected. In the ribbon, select the Table tab. Then, in the After Events group, select After Insert. This action creates a macro that Access will call after you insert a new row into the Order Details table. In the new macro, select the RunDataMacro action, and insert the parameters as shown in Figure 11. In the ribbon, click Close, and save the macro when you are prompted.

Figure 11. Create the After Insert data macro

Create the After Insert data macro

Handle the After Update Event

Handling the After Update event requires a bit more effort. You must subtract out the old value associated with the order detail and then add in the new value. Access provides the original values for each field as properties of the Old object in this data macro so that you can work with both the current values and the old values.

In the ribbon, click After Update to create the data macro for the event, and in the combo box, select If, and use the built-in Updated function to determine whether the Quantity or Unit Price fields were updated as part of the current update; if not, do not change the Orders table. Figure 12 shows the expression that you should enter in the macro designer.

Figure 12. Supply a conditional expression for the If macro action

Supply conditional expression for If macro action

Within the If macro action, call the RunDataMacro action, specifying parameters as shown in Figure 13. Be aware that this action subtracts the old value for the extended amount. You will add the extended amount of the current row in the next action.

Figure 13. Subtract the old extended amount of the current row

Subtract old extended amount of current row

Finish the macro by adding a final call to the RunDataMacro action and add the current extended amount (see Figure 14).

Figure 14. Add the extended amount of the current row

Add in extended amount of current row

In the ribbon, click Save and then click Close.

Handle the After Delete Event

If you delete an Order Detail row, you must subtract the old extended amount─as shown in the previous example. Repeat the steps for the previous section, selecting the After Delete event, and adding only a single call to the RunDataMacro action. When finished, the parameters will appear similar to Figure 15. Click Close, and save the macro when you are prompted.

Figure 15. Subtract the extended amount of the deleted row

Subtract extended amount of deleted row

Test the Macros

Be aware that the first order in the Orders table has an Order ID value of 30. Use this value to modify the Order Details table. Add a new row, specifying a product, a unit price, and a quantity. Verify that the OrderTotal field in the Orders table updates accordingly. Modify the row, and verify the result. Delete the row, and again verify the result. In each case, because of the data macros that you added, you should see the OrderTotal field updated correctly.

Read It

Access 2010 adds the ability to create macros that can be tied to data events in tables. This enables you to create trigger-like functionality without writing code. Because web databases limit your ability to include VBA code and aggregate queries, you can use data macros to replace event handlers or queries that could create totals. This example shows one type of data macro (aggregating data in a table event), but you can use data macros to encapsulate any kind of business logic. Using data macros relieves you of the need to write VBA code in forms and reports and because the macros are attached to table events, Access 2010 runs the macros whether you are working with the table directly, editing data in a query, or even displaying and editing data in a form. In addition, because web databases do not support VBA code, using data macros provides the only way to add trigger-like functionality.

Explore It

About the Author
Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).