Access Cookbook - Ken Getz [189]
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.
9.6. Create a Combo Box That Accepts New Entries
Problem
You're using combo boxes for data entry on your forms, and you want to allow users to add a new entry to the list of values in the combo box. Can you do this without forcing users to close the data entry form, add the record using a different form, and then return to the original form?
Solution
You can use the NotInList event to trap the error that occurs when a user types into a combo box a value that isn't in the underlying list. You can write an event procedure attached to this event that opens a pop-up form to gather any necessary data for the new entry, adds the new entry to the list, and then continues where the user started. This solution demonstrates how to create combo boxes that accept new entries by using the NotInList event and the OpenArgs property of forms.
Load the sample database 09-06.MDB and open the frmDataEntry form in form view. This form allows you to select a U.S. state from the combo box, but the list is purposely incomplete for the example. To enter a new state, type its abbreviation in the form and answer Yes when Access asks whether you want to add a new