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
figs/acb2_0916.gif
Figure 9-17. The values of the "toggled" text boxes have been carried forward
figs/acb2_0917.gif

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

  1. Open your form in design view. Add a small toggle button control to the right of each bound control for which you wish to add a carry-forward feature. On the frmCustomer sample form, we added toggle controls to the right of the Company, Address, City, State, Zip, Phone, and Fax text boxes. Because you can't duplicate an AutoNumber field and you're unlikely to want to carry forward a customer's first or last name, we did not add toggle buttons for these controls.

  2. Adjust the toggle buttons' control properties to match those in Table 9-2.

    Replace Phone with the label of the bound control to the left of the toggle button; replace txtPhone with the name of the bound control. Replace the Width and Height values with anything that works well on your form without unnecessarily cluttering it. We've found that a width of 0.1" works nicely with a height that matches the height of the bound control (on the sample form, the height of both the text box and the toggle button controls is 0.1667").

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])

  1. Add the following function to a global module (or import basCarryForward from 09-05.MDB):

    Public Function acbCarry(frm As Form, ctlToggle As Control)
    
        Dim ctlData As Control
        Const acbcQuote = """"
        
        ' The name of the data control this toggle control serves
        ' is stored in the toggle control's Tag property.
        Set ctlData = frm(ctlToggle.Tag)
        
        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
        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
    End Function

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.