Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

444

FIGURE 11.12

Testing the CalcExtendedPrice function in the Immediate window

The next task is to use the function to calculate the extended price (price multiplied by quantity)

of each item included in a sales invoice. You can add a call to the function from the Amount box

on fsubSalesLineItems. This is a subform embedded on frmSales. Follow these steps:

1. Display the frmSales form in Design view.


2. Click into the fsubSalesLineitems subform.


3. Click into the txtAmount control in the subform.


4. Display the Property window and enter the following into the Control Source property,
   as shown in Figure 11.13: =CalcExtendedPrice (Nz(txtQuantity,0),Nz(txtPrice,0),
   Nz(txtDiscountPercent,0)).

This expression passes the values from three controls — txtQuantity, txtPrice,
and txtDiscountPercent — in the subform to the CalcExtendedPrice function
in the module and returns the value back to the control source of the txtAmount con-
trol each time the line is recalculated or any of the parameters change. The references to
txtQuantity, txtPrice, and txtDiscountPercent are enclosed in calls to the
Nz function, which converts null values to zero. This is one way to avoid Invalid use
of null errors that would otherwise occur.

FIGURE 11.13

Adding a function call to the Control Source of a control

The sales form (frmSales) enforces a business rule that the extended price is always recalculated

any time the user changes the quantity, price, or discount on the sales form.