Supress 2nd Validation Error

[ChipR]

I have a text box with a validation rule and validation text. When entering a new record, if I put in invalid text, the validation text is displayed in a message box, but after clicking OK, another message box is displayed with:
-----------
myApplication (i bubble icon)
The value violates the validation rule for the field or record.

For example, you might have changed a validation rule without verifying whether the existing data matches the new validation rule.

Click undo to restore the previous value, or enter a new value that meets the validation rule for the field or records.
[OK] [Help]
-----------
After clicking OK here, if I try to tab out of the text box without changing anything, I get only the validation text message. So this 2nd message only happens the first time an entry is invalid. I've tried using the Form_Error event and DataErr 7753, but I still get this extra message. The table has no restrictions on the text field. Any idea how I can suppress this?

I just noticed that if I press Escape after the closing validation text message box, it saves the record with the invalid data! What am I doing wrong?

[RuralGuy]

I personally do not use the validation available in a table but put it *all* in the BeforeUpdate event of the control for the field in the form and the BeforeUpdate event of the DataEntry/Edit form. Just my $0.02. I feel it gives me more control over the messages.

[ChipR]

Thanks, I'll give that a try. If I put some testing code and message box in the BeforeUpdate even of the control, what do I need to put in the BeforeUpdate event of the form? I hope this isn't a dumb question, I'm just not sure exactly of the sequence of events with the BeforeUpdate.

[RuralGuy]

I use the BeforeUpdate event of the form to check for missing entries.

[ChipR]

Ah, that would allow you to deal with any fields that can't be null before you get a stupid error message from Acess. I get it :)

[ChipR]

Ok, I like using the BeforeUpdate very much, but I'm still getting this 2nd error message. Here's the code for my text box.

Private Sub TimeIn_BeforeUpdate(Cancel As Integer)
    'MsgBox "before time in update"
    If IsNull(TimeIn) Then
        Cancel = True
        Exit Sub
    End If
    'check for valid time
    If TimeIn > 2359 Or TimeIn Mod 100 > 59 Then
        MsgBox "Please enter a 4-digit military time, or press Escape to cancel."
        Cancel = True
        Exit Sub
    End If
    If IsNull([TimeOut]) Then
        Exit Sub
    End If
    'check < TimeOut
    If TimeIn >= TimeOut Then
        MsgBox "The time in must be before the time out."
        Cancel = True
    End If
End Sub

I've removed the validation from the control, and the table never had any. The field is still a text type. Now, if I go to a new record and put in something like 2600, I get my MsgBox, then I still get this other Access message. The BeforeUpdate event of the form isn't called in this case.

The 2nd message is still only on the first error, but it happens in each field in a new record. So if I put something invalid in Time In the first time, I get 2 errors. Now I try to submit it again, I get one error. I fix it and go to the Time Out text box and put in something invalid, I get 2 errors. I try to submit it again, and I only get one error.

Is there any other way I can try to intercept this message?

[ChipR]

Well I finally found it.

Even though I'm cancelling in the control's BeforeUpdate Event, there is still a Form Error Event being triggered with a DataErr of 2116.

I haven't been able to find an explanation of why, and I'm surprised that others haven't experienced it, but at least I can stop it from bothering my users.

[ChipR]

Well, suppressing this error message hasn't solved the actual problem. My code above doesn't work, and I can't figure out why. When I enter an invalid value in a control the first time, it is not getting cancelled, it's getting saved. And that's why the form error is called. Can anyone tell me why the cancel isn't working?

[RuralGuy]

Setting Cancel = True in the BeforeUpdate event of a control holds the focus in that control. What version of Access are you using? There is a Service Pack for both ac2003 and ac2007 and HotFixes for both as well.

[ChipR]

I have 2007 and the latest service pack, but I don't see any hotfixes. I haven't found any that describe fixes for problems like mine though.

The focus is not actually leaving the control, as far as I can tell. I enter invalid data, and try to tab out of the field. I get the BeforeUpdate MsgBox and the Form Error, and the cursor remains in the same control, but the value has been saved. I can see this because if I hit Escape, the invalid value remains in the control, like it is reverting back to the previously saved value. But when the BeforeUpdate event is called, the control has no .OldValue. The .OldValue is still either null or empty when the Form Error occurs.

This is on a continuous form, but that shouldn't matter, should it?

[ChipR]

I found that the value of the field is being updated BEFORE the BeforeUpdate event is called.

When I go to a new record and type in 2600 then press tab,
TimeInBox.OldValue =
TimeInBox.Value = 2600
[TimeIn] = 2600

So the value is already saved. Then when I cancel, or undo, the 2600 stays set.

I ended up using:

(if validation error)
        If IsNull(TimeInBox.OldValue) Then
            TimeIn = Null
        End If
        Cancel = True
        Exit Sub

This required that I change my field from Required - Yes to Required - No, so I added an input mask of 0000;_; and that makes sure that you can't save records with null entries anyway.

Could I somehow have caused the data to be updated before the BeforeUpdate event or is this "normal" Access behavior?

[RuralGuy]

@ChipR
When changing the value of the control the code will always go through the BeforeUpdate event first. Here's the Office 2007 HotFix link: http://support.microsoft.com/kb/960307

[ChipR]

None of the issues in that HotFix seem to be related. If I do use a HotFix, will I have to install it on all the users' machines also?
Thank you RuralGuy for your continued help.

[RuralGuy]

If it resolves the issue then I would say yes, otherwise you decide.

[RuralGuy]

And chance you can post a zipped up version of your db with any sensitive data removed?

[ChipR]

I'll try to extract the relative form and table today.

[RuralGuy]

I'll keep an eye out.

[ChipR]

The zip file is attached. I've put it back in it's non-working state by commenting out my workaround code and setting the table fields to Required = Yes, the way I had them originally. Also put a bunch of descriptive labels on the form.

[weefrenchie]

@ChipR
Hey ChipR,
I had the same problem - I checked every error function in the form (in VB design mode) and basically whenever I had a "On Error GoTo" statement, I changed the standard Err.Description to something meaningful (i.e. if the Sub was for Form_Load, I wrote "FormLoad error"

Then you can test your db and see what error comes up.

If you just want to get rid of the error message, you can simply suppress that part of the code by adding ' at the start of the line - don't forget to add an Exit Sub as well, example below:

Private Sub New_Settlement_Click()
On Error GoTo Err_New_Settlement_Click

DoCmd.GoToRecord , , acNewRec
Exit_New_Settlement_Click:
Exit Sub

Err_New_Settlement_Click:
' MsgBox Err.Description
' Resume Exit_New_Settlement_Click
Exit Sub

End Sub

Bear in mind though that if you do that you won't get an error message at all for this so ideally you want another error message somewhere else that looks at possible errors.
If you get rid of the whole On Error statement, you will get a runtime error.

I know this post is from last year but it might help someone somewhere!