Store old value of an unbound list box?

[AdamOnAccess]

Hi,

I need to store the last value of an unbound list box. For example, if the list box is currently set to X, when a user clicks Y, I need to save X in a variable.

I've tried to store the old value in a variable during the Before Update event, but that does work. I've also heard that the OldValue property doesn't work on unbound controls.

Anyone know how to do this?
Thanks,
Adam

[ChipR]

You might hide a text box on the form and update its value when an item is selected, after getting the old value.

[AdamOnAccess]

ChipR,

Thanks for the reply. That's kind of the the approuch I took, but I because I couldn't find an event to capture the value at the moment of the click, I needed 2 text boxes. Here's my solution:

Private Sub lstbMyListBox_AfterUpdate()

        'store the last value
        !txbLastID = !txbCurrentID
        !txbCurrentID = !lstbMyListBox

End Sub

It works, but I can't help feeling there is a better way to do this.

Thanks,
Adam

[Stewart Ross]

One approach if you want to store the original value of the control as it was when the control gets focus would be to use the On Enter event of the listbox control to store the current value of the list into the Tag property of the same control. You can then reference the control's previous value by listing its Tag property, and its current value by listing its Value property (the default when referring to the control).

For a test control (simply called lstTest in my example), the On Enter code is just

Private Sub lstTest_Enter()
    Me.lstTest.Tag = Nz(Me.lstTest, "Null")
End Sub

The Nz is necessary because when the control gains focus the On Enter event will be fired, and as no selection has been made the listbox has a null value which cannot be stored in the Tag property.

In testing this here is an After Update event routine which prints out the current and previous values to the VBE immediate window:

Private Sub lstTest_AfterUpdate()
    Debug.Print "Current: " & Nz(Me.lstTest, "Null") & " Prev: " & Me.lstTest.Tag
End Sub

Please note that On Enter fires just once until the control loses focus, whereas After Update will be fired several times if the user makes choices from the control one after the other (see first three examples and next two examples below):

Current: eastgate Prev: Null     
Current: PUMPGATE COURT Prev: Null          (selected straight after the first selection without leaving the control)
Current: BROOM DRIVE Prev: Null             (as above)
Current: HIGH STREET Prev: BROOM DRIVE      (selected after leaving the control to do something else)
Current: ISLAND BANK ROAD Prev: BROOM DRIVE (selected without leaving the control)
Current: B9006 Prev: ISLAND BANK ROAD       (selected after leaving the control to do something else)
Current: Croy Prev: B9006                   (selected after leaving the control to do something else)

-Stewart

[AdamOnAccess]

Stewart,

Yes, you see the problem. I tried the OnEnter and the GotFocus events, but I saw it didn't work if the user made additional selections. I knew it was going to have to involve AfterUpdate. Ultimately, using the 2 text boxes seems to be the only thing that works consistently. Odd that there is no way to access OldValue, but I have it working so onward and upward.

Thanks,
Adam

[mkaney]

Just save the value to a variable in the current event and in the after update event. Then the next time the user changes it, the new value will not have copied to the variable until after the update, so the old value will be in the variable in the before update event. Just make sure to put the variable at the top of the form module above any subs so that it is available to all subs in the module.

[lokcard]

how to store a value in a control?

i am a newbie with Access. I like to create a form to store user configuration. How to transfer and store those user-select value? should i store them in a table?

and, during operation, how to retrieve those value?

thank you.