Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

554

Adding data macros to a table is quite easy. In fact, an Access table doesn’t even have to be in

Design view — you can add data macros to a table displayed as a datasheet, if you like. The data

macros you construct for a table are in effect immediately, so you can easily work on a macro and

observe how well the macro works without compiling or switching between Design view and

Datasheet view.

For this example, let’s assume that the Collectible Mini Cars company uses a standard markup of

66.66 percent on its products. This means that a product’s wholesale cost is multiplied by 1.6666

to yield the default selling price of an item. Collectible Mini Cars has found that a 66.66 percent

markup provides the margin necessary for them to offer volume discounts, special sales, and sig-

nificant discounts to selected buyers while remaining profitable.

The default selling price can always be overridden by manually entering a new price in the

RetailPrice field. The 1.6666 just serves as the starting point for a product’s selling price.

The problem to be solved with a data macro is updating the retail price of a product any time the

product’s cost is changed. Although this could be done quite easily with code or a macro behind

Access forms, consider the issue if there were dozens of different forms where the product’s cost

might be changed. The same code or macro would have to be added in many different places, con-

tributing to development and maintenance costs. Also, there is always the chance that one or more

forms would not be updated should Collectible Mini Cars ever decide on a different approach for

setting the default retail price of its products.

Using a data macro attached directly to the Products table simplifies development and maintenance

of the application’s forms and reports. Because the business rule (multiplying cost by 1.6666) is

enforced at the data layer, every form, report, and query using the Products data benefits from the

data macro. Also, if the cost is ever changed by VBA code or a user interface macro, the selling

price is automatically updated appropriately.

With the Products table open in Datasheet view, click on table ribbon tab to show table events (see

Figure 15.1). Notice that five events — Before Change, Before Delete, After Insert, After Update,

and After Delete — are available to the table.

FIGURE 15.1

Every Access table includes five data-oriented events.

Table tab

Table events Named macro command