Access Cookbook

Carry Data Forward from Record to Record

Problem

You’d like to reduce the tedium of data entry by carrying forward selected values from one record to the next. Ideally, this feature will be user-selectable at runtime so that each user can indicate, on a control-by-control basis, whether the current value of a control should carry forward onto newly added records. Is there any way to implement this in Access?

Solution

There are two parts to this problem: the mechanics of carrying a value from one record to the next, and how best to let a user select which controls should carry forward values. The first part of the problem can be solved with a little VBA code to change the value of a control’s DefaultValue property at runtime, squirreling away the original DefaultValue, if one exists, in the control’s Tag property. The second part of the problem can be handled in a variety of ways; in this solution, we suggest using a small toggle button for each bound control that will offer the carry-forward feature.

To see an example, load the 09-05.MDB database and open the frmCustomer form in form view. Note that many of the text box controls have a small, captionless toggle button located just to their right. Navigate to the record of your choice and depress one or more of the toggle buttons to indicate that you wish to carry forward that text box’s value to newly added records (see Figure 9-16). Now jump to the end of the recordset and add a new record. (A quick way to accomplish this is ...