Solved

Required field Validation Rule

Posted on 2006-11-16
16
2,339 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

20 Experts available now in Live!

Get 1:1 Help Now