Hack 72. Store Initial Control Selections for Later Recall

The Tag property is a great place to store data about controls. Here's how to put it to good use.

Combo boxes and listboxes are great for presenting users with choices. As the user makes selections in a combo box or listbox, the value is available to use in code simply by referring to the control's Value property:

If IsNull(cmbLevel) Then
  MsgBox "No Value Selected"
Else
  MsgBox cmbLevel.Value
End If

You can even refer only to the control itself and leave the Value property off, like this:

If IsNull(cmbLevel) Then
 MsgBox "No Value Selected"
Else
  MsgBox cmbLevel
End If

Note that both code snippets begin with a test for a null. The listbox or combo box might initially be null, so it is good practice to include a way to avoid bombing out if this is the case.

As users click away and make selections, a listbox or combo box's value changes to reflect the user's last selection. But what if you have to recall an earlier selection or perhaps the first selection the user made? A user might have forgotten what he first selected and wants to return to that value. You can build into your application a way to do this, but it will be for naught unless you stored the initial value.

Of course, you can keep the initial value stored in a table, but that's extra work. Instead, this hack shows you how to store a control's initial value right in the control itself! Both the listbox and the combo-box controls have a Tag property. This property has no purpose other than to act like a little comment area for Access objects.

In case you are wondering about the OldValue property, here is the skinny on that. OldValue stores the unedited value while the value is being edited. This is like a before-and-after picture of an edit, but it works only for the most recent change. You can't use OldValue to recall a value from several changes back.

Figure 8-1 shows a form with a combo box, a listbox, and a button. Both the combo box (cmbLevel) and the listbox (listRegion) have their Row Source Type set to Value List. The combo box has four Row Source items: Beginner, Intermediate, Advanced, and Expert. The listbox has five Row Source items: East, West, North, South, and Central. Original selected values for these controls are stored in the Tag property.

These controls are unbound. When the form opens, they are blank. In the form's Open event, the Tag property of each control is set to a zero-length string. Here is the complete code:

Figure 8-1. A form with a combo box, listbox, and button

Private Sub Form_Open(Cancel As Integer)
 cmbLevel.Tag = ""
 listRegion.Tag = ""
End Sub

Private Sub cmbLevel_AfterUpdate( )
  If cmbLevel.Tag = "" Then
	cmbLevel.Tag = cmbLevel
  End If
End Sub

Private Sub listRegion_AfterUpdate( )
  If listRegion.Tag = "" Then
    listRegion.Tag = listRegion
  End If
End Sub

Private Sub cmdReset_Click( )
  cmbLevel = cmbLevel.Tag
  listRegion = listRegion.Tag
End Sub

The first time a selection is made in either cmbLevel or listRegion, the Tag property is tested to see if it is a zero-length string. If it returns true, the Tag property updates to the control's current value. This activity occurs in the AfterUpdate event because the new latest value is required. The latest value is the current one after the update.

The test for a tag with a zero-length string is successful only the first time it is run. Every time after that, a new selection is made in a control; therefore, the test fails because the Tag property has a valuethe original selected value.

The Reset button, when clicked, simply sets both the combo box and the listbox to the original selections by setting them to their own Tag properties.

This hack shows you only how to store the initial selections. What you actually do with them is a function of your application. The great thing about this hack is that it is easily portable. Because no tables are involved, you can copy the code into other controls' event stubs and just change the name of the control reference in the code.