problem with before update event for data validity

[Jonathan Snyder via AccessMonster.com]

Hi all,

I'm using a before update event to validate data entered on a form. If
data is valid my code works fine. If data is invalid I want the event
canceled and what ever was entered erased. My code works fine up through
the msgbox, but then the data is not erased and a default message appears
saying a validation rule has been violated. I have no validation rules
attached to the table or form other than this code. I dont understand why
the cancel=true and me.undo statements are not working.

Please help!!

Private Sub Trip_ID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Trip_ID_BeforeUpdate

Dim trip As String
Dim vill As String
Dim strvill As String
Dim strtrip As String
vill = Me.Village
strvill = Left(vill, 1)

trip = Me.Trip_ID
strtrip = Left(trip, 1)

If strtrip <> strvill Then
MsgBox "Your Trip ID must begin with the first letter of the
village name", vbOKOnly, "Hey Dude"
Cancel = True
Me.Trip_ID.Undo
End If

 

[Rick Brandt]

Hi all,

I'm using a before update event to validate data entered on a form.
If data is valid my code works fine. If data is invalid I want the
event canceled and what ever was entered erased. My code works fine
up through the msgbox, but then the data is not erased and a default
message appears saying a validation rule has been violated. I have no
validation rules attached to the table or form other than this code.
I dont understand why the cancel=true and me.undo statements are not
working.

Please help!!

Private Sub Trip_ID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Trip_ID_BeforeUpdate

Dim trip As String
Dim vill As String
Dim strvill As String
Dim strtrip As String
vill = Me.Village
strvill = Left(vill, 1)

trip = Me.Trip_ID
strtrip = Left(trip, 1)

If strtrip <> strvill Then
MsgBox "Your Trip ID must begin with the first letter of the
village name", vbOKOnly, "Hey Dude"
Cancel = True
Me.Trip_ID.Undo
End If

I just ran into this yesterday. You don't want both Cancel = True and Undo.
Just use the Undo by itself.

 

[Jonathan Snyder via AccessMonster.com]

Rick,

thanks, for the suggestion, but using just the undo. supresses the default
validation message, but does not erase the invalid data and does not allow
the focus to be held on that field.

Any other ideas?

 

[Rick Brandt]

Rick,

thanks, for the suggestion, but using just the undo. supresses the
default validation message, but does not erase the invalid data and
does not allow the focus to be held on that field.

Any other ideas?

You're correct. After reviewing my code I see that I am using Undo of the
entire Form, not of the control. As I recall that was the only way I could
get rid of the same problem you are having. Apparently setting Cancel =
True in the BeforeUpdate of a required field is what triggers the validation
message whereas if the entire form is "undone" this doesn't happen.