Form-Level Validation Rules and Datasheet View


C

croy

Do validation rules for controls on forms not work when the
form is displayed in datasheet view?
 
Ad

Advertisements

C

croy

Do validation rules for controls on forms not work when the
form is displayed in datasheet view?


I wrote the above because my vrule of "Is Not Null or <0"
didn't seem to be working for a control in a datasheet
subform. Nor could I get "Is Not Null" to work.

On further testing, I see that it makes no difference what
view-type the form is.

And I see that if I make the rule "Not <0", it doesn't
prevent me from tabbing thru the field without an entry...
unless I enter a value less than 0--then the rule kicks in,
and if I delete the value I entered, the rule kicks in
again. But if I escape back to the original state of no
data ever entered, I can again tab right thru without the
rule showing up.

I'm confused!
 
C

croy

I wrote the above because my vrule of "Is Not Null or <0"
didn't seem to be working for a control in a datasheet
subform. Nor could I get "Is Not Null" to work.

On further testing, I see that it makes no difference what
view-type the form is.

And I see that if I make the rule "Not <0", it doesn't
prevent me from tabbing thru the field without an entry...
unless I enter a value less than 0--then the rule kicks in,
and if I delete the value I entered, the rule kicks in
again. But if I escape back to the original state of no
data ever entered, I can again tab right thru without the
rule showing up.

I'm confused!


This is all on Access 2002, saving in 2000 format.
 
D

Dirk Goldgar

croy said:
I wrote the above because my vrule of "Is Not Null or <0"
didn't seem to be working for a control in a datasheet
subform. Nor could I get "Is Not Null" to work.

On further testing, I see that it makes no difference what
view-type the form is.

And I see that if I make the rule "Not <0", it doesn't
prevent me from tabbing thru the field without an entry...
unless I enter a value less than 0--then the rule kicks in,
and if I delete the value I entered, the rule kicks in
again. But if I escape back to the original state of no
data ever entered, I can again tab right thru without the
rule showing up.

I'm confused!


That's the expected behavior (although, admittedly, *you* didn't expect it
<g>). Validation rules on controls are applied when the value of a control
is changed; not when the value is not touched at all. If you put the
validation rule on the field in the table instead (via the table's design
view), it will be applied both when the value is changed and when the record
is about to be saved.

Incidentally, shouldn't your rule be:

Is Not Null And >0

.... instead of "Or >0"? After all, if the field is > 0, then it isn't Null.
 
C

croy

That's the expected behavior (although, admittedly, *you* didn't expect it
<g>). Validation rules on controls are applied when the value of a control
is changed; not when the value is not touched at all. If you put the
validation rule on the field in the table instead (via the table's design
view), it will be applied both when the value is changed and when the record
is about to be saved.

Incidentally, shouldn't your rule be:

Is Not Null And >0

... instead of "Or >0"? After all, if the field is > 0, then it isn't Null.


Hmmm... lemme think. Well, that's not working ;-l

In plain old mushy English: Must not be null, *and* must
not be less than zero.

I guess that would be:

Is Not Null And Not <0

or just...

....?

Thanks for lending a hand. As you can see, I need it!
 
C

croy

On Thu, 29 Nov 2007 13:29:18 -0500, "Dirk Goldgar"

[...]
Validation rules on controls are applied when the value of a control
is changed; not when the value is not touched at all.

[...]


Thank you, Dirk.


I didn't see *that* in the help files anywhere! It's
probably in there somewhere, but my searches for
"validation" sure didn't bring it out.

Without the help of folks like you, I flat-out couldn't do
this job!
 
Ad

Advertisements

D

Dirk Goldgar

croy said:
In plain old mushy English: Must not be null, *and* must
not be less than zero.

I guess that would be:

Is Not Null And Not <0

or just...


...?


No, just testing >=0 won't work, unless you also set the field's Required
property to True (because that enforces Not Null). Testing >=0 by itself
won't catch a Null value, because comparing anything to Null gives a Null
result: that which is unknown can't be said to be greater than, equal to,
or less than anything else -- it's unknown.

You can either have a validation rule of

Is Not Null And >=0

or you can set the field's Required property to Yes, and use a validation
rule of

Either will prohibit both Null values and values < 0 in the field.
 
D

Dirk Goldgar

croy said:
On Thu, 29 Nov 2007 13:29:18 -0500, "Dirk Goldgar"

[...]
Validation rules on controls are applied when the value of a control
is changed; not when the value is not touched at all.
[...]

Thank you, Dirk.

I didn't see *that* in the help files anywhere! It's
probably in there somewhere, but my searches for
"validation" sure didn't bring it out.


You're welcome. It used to be in there, but I'm sure not finding it in
Access 2003. They've reorganized the help files so many times, useful
information has been lost. Now, the Access 97 help file was a thing of
beauty!
 
C

croy

croy said:
On Thu, 29 Nov 2007 13:29:18 -0500, "Dirk Goldgar"

[...]
Validation rules on controls are applied when the value of a control
is changed; not when the value is not touched at all.
[...]

Thank you, Dirk.

I didn't see *that* in the help files anywhere! It's
probably in there somewhere, but my searches for
"validation" sure didn't bring it out.


You're welcome. It used to be in there, but I'm sure not finding it in
Access 2003. They've reorganized the help files so many times, useful
information has been lost. Now, the Access 97 help file was a thing of
beauty!


Yup, I think that was the zenith of Access Help. I've
actually pulled those help filed from my old Office97 disk
and use them as a stand-alone help set, but only when I
remember to do so! ;-0
 
Ad

Advertisements

C

croy

No, just testing >=0 won't work, unless you also set the field's Required
property to True (because that enforces Not Null). Testing >=0 by itself
won't catch a Null value, because comparing anything to Null gives a Null
result: that which is unknown can't be said to be greater than, equal to,
or less than anything else -- it's unknown.

You can either have a validation rule of

Is Not Null And >=0

or you can set the field's Required property to Yes, and use a validation
rule of


Either will prohibit both Null values and values < 0 in the field.


More excellent info!

Thanks Dirk.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top