Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

508

Writing VBA Code to Update a Table

Updating data in a table by using a form is easy. You simply place controls on the form for the

fields of the table that you want to update. For example, Figure 13.14 shows frmSales. The con-

trols on frmSales update data in tblSales, tblSalesLineitems, and tblSalesPay-

ments. because these fields are directly bound to controls on frmSales.

FIGURE 13.14

Using a form to update data in tables

Sometimes, however, you want to update a field in a table that isn’t displayed on the form.

When information is entered in frmSales, for example, the field for the last sales date

(LastSalesDate) in tblCustomers should be updated to reflect the most recent date on

which the contact purchased a product. When you enter a new sale, the value for the

LastSalesDate field is the value of the txtSaleDate control on frmSales.

Because the contact’s last sales date refers to the txtSaleDate control on frmSales, you don’t

want the user to have to enter it twice. Theoretically, you could place the LastSalesDate field

as a calculated field that is updated after the user enters the Sale Date, but displaying this field

would be confusing and is irrelevant to the items for the current sale.

The best way to handle updating the LastSalesDate field in tblCustomers is to use a VBA

procedure. You can use VBA code to update individual fields in a record, add new records, or

delete records.