Validation Rule on Table Field vs Form Control

[gweasel]

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

[Rick Brandt]

gweasel wrote:

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

In an all-in-one single user system it makes very little difference. In a
business app though it is best to look at the database as a stand-alone entity.
One that exists as a resource unto itself and which could be accessed by
multiple interfaces, not just the front end that you create for it in Access.

When looked at that way then you want as many of the rules as possible to be
defined at the data engine level so that they are enforced regardless of what
program might interact with the data.

This is the default mind-set when building server databases in SQL Server,
Oracle, etc., since those are purely database engines and the guy who sets up
the database usually has nothing to do with building the applications that will
interact with it. When building database applications with Access this doesn't
isn't always so obvious, but it is still the best practice.

The most simple example is that even in an all-in-one single user application
you could build multiple forms that all are bound to the same table. Do you
want to repetitively build those rules into each and every one of those forms,
or do it once in the table and be done with it?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Don Leverton]

Hi,

IMHO, it sounds like it would just be easier to set the "Required" property
to "Yes" in the table design view in a case like this.

HTH,
Don
"gweasel" <ba*****@yahoo.comwrote in message
news:11**********************@64g2000cwx.googlegro ups.com...

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

[gweasel]

I have set the Required property to yes and moved the validations to
the table level instead of the form level. I like to have the
validation rules in effect though because the error messages that come
up when only the Required property are set are not helpful to the end
user (they refer to the field name as "[Tbl_Name].[Field_Name]" and
that is confusing to the users.

I am actually having an issue though. I swear this was working
earlier in the week, but even before moving the rules to the table
level, when I went through to test the system, I am able to breeze
right through the supposedly required fields/controls. Now, if I am
switching from a form to the subform, I get the unhelpful "required
property" error message, but never see the validation text I typed
out. And ideally, I'd want this message to pop up when they are
trying to exit the required control on the form rather than when
they're trying to exit another control to get into a subform. For
example, "Due Date" is required, but it is 5 tab stops away from my
subform. They can tab/click right past "Due Date" and never know they
missed something that was required until 5 steps later. I think this
is adding to confusion as well - since they're most likely going to
look in the last field they were in to try and fix the problem, when
in reality, they need to go back farther than that.

Make sense? Any idea how I can have these messages pop up "On exit"
basically, without coding? If coding is needed, I guess that's fine,
but please give me some hint as to what I need to make it work and
give the user a custom message instead of the default messages Access
gives.

Thanks.

On Mar 7, 6:03 am, "Don Leverton" <[e-mail]>
wrote:

Hi,

IMHO, it sounds like it would just be easier to set the "Required" property
to "Yes" in the table design view in a case like this.

HTH,
Don

"gweasel" <[e-mail] in message

news:11**********************@64g2000cwx.googlegro ups.com...

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

[Larry Linson]

Table validations are applied when you write the Record, and that's what
happens when you go from a main Form to the Form embedded in a Subform
Control. You can "protect yourself" with Table-level validation and _also_
protect your users from unfriendly error messages, by _also_ including
validation in VBA code in the AfterUpdate of Controls on the Form. (Yes, I
know -- extra work... frustrating, isn't it?)

Larry Linson
Microsoft Access MVP

"gweasel" <ba*****@yahoo.comwrote in message
news:11********************@30g2000cwc.googlegroup s.com...

>I have set the Required property to yes and moved the validations to
the table level instead of the form level. I like to have the
validation rules in effect though because the error messages that come
up when only the Required property are set are not helpful to the end
user (they refer to the field name as "[Tbl_Name].[Field_Name]" and
that is confusing to the users.

I am actually having an issue though. I swear this was working
earlier in the week, but even before moving the rules to the table
level, when I went through to test the system, I am able to breeze
right through the supposedly required fields/controls. Now, if I am
switching from a form to the subform, I get the unhelpful "required
property" error message, but never see the validation text I typed
out. And ideally, I'd want this message to pop up when they are
trying to exit the required control on the form rather than when
they're trying to exit another control to get into a subform. For
example, "Due Date" is required, but it is 5 tab stops away from my
subform. They can tab/click right past "Due Date" and never know they
missed something that was required until 5 steps later. I think this
is adding to confusion as well - since they're most likely going to
look in the last field they were in to try and fix the problem, when
in reality, they need to go back farther than that.

Make sense? Any idea how I can have these messages pop up "On exit"
basically, without coding? If coding is needed, I guess that's fine,
but please give me some hint as to what I need to make it work and
give the user a custom message instead of the default messages Access
gives.

Thanks.

On Mar 7, 6:03 am, "Don Leverton" <[e-mail]>
wrote:

>Hi,

IMHO, it sounds like it would just be easier to set the "Required"
property
to "Yes" in the table design view in a case like this.

HTH,
Don

"gweasel" <[e-mail] in message

news:11**********************@64g2000cwx.googlegr oups.com...

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

[gweasel]

On Mar 7, 2:24 pm, "Larry Linson" <[e-mail]:

Table validations are applied when you write the Record, and that's what
happens when you go from a main Form to the Form embedded in a Subform
Control. You can "protect yourself" with Table-level validation and _also_
protect your users from unfriendly error messages, by _also_ including
validation in VBA code in the AfterUpdate of Controls on the Form. (Yes, I
know -- extra work... frustrating, isn't it?)

Larry Linson
Microsoft Access MVP

"gweasel" <[e-mail] in message

news:11********************@30g2000cwc.googlegroup s.com...

I have set the Required property to yes and moved the validations to
the table level instead of the form level. I like to have the
validation rules in effect though because the error messages that come
up when only the Required property are set are not helpful to the end
user (they refer to the field name as "[Tbl_Name].[Field_Name]" and
that is confusing to the users.

I am actually having an issue though. I swear this was working
earlier in the week, but even before moving the rules to the table
level, when I went through to test the system, I am able to breeze
right through the supposedly required fields/controls. Now, if I am
switching from a form to the subform, I get the unhelpful "required
property" error message, but never see the validation text I typed
out. And ideally, I'd want this message to pop up when they are
trying to exit the required control on the form rather than when
they're trying to exit another control to get into a subform. For
example, "Due Date" is required, but it is 5 tab stops away from my
subform. They can tab/click right past "Due Date" and never know they
missed something that was required until 5 steps later. I think this
is adding to confusion as well - since they're most likely going to
look in the last field they were in to try and fix the problem, when
in reality, they need to go back farther than that.

Make sense? Any idea how I can have these messages pop up "On exit"
basically, without coding? If coding is needed, I guess that's fine,
but please give me some hint as to what I need to make it work and
give the user a custom message instead of the default messages Access
gives.

Thanks.

On Mar 7, 6:03 am, "Don Leverton" <[e-mail]>
wrote:

Hi,

IMHO, it sounds like it would just be easier to set the "Required"
property
to "Yes" in the table design view in a case like this.

HTH,
Don

"gweasel" <[e-mail] in message

>news:11**********************@64g2000cwx.googlegr oups.com...

What is the best way to apply a Validation Rule - or rather, where is
the best place to put it? Is there an advantage to putting it on the
field in the table vs setting the validation rule on the form the
control is on?

Basically I have a number of controls in a form that are required, and
to check it I am setting the Validation Rule to "<>"IsNull" so that
when the user tries to tab through/click out of a required area
without entering anything, they are alerted. I noticed though, after
I'd set up a few controls on the form this way, that the same settings
are also available when creating the table that drives the form.

Just curious and want to fix possible problems before I release the
system to the end users.

Thanks.

It's not that much extra work - just one of those things I thought
(like so many others) would be included in the properties or regular
functions. But, alas, coding it is. Thanks for the advice and
explanations.

[missinglinq via AccessMonster.com]

The question here really is, are you satified with your users getting the
rather unhelpful (as was metioned) Access messages about validation having
been failed (if set on the table level) or do you want them to receive a
cogent message that you've written (if validation is done on the form level)
explaining what they need to do. I deal solely with non-power users, and
always do validation on the form level, with simple, clear explanations of
what hasn't been done and howw to correct it.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[Rick Brandt]

missinglinq via AccessMonster.com wrote:

The question here really is, are you satified with your users getting
the rather unhelpful (as was metioned) Access messages about
validation having been failed (if set on the table level) or do you
want them to receive a cogent message that you've written (if
validation is done on the form level) explaining what they need to
do. I deal solely with non-power users, and always do validation on
the form level, with simple, clear explanations of what hasn't been
done and howw to correct it.

When I am worried about that (which is usually) I do both. One is important and
one is "frosting".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@hotmail.comwrote in
news:ne*************@newssvr29.news.prodigy.net:

missinglinq via AccessMonster.com wrote:

>The question here really is, are you satified with your users
getting the rather unhelpful (as was metioned) Access messages
about validation having been failed (if set on the table level)
or do you want them to receive a cogent message that you've
written (if validation is done on the form level) explaining what
they need to do. I deal solely with non-power users, and always
do validation on the form level, with simple, clear explanations
of what hasn't been done and howw to correct it.

When I am worried about that (which is usually) I do both. One is
important and one is "frosting".

I would say one is essential and the other is helpful.

If you're validating it at the form level, insofar as is possible,
the underlying table should already have validation rules for the
same thing defined.

Why?

Because you can't always know for certain that all your data will be
entered through that particular form.

This is why we use RI at the engine level.

It's why we use any data integrity enforcement at the engine level.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[onedaywhen]

On Mar 8, 4:09 am, "David W. Fenton" <[e-mail]>
wrote:

If you're validating it at the form level, insofar as is possible,
the underlying table should already havevalidationrules for the
same thing defined.

Why?

Because you can't always know for certain that all your data will be
entered through that particular form.

Another reason is you can't be sure your form is, and will always be,
bug free.

Jamie.

--

[onedaywhen]

On Mar 7, 9:24 pm, "Larry Linson" <[e-mail]:

Table validations are applied when you write the Record, and that's what
happens when you go from a main Form to the Form embedded in a Subform
Control. You can "protect yourself" with Table-level validation

[Slightly picky] It may be referred to as a "Table Validation Rule"
and be defined it the table's property pages in the Access UI but it
is not actually table-level. Rather, it is record-level because it can
only refer to values in the same record (row).

A ACE/Jet CHECK constraint, however, is genuinely table-level because
it can refer to values on other rows in the same table. FWIW, although
it can additionally refer to values in other tables, it is not
actually schema-level because its table must be targeted by the INSERT/
UPDATE/DELETE statement for the constraint to be checked.

Jamie.

--