Form BeforeUpdate Event Is Running Only Once (Access 97)

[Ryan]

I have a form in Access 97 and on its BeforeUpdate event I placed code
that scans all the controls on the form and, if the field is required,
will stop the update from occurring and provide a nice message to the
user:

Dim ctrl As Variant
For Each ctrl In Me.Controls
If InStr(1, ctrl.Tag, "Required") Then
If IsNull(ctrl.Value) Then
msgbox "You cannot leave...Please fill in...and
try...again."
ctrl.Setfocus
Cancel = True
Exit Sub
End If
End If
Next ctrl

The code works great the first time I go to commit a change with a
field left blank. It also works great if I keep trying to save my
change without doing anything. If, however, I change anything in the
field and try to save again, the BeforeUpdate event no longer fires
and Access Default Error message takes over. Also, the same thing
happens if I am just editing a record. If I delete the contents of a
field and go to save, the BeforeUpdate does not fire, and I am again
faced with Access's built in error message. Is there any way around
this? (I have also tried the above code without ctrl.SetFocus.

 

[Allen Browne]

If you enter something in a required field, and then use backspace to remove
the characters, the default message pops up and prevents you from leaving
the control. Naturally this fires before Form_BeforeUpdate, i.e. it is
validating the particular field, whereas Form_BeforeUpdate is validating the
record.

You can trap the message in the Error event of the form. Expect DataErr
3201. After displaying your custom message, set Response to
acDataErrContinue.

 

[Ryan]

Actually, I am permitted to leave the field. I don't get any error
until I go to save the record again, at which point Access default
error message will pop up instead of BeforeUpdate firing. There is no
validation set on the control since I only care that they have
everything in order when they actually go to commit their changes. Do
you know what is preventing the Form_BeforeUpdate from firing the
second time?

THANKS!!

 

[Ryan]

Actually, scratch my update. You are correct, it is looking at the
validation of the database field. This problem does not seem to
trigger the form's onerror
event, however. Any suggestions?

Thanks again!!!

 

[Ryan]

Sorry again. I had the on error event code on the wrong item. You are
totally correct and your advice was incredibly helpful!! THANK YOU!!!