ComboBox BeforeUpdate Cancel not working

[Stef]

Hello all,

I have a problem, I want to use the beforeupdate event of a combobox to
cancel
an action (using a simple msgbox warning in the event), but it doesn't seem
to work.
I even made the event routine dead simple: cancel all the time. And no
success... !!!
It seems to work a bit has the AfterUpdate event never trigger when I cancel
and
does trigger and I don't, but the value stay the same even when I cancel.

The combobox is unbound but takes it values from a query.

Help!!

Here's my dead-simple-but-doesn't-work routine:

Private Sub cboManufacturer_BeforeUpdate(Cancel As Integer)
MsgBox (Me.cboManufacturer.Value & "<vs>" &
Me.cboManufacturer.OldValue)
Me.cboManufacturer.Undo
Cancel = True
End Sub

Note: The msgbox was to show me .oldvalue & .value, and there always the
same
even if I just changed the value. Maybe it doesn't work for
unbound combobox

Thx
Stef

 

[Ken Snell [MVP]]

The Undo action only works if the control is bound to a field in the form's
Recordsource, sorry.

 

[Stef]

Thx (that was quick!)

So how can I implement a way to "cancel" an update on a combobox?

Is there any event trigger I can rely on or I will have to manually track
the value myself and somehow (maybe AfterUpdate) find a way to revert
the change if inapropriate?

Regards

 

[Ken Snell [MVP]]

This can be tricky for an unbound combo box. There is no specific event that
would ensure the result you want. You will have to use two module-level
variables to try to do this (and it may not be foolproof for all
situations).

Declare two variables in the Declarations section of the form's module:
Private glbvarComboValue As Variant
Private glbblnChangeBack As Boolean

You could use the GotFocus event to store the current value of the combo box
into glbvarComboValue and to set glbblnChangeBack to False.

Then, in your BeforeUpdate event, when you want to "undo" the value entered,
set glbblnChangeBack to True. Then, in the combo box's AfterUpdate event,
test the value of glbblnChangeBack; if it's True, set the combo box to
glbvarComboValue value and set glbblnChangeBack to False. If
glbblnChangeBack is False when the AfterUpdate event occurs, then store the
new value of the combo box into glbvarComboValue.

Example code:

Private glbvarComboValue As Variant
Private glbblnChangeBack As Boolean

Private Sub cboManufacturer_BeforeUpdate(Cancel As Integer)
If Me.cboManufacturer = "SomeValue" Then
' not valid value
MsgBox "Invalid Value"
glbblnChangeBack = True
End If
End Sub

Private Sub cboManufacturer_AfterUpdate()
If glbblnChangeBack = True Then
glbblnChangeBack = False
Me.cboManufacturer.Value = glbvarComboValue
Else
glbvarComboValue = Me.cboManufacturer.Value
End If
End Sub

Private Sub cboManufacturer_GotFocus()
glbvarComboValue = Me.cboManufacturer.Value
End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[Stef]

thx, that's what I had in mind as a workaround. I'll most probably won't use
the
BeforeUpdate+glbblnChangeBack and just do all the test in the AfterUpdate

Thx... this was very informative... much appreciated