ComboBox BeforeUpdate handler - Cancel/Undo unexpected behavior

Question

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 Cancel, the 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 BeforeUpdate and AfterUpdate fire first, and THEN Change. I'm completely stymied trying to do something that should be simple.

So, the questions are:

1. 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?
2. Why is the actual order of events exactly opposite to the order documented?

Answer 1

In BeforeUpdate, if user cancels the operation:

Cancel = True
SendKeys "{ESC}"

Answer 2

Hi. Not in front of a computer now, but I would probably try using the AfterUpdate event instead.

Answer 3

I could be wrong but you would do the undo and then refresh the combo to have the old value appear.

Answer 4

I'd agree with thedbguy. Assign the current value of the combo box to a module level variable of Variant data type in the control's GotFocus event procedure. Then in its AfterUpdate event procedure assign the value of the variable to the control if the user elects to abort the operation.

The following is the module of a simple form in which I tested this:

Option Compare Database
Option Explicit

Private varCountry As Variant

Private Sub cboCountry_AfterUpdate()

Const MESSAGETEXT = "Do you wish to proceed on the basis of the selected country?"

If MsgBox(MESSAGETEXT, vbOKCancel, "Confirm") = vbCancel Then
    Me.cboCountry = varCountry
Else
    Me.cboRegion.SetFocus
End If

End Sub

Private Sub cboCountry_GotFocus()

varCountry = Me.cboCountry

End Sub

In the above if the user selects to cancel the update the previous value, or a NULL if the control was previously empty, is assigned to the control. Note that the AfterUpdate event procedure only executes if the user changes the value of the control manually, so the procedure does not execute when the above code assigns the old value to the control.

Note also that the code moves focus to the next control if the user confirms the update. This forces the cboCountry control's GotFocus event procedure to execute if the user immediately decides to change the selected country value again, assigning the current value to the variable.