By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Store old value of an unbound list box?

P: 99

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?
Dec 9 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,287
You might hide a text box on the form and update its value when an item is selected, after getting the old value.
Dec 9 '09 #2

P: 99

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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstbMyListBox_AfterUpdate()
  3.         'store the last value
  4.         !txbLastID = !txbCurrentID
  5.         !txbCurrentID = !lstbMyListBox
  7. End Sub

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

Dec 9 '09 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstTest_Enter()
  2.     Me.lstTest.Tag = Nz(Me.lstTest, "Null")
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstTest_AfterUpdate()
  2.     Debug.Print "Current: " & Nz(Me.lstTest, "Null") & " Prev: " & Me.lstTest.Tag
  3. 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):

Expand|Select|Wrap|Line Numbers
  1. Current: eastgate Prev: Null     
  2. Current: PUMPGATE COURT Prev: Null          (selected straight after the first selection without leaving the control)
  3. Current: BROOM DRIVE Prev: Null             (as above)
  4. Current: HIGH STREET Prev: BROOM DRIVE      (selected after leaving the control to do something else)
  5. Current: ISLAND BANK ROAD Prev: BROOM DRIVE (selected without leaving the control)
  6. Current: B9006 Prev: ISLAND BANK ROAD       (selected after leaving the control to do something else)
  7. Current: Croy Prev: B9006                   (selected after leaving the control to do something else)
Dec 10 '09 #4

P: 99

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.

Dec 10 '09 #5

P: 1
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.
Dec 15 '09 #6

P: 3
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.
Feb 22 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.