473,898 Members | 3,371 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Validation Rule on Table Field vs Form Control

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.

Mar 7 '07 #1
10 5747
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
Mar 7 '07 #2
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.goog legroups.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.

Mar 7 '07 #3
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" <leveriteNoJunk M...@telusplane t.net>
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" <bane...@yahoo. comwrote in message

news:11******** **************@ 64g2000cwx.goog legroups.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.

Mar 7 '07 #4
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******** ************@30 g2000cwc.google groups.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" <leveriteNoJunk M...@telusplane t.net>
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" <bane...@yahoo. comwrote in message

news:11******* *************** @64g2000cwx.goo glegroups.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.


Mar 7 '07 #5
On Mar 7, 2:24 pm, "Larry Linson" <boun...@localh ost.notwrote:
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" <bane...@yahoo. comwrote in message

news:11******** ************@30 g2000cwc.google groups.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" <leveriteNoJunk M...@telusplane t.net>
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" <bane...@yahoo. comwrote in message
>news:11******* *************** @64g2000cwx.goo glegroups.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.

Mar 7 '07 #6
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.c om
http://www.accessmonster.com/Uwe/For...ccess/200703/1

Mar 8 '07 #7
missinglinq via AccessMonster.c om 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
Mar 8 '07 #8
"Rick Brandt" <ri*********@ho tmail.comwrote in
news:ne******** *****@newssvr29 .news.prodigy.n et:
missinglinq via AccessMonster.c om 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/
Mar 8 '07 #9
On Mar 8, 4:09 am, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:
If you're validating it at the form level, insofar as is possible,
the underlying table should already havevalidationr ules 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.

--
Mar 8 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
15929
by: Dalan | last post by:
I apparently need a bit of assistance regarding the structure of some validation code on the BeforeUpdate or AfterUpdate event on a form for several fields that need to controlled. I did search the archives, but found nothing precise enough to address my specific needs. 1. There are three text box fields on the form and one of the three need to have a text value provided. I simply need to ensure that one field has data, not two, not...
2
2697
by: Joey P | last post by:
Hi all, I am doing a project for university whereby i have to implement a simple database related to a frozen foods company. I am having some trouble though creating a validation rule for one of my fields. I have a table called "Product" and two of the fields included in this table are "Cost Price" and "Retail Price". I need to create a validation rule so that the Cost Price is always less than the Retail Price. I have tried...
2
320
by: Tony Williams | last post by:
I am validating two text boxes to make sure they contain data. I have use Is Not Null in the Validation Property of the control but it doesn't seem to work. The Help shows Validation with code. How do I write the rule to check the user has completed the control? TIA Tony Williams
18
9057
by: Steve | last post by:
Hi I have a really weird problem and any assistance would be welcome. I have developed an app in Access 2002. The app runs perfectly on the development machine. I have packaged the app using the Microsoft XP Developer Packaging Wizard (Service Pack 1). The 1st 3 releases of the app ran perfectly on site.
2
3889
by: Kostas | last post by:
Just a quick question on this issue. Assume a small set of fixed values for a field. For instance Field Gender, values: Male, Female, Unknown If I create a Combo Box with the above values, I believe I am adding a form-level validation. Is it wise to also include the table-level validation for the above constraint? For instance, go to the field's validation rule property in Design Mode and
6
3698
by: Chuck | last post by:
A97. A database has a table: tblA which has a single text field, B. It is a primary field, indexed and no duplicates. It is used as a lookup for table tblC. A form based on tblA is used to add new records. The Validation Rule: <>!! was made using the expression builder. The rule produces this error: The expression you entered in the Form control's Validation Rule doesn't contain the Automation object 'Tables' If I use simply: ...
5
1824
by: Don Sealer | last post by:
I've got a validation rule that says something like =xx or xxx or zzzz. Though I may not have described it well it does work fine and that is really not my question. Here's what I'd like to do. Let's say that I choose zzzz, I'd like for a particular control (or text box) in my form to become required. At other times, if xx or xxx is inputed it would not be required. Is this possible? Thanks, and as always, further explanatiions...
3
2039
by: BrianB830 | last post by:
Hello all, I have a quick question regarding an MS Access database I'm creating. In the entity "ORDER", I have the attributes "Order Date" and "Delivery Date". I need to create a validation rule here. The delivery date must come after the order date. Would anyone be able/willing to help?
7
5980
by: sharsy | last post by:
Hi guys, I would like to setup a validation rule for a database in microsoft access that restricts data entry so that a certain field can only be filled in if another field has a specific answer (that is selected via a drop down list). Example Field1 - options are "In" or "Out" Field2 - options are "Join" or "Not Joining"
1
10949
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10484
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8036
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5882
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6077
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4706
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.