WEIRD behavior with BeforeUpdate

  • Thread starter Thread starter jfp
  • Start date Start date
J

jfp

I have an unbound control named "edtCopies" on a form. This is the code
for BeforeUpdate :

Private Sub edtCopies_BeforeUpdate(Cancel As Integer)

If ((edtCopies < 1) Or (edtCopies > 100)) Then
MsgBox "# of copies must be between 1 and 100.", vbExclamation
edtCopies.Undo
Cancel = True
End If

End Sub

The value of the control is initialized to "1".
If i change that to "123" and try to leave the field (Tab, Enter, etc.)
then:
The message box pops up,
I acknowledge it,
The value "123" remains in the control, but is now highlighted
If i then move to some other control, the (illegal) value 123 remains in
the control.

If i set a breakpoint on the first line in the Sub,
BOTH edtCopies.Value and edtCopies.OldValue are seen to be "123".
Thus, perhaps, Undo is "working" in that it is restoring the old value
-- but why is the old value wrong?

What is happening here ?
I have used this same type of logic on other projects (running on the
same PC - same Access 2002 etc.) without a problem. Is there some
setting or option that could have gotten messed up in this project (or
form) ?
 
Undo only works on bound controls.

You must have an unbound textbox, just try removing the undo line and
it will probably do what you want.

- Jim
 
More info -- see end of post.
-=-=
jfp said:
I have an unbound control named "edtCopies" on a form. This is the code
for BeforeUpdate :

Private Sub edtCopies_BeforeUpdate(Cancel As Integer)

If ((edtCopies < 1) Or (edtCopies > 100)) Then
MsgBox "# of copies must be between 1 and 100.", vbExclamation
edtCopies.Undo
Cancel = True
End If

End Sub

The value of the control is initialized to "1".
If i change that to "123" and try to leave the field (Tab, Enter, etc.)
then:
The message box pops up,
I acknowledge it,
The value "123" remains in the control, but is now highlighted
If i then move to some other control, the (illegal) value 123 remains in
the control.

If i set a breakpoint on the first line in the Sub,
BOTH edtCopies.Value and edtCopies.OldValue are seen to be "123".
Thus, perhaps, Undo is "working" in that it is restoring the old value
-- but why is the old value wrong?

What is happening here ?
I have used this same type of logic on other projects (running on the
same PC - same Access 2002 etc.) without a problem. Is there some
setting or option that could have gotten messed up in this project (or
form) ?
Click to expand...
-=-=
More info:

OK -- according to the VBA help, the OldValue property has meaning ONLY
for a bound control. I would guess from this that, for an unbound
control, it is always the same as the Value property. This explains the
failure of the Undo method. I would also guess that the highlighting is
due to the combination of Undo "changing" the control value and the
"Cancel = True" forcing focus to stay in the control.
SO -- how do i accomplish what i want ?
(restore the original value and keep focus in the control, without
highlighting it)

OR -- i can just remove the "Undo". Then, the bad value remains, but
focus is kept in the control.
 
Instead of using the BeforeUpdate event, would setting a validation rule for editCopies (e.g., ">=1 And <=100") and validation text do the trick?

Kurt
 
jfp said:
More info -- see end of post.
-=-=

-=-=
More info:

OK -- according to the VBA help, the OldValue property has meaning
ONLY for a bound control. I would guess from this that, for an
unbound control, it is always the same as the Value property. This
explains the failure of the Undo method. I would also guess that the
highlighting is due to the combination of Undo "changing" the control
value and the "Cancel = True" forcing focus to stay in the control.
SO -- how do i accomplish what i want ?
(restore the original value and keep focus in the control, without
highlighting it)

OR -- i can just remove the "Undo". Then, the bad value remains, but
focus is kept in the control.
Click to expand...

As you've realized, Control.Undo really only works for bound controls.
You could save the value of the control in a module-level variable in
the control's GotFocus event, and pull it back from there when canceling
the update in the BeforeUpdate event.
 
OR -- i can just remove the "Undo". Then, the bad value remains, but
focus is kept in the control.
Click to expand...

Yep. Like I mentioned in a response you the original post. It is
helpful to train your users to use <esc> when they change their minds.
So many want to delete or backspace over the contents which also has
its own problems.

Also, just mention that in the error message.

- Jim
 
Back
Top