Validate at Before Update

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

I want to do some validation at the Before Update point - what VB code do I
use to stop the update (or Add) taking place if the choices fail my
validation rules ?

Thanks
 
Cancel = True
Cancels the update of the record, but does not change the values in the
controls.

DoCmd.RunCommand acCmdUndo
Which is better written as Me.Undo causes the current record to revert to
the values it had before any editing was done.

What are you trying to accomplish? If you can tell me What you want to do,
I can explain How to do it.
 
Dave, Thanks again for the reply - sorry for the lack of info, I thought
what I was doing was simple and I see it is slightly more complicated.

I am trying to stop a user saving a record in the event that various bits
of data fail my validation rules. I have a couple of Date fields on the
form that I let the user fill with a date picker that I have - the dates
have to 'relate' to each other in various ways (one not > than the other,
not in the future, that type of thing) - along with a few other fields
that I want to validate. I am a DB programmer but not an Access or VB
one. I am used to testing the data entered (or validating field by field)
then at the Update or Add point in the code, allowing or disallowing
(with a message) the Update or Add.

I have tried using the 'Change' (method ?) code point but as I am
actually programatically changing the values in the Date fields (either
with the Date Picker tool or just in code) I don't seem to 'trip' the
Change code point.

Generally I don't want to blank the fields or revert to previously saved
data, I just want the user to go back and correct the incorrect entries.

Hope this gives a better picture of my situation.

Thanks for taking the time to respond.

Regards
 
You should seldom, if ever, use a control's Change event. It does exactly
what it says. It fires on every change of value in the control. That
translates to every keystroke, so it is not likely that is what you really
want.

It issue with using a control's Before Update event is that a user may enter
values in the form, but never enter or make a change to the control, so the
event will never fire. It is useful for checking the value entered, but not
reliable for comparing it's value to another control's value.

When you have multiple controls to validate, the best event to use is the
Form Before Update event because it can be canceled. That means the record
is not changed.
Typically, you would check each control you want to validate and if it does
not contain a correct value, you cancel the update, present a message box to
notify the user of the problem, and you can also set the focus to the
offending control. When you need to verify multiple controls whose values
depend on each other, then it is up to you to make the decision on where to
set the focus. But the basic principle is like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtZipCode) Then
MsgBox "A Zip Code Is Required", vbExclamation, "Entry Error"
Cancel = True
Me.txtZipCode.SetFocus
Exit Sub
End If

If Me.txtStartDate > Me.txtEndDate Then
MsgBox "Start Date Must Be Before End Date", vbExclamation, "Entry
Error"
Cancel = True
Me.txtStartDate.SetFocus
Exit Sub
End If

etc.
 
Dave,

Thanks for taking the time to come back to me again. I think I maybe still
did not make myself clear - it WAS (not, shouted, but emphasised !) the
Before Update on the Form that I was using - I had assumed that I wanted
the user to finish (at least as far as he was concerned :-)) editing,
entering data, then I would arrest his progress (if required) once he had
made the decision to complete and save. Your code is sort off what I have -
I will make my validation more complete, then use you Cancel = True to stop
the update if required.

Thanks for the help.

Regards
 
Back
Top