Solved

Required field Validation Rule

Posted on 2006-11-16
16
2,342 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 119

Expert Comment

by:Rey Obrero
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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now