Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Required field Validation Rule

Posted on 2006-11-16
16
Medium Priority
?
2,350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 66

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 400 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 400 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 800 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 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