Conditional Formatting and Validation Rule

  • Thread starter Thread starter oldblindpew
  • Start date Start date
O

oldblindpew

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
 
On Tue, 9 Feb 2010 09:45:01 -0800, oldblindpew

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
 
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
 
On Wed, 10 Feb 2010 10:23:02 -0800, oldblindpew

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
 
Back
Top