By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,569 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Supress 2nd Validation Error

Expert 100+
P: 1,287
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?
Feb 23 '09 #1
Share this Question
Share on Google+
18 Replies


RuralGuy
Expert 100+
P: 375
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.
Feb 24 '09 #2

Expert 100+
P: 1,287
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.
Feb 24 '09 #3

RuralGuy
Expert 100+
P: 375
I use the BeforeUpdate event of the form to check for missing entries.
Feb 24 '09 #4

Expert 100+
P: 1,287
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 :)
Feb 24 '09 #5

Expert 100+
P: 1,287
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub TimeIn_BeforeUpdate(Cancel As Integer)
  2.     'MsgBox "before time in update"
  3.     If IsNull(TimeIn) Then
  4.         Cancel = True
  5.         Exit Sub
  6.     End If
  7.     'check for valid time
  8.     If TimeIn > 2359 Or TimeIn Mod 100 > 59 Then
  9.         MsgBox "Please enter a 4-digit military time, or press Escape to cancel."
  10.         Cancel = True
  11.         Exit Sub
  12.     End If
  13.     If IsNull([TimeOut]) Then
  14.         Exit Sub
  15.     End If
  16.     'check < TimeOut
  17.     If TimeIn >= TimeOut Then
  18.         MsgBox "The time in must be before the time out."
  19.         Cancel = True
  20.     End If
  21. 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?
Feb 24 '09 #6

Expert 100+
P: 1,287
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.
Feb 24 '09 #7

Expert 100+
P: 1,287
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?
Feb 24 '09 #8

RuralGuy
Expert 100+
P: 375
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.
Feb 24 '09 #9

Expert 100+
P: 1,287
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?
Feb 25 '09 #10

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. (if validation error)
  2.         If IsNull(TimeInBox.OldValue) Then
  3.             TimeIn = Null
  4.         End If
  5.         Cancel = True
  6.         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?
Feb 25 '09 #11

RuralGuy
Expert 100+
P: 375
@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
Feb 25 '09 #12

Expert 100+
P: 1,287
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.
Feb 25 '09 #13

RuralGuy
Expert 100+
P: 375
If it resolves the issue then I would say yes, otherwise you decide.
Feb 25 '09 #14

RuralGuy
Expert 100+
P: 375
And chance you can post a zipped up version of your db with any sensitive data removed?
Feb 25 '09 #15

Expert 100+
P: 1,287
I'll try to extract the relative form and table today.
Feb 25 '09 #16

RuralGuy
Expert 100+
P: 375
I'll keep an eye out.
Feb 25 '09 #17

Expert 100+
P: 1,287
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.
Attached Files
File Type: zip FormTest.zip (21.2 KB, 77 views)
Feb 25 '09 #18

P: 9
@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!
Apr 10 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.