Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

514

A single event can handle recalculating the tax amount when new line items are added or when a

line item is changed — when an item’s price or quantity is changed, for example. In any case, you

can use the subform’s AfterUpdate event to update the sales tax. AfterUpdate occurs when a

new record is entered or when any value is changed for an existing record.

The Form_AfterUpdate procedure for fsubSalesLineItems executes when a line item is

added to the subform, or when any information is changed in a line item. The Form_

AfterUpdate procedure recalculates the tax amount control (txtTaxAmount) on frmSales.

The dblTaxRate variable holds the customer’s tax rate (the value of txtTaxRate on frm-

Sales) and curTaxAmount stores the value returned by the CalcTax() function. CalcTax()

calculates the actual tax amount. When the After_Update procedure calls CalcTax(), it

passes two parameters: the value of dblTaxRate and the current line item’s invoice number (Me.

InvoiceNumber). Figure 13.17 shows the CalcTax() function.

FIGURE 13.17

CalcTax() uses ADO to determine sales tax.

CalcTax() uses ADO syntax to create a recordset that sums the quantities and prices for the tax-

able items in tblSalesLineItems for the current sale. The function receives two parameters:

the tax rate (dblTaxPercent) and the invoice number (lngInvoiceNum). The function’s

return value is initially set to 0 (zero) at the top of the function. The ADO code checks to see if the

recordset returned a record. If the recordset is at the end of the field (EOF), the recordset did not

find any line items for the current sale — and CalcTax remains set to 0. If the recordset does

contain a record, the return value for CalcTax is set to the recordset’s TaxableAmount field

times the tax rate (dblTaxPercent).