ValidationRule not executing on null values.

  • Thread starter Thread starter Scott Stubbs
  • Start date Start date
S

Scott Stubbs

Hi,

I have a form that allows the user to enter comments in a bound text box.
The bound field name is called StaffComments. In SQL Server 2000 Desktop
Edition, the column is defined as text and nulls are not allowed.

However, my validation rule does not appear to be evaluating when a user
attempts to add a new record. If they do not enter anything in the text box
and attempt to save the new record, Access returns the SQL Server error
message that it can not insert due to a NULL column value.

Fundamentally, that's good. SQL Server is enforcing the rule but my concern
is the fact that my ValidationRule expression is not evaluating and
displaying my ValidationText message.

If the user where to enter some text and then delete that text, the value of
the field (or text box) would no longer be null and contain an empty string.
In this case, my ValidationRule expression does evaluate and the user gets
the ValidationText message that I defined.

I've tried various combinations of expressions for the ValidationRule and
have alternated the requirement of NOT NULL in the table definition with
consistent results. In summary, it appears that if the control has never
received any input from the user on a new record and that field can not be
null, the ValidationRule for the control never evaluates.

Here is what my ValidationRule property is set to for now:

<> 0 And Is Not Null

The ValidationText property is set to my own error message text.

I'm having a hard time getting my brain around this one. If anyone has seen
this before and has a solution or even an explanation why, I sure would be
grateful for a response.

Best regards,

Scott S.
 
Hi Scott,

From your description, I understand that you would like to know why your
Validation Rules is not fired unless you add some value in the field and
then delete it. Have I understood the issue correctly and completely? If
there is anything I misunderstood, please feel free to let me know.

Based on my knowledge, a validation rule for a field or control is checked
when you move to another different field or control, which is to say, you
have to make changes in a field then you could make validation rule fired.
A validation rule for a record is checked when you move to a different
record.

However, you could take this as a workaround
1. Select Form Properties click BeforeUpdate on Event page.
2. Select Code Builder
3. Add validation code by using VBA such as
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(col1.Value) Then
MsgBox "No, It should not be NULL"
End If

End Sub



Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Thanks Michael,

Yes, you understood the situation completely. I will be using the
workaround you suggested and will consolidate all of my error handling in
the Form_BeforeUpdate event. I was hoping to leverage the validation
properties to "internationalize" the application by scanning properties and
swapping out different language strings. I was working on an add-in that
would do just that but what I'm already doing in the code modules with
generic string handling will work nicely with the workaround you provided.

If Microsoft culls these newsgroups for future changes in Access, I'd like
to have this one captured: It would be nice for Access to give me the
option of fireing a Validation Rule even if the control was never modified
during an add or edit.

I do appreciate the quick response and workaround!

Best Regards,

Scott S.
 
Hi Scott,

Thank you very much for you kindness and I am glad to hear that I solved
your issue.

IOM, It's a great ida to have such expand feature for Validation Rule, I'd
recommand that you forward your recommandation to the Microsoft Wish
Program, http://register.microsoft.com/mswish/suggestion.asp

Have a nice day, if you have any questions or concerns, don't hesitate to
let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Back
Top