April 16, 2011, 5:57 p.m.
posted by osi
Store Initial Control Selections for Later Recall
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.
Figure 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:
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.