Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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").

3. 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
propertyan extra property that Access allows us to use any way
we wantis 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.