Recipe 9.5 Carry Data Forward from Record to Record

9.5.1 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?

9.5.2 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 to click on the rightmost navigation button at the bottom of the form.) The values for the "toggled" text boxes carry forward onto the new record (see Figure 9-17). To turn off this feature for a control, click again on its toggle button to reset it to the unselected state.

Figure 9-16. The toggle buttons to the right of several text boxes have been depressed

Figure 9-17. The values of the "toggled" text boxes have been carried forward

To add this functionality to your own forms, follow these steps:

Table 9-2. Property settings for tglPhone on frmCustomer

Property	Value
Width	0.1"
Height	0.1667"
ControlTip	Carry forward Phone value to new records
Tag	txtPhone
OnClick	=acbCarry([Form], [Screen].[ActiveControl])

9.5.3 Discussion

Although there are other ways to offer this functionality to users, the toggle button control works best because it stays depressed to indicate its special state. If we had instead used a menu item or code attached to the bound control's double-click event to indicate that a control should be carried forward, users might find it difficult to remember which fields they had selected to carry forward.

Because the toggle button controls are small and do not visually call out their purpose, we added control tips to each button to identify them. Control tips are nice because they don't take up any room on the form until a user leaves the mouse cursor positioned over the control for a few moments.

The Tag property—an extra property that Access allows us to use any way we want—is used in two ways in this solution. First, the Tag property of each toggle button indicates which bound control it serves: for example, tglState's Tag property is set to txtState. Second, the Tag property of each bound control stores the existing DefaultValue property so we do not overwrite it when we carry a value forward: for example, txtState contains an existing DefaultValue of WA.

All the work for this solution is done by the acbCarry function. This function is attached to each toggle button's Click event using the following syntax:

=acbCarry([Form], [Screen].[ActiveControl])

Rather than passing strings to the function, we pass a reference to the form object and a reference to the active control object. Passing object references instead of the name of the form or control is efficient because back in the function, we will have immediate access to all the object's methods and properties without having to create form and control object variables.

The acbCarry function does its magic in several steps. First, it extracts the name of the bound control served by the toggle button from the toggle button's Tag property:

Set ctlData = frm(ctlToggle.Tag)

Second, the function checks whether the toggle is up or down: if it's depressed, its value will be True. This executes the following section of code, which stores the bound control's DefaultValue property in its Tag property and then sets the DefaultValue equal to the current value of the bound control, adding the necessary quotes along the way. Both DefaultValue and Tag contain string values:

If ctlToggle.Value Then
    ' If the toggle button is depressed, place the current
    ' carry field control into the control's DefaultValue
    ' property. But first, store the existing DefaultValue,
    ' if any, in the control's Tag property.
    If Len(ctlData.DefaultValue) > 0 Then
        ctlData.Tag = ctlData.DefaultValue
    End If
    ctlData.DefaultValue = acbcQuote & ctlData.Value & acbcQuote

When the toggle button is deselected, the function resets everything back to normal:

Else
    ' The toggle button is unpressed, so restore the text box's
    ' DefaultValue if there is a nonempty Tag property.
     If Len(ctlData.Tag) > 0 Then
         ctlData.DefaultValue = ctlData.Tag
         ctlData.Tag = ""
    Else
         ctlData.DefaultValue = ""
    End If
End If

Although the sample form uses only bound text boxes, this technique works equally well for all types of bound controls, with the exception of bound controls containing AutoNumber or OLE Object fields.