Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

513

The rsCustomers.Close statement near the end of the Form_AfterUpdate procedure closes

the recordset. Closing recordsets when you’re done with them is good practice. In Figure 13.15,

notice also that the Recordset object is explicitly set to nothing (Set rsCustomers =

Nothing) to clear the recordset from memory. Omitting this important step can lead to “memory

leaks” because ADO objects tend to persist in memory unless they’re explicitly set to Nothing and

discarded.

Updating a calculated control

In the frmSales example, the txtTaxAmount control displays the tax to collect at the time of

the sale. The tax amount’s value is not a simple calculation. The tax amount is determined by the

following items:

l The sum of the item amounts purchased that are taxable

l (^) The customer’s tax rate in effect on the sale date
l The value in txtOtherAmount and whether the txtOtherAmount is a taxable item
When the user changes information for the current sale, any one or all three of these factors can
change the tax amount. The tax amount must be recalculated whenever any of the following events
occur in the form:
l Adding or updating a line item
l (^) Deleting a line item
l Changing the buyer to another customer
l (^) Changing txtTaxLocation
l Changing txtOtherAmount
You use VBA procedures to recalculate the tax amount when any of these events occurs.
Recalculating a control when updating or adding a record
Figure 13.16 shows the code for adding or updating a line item on frmSales.
FIGURE 13.16
Recalculating a field after a form is updated