Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

446

Actually, better would be to place this statement in the AfterUpdate event of fsubSalesLin-

eitems. This way, the tax is recalculated each time a value is updated in any record of this form.

Because fsubSalesLineitems is displayed as a datasheet, the AfterUpdate event fires as

soon as the user moves to another line in fsubSalesLineitems.

Although you can use a simple expression that references controls on forms and subforms, this

technique only works behind the form containing the code. Suppose you also need to calculate tax

in other forms or in reports. There’s a better way than relying on a form.

This is an old developer’s expression: “Forms and reports lie; tables never lie.” This means that the

controls of a form or report often contain expressions, formats, and VBA code that may make a

value seem to be one thing when the table actually contains a completely different value. The table

containing the data is where the real values are stored and it’s where calculations and reports

should retrieve data from.

You can easily use VBA code to extract data from a table, use the data in a complex calculation,

and return the result to a control on a form, on a report, or to another section of code.

Figure 11.14 shows the completed CalcTax function.

FIGURE 11.14

The CalcTax function

The function is called from the AfterUpdate events behind the txtQuantity, txtPrice, or

txtDiscountPercent controls in the subform. The CalcExtendedPrice function calculates

the sum of the taxable line items from the tblSalesItems table. The SQLstatement com-

bined with a bit of ADO code to determine the total. The calculated total amount is then multi-

plied by the dblTaxPercent parameter to calculate the tax. The tax is set to the variable

CalcTax (the name of the expression).