A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Formatting and Validation Rule



 
 
Thread Tools Display Modes
  #1  
Old February 9th, 2010, 06:45 PM posted to microsoft.public.access.forms
oldblindpew
external usenet poster
 
Posts: 128
Default Conditional Formatting and Validation Rule

Please correct me on this: in a form control (Text Box) if you use a
Validation Rule, you can have an audible tone and a Validation Text, so it is
clear to the user that something is wrong, however, the offending field is
not highlighted. On the other hand, you can go with Conditional Formatting,
so the field changes appearance, but there's no alarm and no validation text!
Doesn't seem odd? How do you work around this?

This question has probably been asked and answered before, but I was unable
to locate the needle in the haystack. Could anyone give me a pointer on how
to do more than a one-word search on this forum?

Thanks,
OldBlindPew
  #2  
Old February 10th, 2010, 03:07 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Conditional Formatting and Validation Rule

On Tue, 9 Feb 2010 09:45:01 -0800, oldblindpew
wrote:

It's not odd to me. I think the Access team wants to provide you with
a set of base features which you can use to put together your app. I
would be HORRIFIED if a violation of validation would color the field.
In the rare case I want that, I would program it myself (e.g. using
VBA code in the control_BeforeUpdate or Form_BeforeUpdate).

Conditional Formatting is not primarily to be used for validation. It
has many other broader applications.

People search the newsgroups in many different ways. "this forum" is
not clear enough to me. Often I would use groups.google.com to start
my search.

-Tom.
Microsoft Access MVP


Please correct me on this: in a form control (Text Box) if you use a
Validation Rule, you can have an audible tone and a Validation Text, so it is
clear to the user that something is wrong, however, the offending field is
not highlighted. On the other hand, you can go with Conditional Formatting,
so the field changes appearance, but there's no alarm and no validation text!
Doesn't seem odd? How do you work around this?

This question has probably been asked and answered before, but I was unable
to locate the needle in the haystack. Could anyone give me a pointer on how
to do more than a one-word search on this forum?

Thanks,
OldBlindPew

  #3  
Old February 10th, 2010, 07:23 PM posted to microsoft.public.access.forms
oldblindpew
external usenet poster
 
Posts: 128
Default Conditional Formatting and Validation Rule

Thanks for replying.

When filling out a form on the internet (say) and you leave out or otherwise
bungle some required bit of information, the application stops, tells you
there is some kind of problem, and highlights or changes the color of the
field where the problem lies. I don't see anything horrific about this.

As it is, Access automatically provides an interruption, a tone, and a
message box whenever there is a violation. I thought Conditional Formatting
might be used in conjunction with the Validation Rule, to gain this
functionality, but the two don't seem to work together. The nifty built-in
features of Access often fall just short of the goal, leaving one to face the
truly horrific prospect of VBA.

However, for now, I have decided to be content with just changing the font
color via Conditional Formatting when something is amiss. Hopefully the user
will get the hint and correct his mistake, but this is not best practice
because it only provides a warning, rather than a prohibition, of invalid
data.

Pew

"Tom van Stiphout" wrote:

On Tue, 9 Feb 2010 09:45:01 -0800, oldblindpew
wrote:

It's not odd to me. I think the Access team wants to provide you with
a set of base features which you can use to put together your app. I
would be HORRIFIED if a violation of validation would color the field.
In the rare case I want that, I would program it myself (e.g. using
VBA code in the control_BeforeUpdate or Form_BeforeUpdate).

Conditional Formatting is not primarily to be used for validation. It
has many other broader applications.

People search the newsgroups in many different ways. "this forum" is
not clear enough to me. Often I would use groups.google.com to start
my search.

-Tom.
Microsoft Access MVP


Please correct me on this: in a form control (Text Box) if you use a
Validation Rule, you can have an audible tone and a Validation Text, so it is
clear to the user that something is wrong, however, the offending field is
not highlighted. On the other hand, you can go with Conditional Formatting,
so the field changes appearance, but there's no alarm and no validation text!
Doesn't seem odd? How do you work around this?

This question has probably been asked and answered before, but I was unable
to locate the needle in the haystack. Could anyone give me a pointer on how
to do more than a one-word search on this forum?

Thanks,
OldBlindPew

.

  #4  
Old February 11th, 2010, 02:59 PM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Conditional Formatting and Validation Rule

On Wed, 10 Feb 2010 10:23:02 -0800, oldblindpew
wrote:

Then you're doing validation incorrectly. Say I have a textbox named
myText and I want the user to enter a number greater than 10. I could
write this in myText_BeforeUpdate event:
if Me.myText = 10 then
msgbox "Yo! Greater than ten!", vbExclamation
Cancel = True
end if

The "Cancel=True" line is essential, and stops the user from doing
anything else until the requirement is satisfied, or she hits Esc to
cancel the record.

Note: in this example I am not using ValidationRule, ValidationText
either at the control level or the table field. Years go by that I
don't use these properties.

-Tom.
Microsoft Access MVP



Hopefully the user
will get the hint and correct his mistake, but this is not best practice
because it only provides a warning, rather than a prohibition, of invalid
data.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.