Consider the following scenario:
An unbound combobox with
rowSource set to a query. The selected value is used in later processing.
In a given situation, the
BeforeUpdate handler displays a
MsgBox confirming that the user wants to change the value (`OK=proceed, Cancel=leave the old value`)
If the user replies
BeforeUpdate handler sets
Cancel=1 and returns. The combobox's value is NOT reset to its previous value. This part I think I understand, since all the
Cancel=1 setting does is prevent further update processing. However, it still leaves the control state "dirty" so any attempt to leave the control re-fires
BeforeUpdate. This would imply that I need to do something to reset the control's value to its old value.
My research on the web says I need to add a control.Undo call. Unfortunately, for a combobox at least, Undo doesn't do anything, it leaves the newly selected value in the combobox.
If I try setting the value directly (
control.value = saved_value note: control.oldValue has the new value, even in
BeforeUpdate so I have to save the prior value earlier) I get a popup saying
Which is kind of meaningless since the field isn't bound and there's no record to save in the database.
So then I thought to try using the OnChange handler instead. According to the Microsoft documentation...
When you change the text in a text box or in the text box section of a combo box, the Change event occurs. This event occurs whenever the contents of the control change, but before you move the focus to a different control or record (and therefore, before the BeforeUpdate and AfterUpdate events occur). (my emphasis)
However, this is most definitely NOT the case IF THE CHANGE WAS A RESULT OF SELECTING A NEW VALUE WITH THE MOUSE. What I see (setting breakpoints) is that
AfterUpdate fire first, and THEN
Change. I'm completely stymied trying to do something that should be simple.
So, the questions are:
How do I trap a change to a combobox and undo that change so that to the user "it never happened" and things are exactly the way they were on the form before the change was initiated, whether it's by typing into the combobox or by selecting a new value from the dropdown?
Why is the actual order of events exactly opposite to the order documented?