In BeforeUpdate, if user cancels the operation:
Cancel = True
SendKeys "{ESC}"
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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:
In BeforeUpdate, if user cancels the operation:
Cancel = True
SendKeys "{ESC}"
Hi. Not in front of a computer now, but I would probably try using the AfterUpdate event instead.
I could be wrong but you would do the undo and then refresh the combo to have the old value appear.
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.