Solved

Required field Validation Rule

Posted on 2006-11-16
16
2,345 Views
Last Modified: 2009-12-16
Hi all,

A2003 SP2:

     In my continuing quest for an easier way to do simple field validation I started looking at the "Validation Rule" property of fields while in table design. I just can't seem to get it to pop my "Validation Text" if the field's "Required" property = Yes    ...   no matter what I put in the validation Rule!!!

Not Is Null
Is Not Null
<>""

My current test is on a Text field.

I don't want form code because Form_BeforeUpdate does not catch Required fields before Form_Error barfs it up and I already wrote the code to handle this but it is a royal PITA and if I can get the field validation to work in the table then I bet I can use the same for the Validation Rule for the control on a bound form.

TIA,
Steve
0
Comment
Question by:stevbe
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17957069
>My current test is on a Text field.
I thought that the absense of a value in a text field would be an empty string "" and not a NULL.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17957146
did you set Allow Zero Length to NO
0
 
LVL 39

Author Comment

by:stevbe
ID: 17957155
so what expression are you suggesting that I use for my rule?


...
I just found this in 2007 help online and my guess (cobined with my tests) is that is is true for 2003 also

IS NOT NULL Forces users to enter values in the field. This is the same as setting the Required field property to Yes. However, when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly error message. Typically, your database is easier to use if you use IS NOT NULL and enter a friendly message in the Validation Text property.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 39

Author Comment

by:stevbe
ID: 17957211
<did you set Allow Zero Length to NO>
yes
0
 
LVL 39

Author Comment

by:stevbe
ID: 17957365
So ... if I turn Required off and then set the Validation Rule to Is Not Null, put in a decent message for Validation Text, I can then apply complete logic (using custom functions) in the control's & form's BeforeUpdate?

I guess I am gonna have to start thinking about how I develop apps a bit differently as I typically do all of the validation messaging in the FE.

0
 
LVL 39

Author Comment

by:stevbe
ID: 17957578
this is turning further into a pile of #$%^&*()_

so what rule is used to make sure they entered a number in a Long filed (instead of say the letter 'a') ... or even a number that is too large for the data type specified ... grrrr... does not exist :-(

so there is no way except capturing form_error event in a bound form to over-ride the default bogus Access messages !!!

Please ... someone tell me I am wrong :-)

hey ... where are all of the unbound form champions ... they could tell me I can avoid all this :-)
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 17957684
Not sure if this helps, but...
http://msdn2.microsoft.com/en-us/library/aa195900(office.11).aspx

The @ character works in the Format property, but not in the Input Mask or Validation Rule property.
0
 
LVL 3

Assisted Solution

by:incrediblejohn
incrediblejohn earned 100 total points
ID: 17957786
try this:
Len(yourfield & "") <> 0

What it does is turns any nulls into emtpy strings so it covers all options
0
 
LVL 39

Author Comment

by:stevbe
ID: 17957924
jim: @ format did not help
john: Len(yourfield & "") <> 0 did not help

I am now looking at calling the BeforeUpdate for the ActiveControl in the Form_Error event? It would be nice if I could use the CallByName here also ...

I'll leave this open for a bit longer to see if there any other suggestions.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17957994
stevbe
tried john suggestion in a *bound* textbox
setting in the table for field Test

Validation Rule Len([Test] & "")<>0
Validation Text You have to Enter a value
Required No
Allow Zero Length No

i got the message  "You have to Enter a value"
when i tabbed out of the textbox
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
ID: 17958005
Hi (late to the party again huh :-S)

Have your interests shifted now Steve?  From doing this all in the tables (always gonna be something of a struggle with a Jet BE) to more friendly FE messages - without nasty default field ones popping up?
0
 
LVL 39

Author Comment

by:stevbe
ID: 17958142
I would like to know if there is a way to do it in tables but if not ... I should open a new Q to dole out some more points.

The more I look into this there does not seem to be a way to capture bad data types in field validation so I need to go back to using Form_Error ... so I might as well capture the .Required error there also ... I like to keep stuff together.

Steve
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 200 total points
ID: 17958164
when i set the Required property to YES

the Validation Text did not appear,
the Access default message appeared.
0
 
LVL 39

Author Comment

by:stevbe
ID: 17958207
thanks cap ... that verifies my findings :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17958209
Are we not wanting a bit more of a global solution now - than worrying about the Required/Is Null issue alone Steve?
0
 
LVL 39

Author Comment

by:stevbe
ID: 17958328
required(3314) and data types(2113) are the biggest headache.

question hopping ... CallByName?
http://www.experts-exchange.com/Databases/MS_Access/Q_22063335.html
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question