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?
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.
To add this functionality to your own forms, follow these steps:
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.
Adjust the toggle buttons’ control properties to match those in Table 9-2.
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]) |
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”).
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
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.