Microsoft Access 2010 Bible

Chapter 11: Mastering VBA Data Types and Procedures

445

In Figure 11.13, notice that the Control Source property for txtAmount simply calls the

CalcExtendedPrice function. The call does not specify the module that contains the function.

Because CalcExtendedPrice was declared with the Public keyword, Access easily finds it

and passes the required arguments to it.

Tip
The CalcExtendedPrice example illustrates an important aspect of Access development: Add a public
function in a single location anywhere in the application’s code and use the function anywhere it’s needed. The
ability to reuse a procedure in multiple places reduces maintenance. Changing the single instance of the func-
tion is reflected everywhere the public procedure is used.

Creating a function to calculate sales tax

In the Collectible Mini Cars application, whenever you add a line item to a sales invoice, you spec-

ify whether the item is taxable. The sales form adds up the extended prices for all the taxable line

items to determine the sales tax for the sale. This total can then be multiplied by the tax rate to

determine the tax.

The Collectable Mini Cars sales form (frmSales) includes a text-box control for the tax amount.

You could simply create an expression for the control’s value such as:

=fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate

This expression references txtTaxableTotal in the subform (fSubSalesLineitems) and

multiplies it by the tax rate (txtTaxRate) from the main form (frmSales).

However, although this expression displays the tax amount, the expression entered into the txt-

TaxAmount control would make the txtTaxAmount control read-only because it contains an

expression. You wouldn’t be able to override the calculated amount if you wanted to. The tax

applied to a sale is one of the fields that needs to be changed once in a while for specific business

purposes.

Better than using a hard-coded expression is creating a function to calculate a value and then place

the value of the calculation in the control. This way, you can simply type over the calculated value

if needed.

You could enter the following lines of code at the end of the cboBuyerID_AfterUpdate event

code you created before. This way, each time you choose a new contact on the sales form, the tax

is recalculated after the contact’s tax rate is retrieved on the frmSales form.

txtTaxAmount = _

fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate

You could also add these lines of code to the AfterUpdate events behind the txtQuantity,

txtPrice, txtDiscountPercent, and chkTaxable controls. Each time the value in any of

these controls changes, the value of the tax is updated.