access 2010 form validation rule property versus vba validation


Just a quickie. I am aware that vba validation on a form provides the ability for more complex form field validation. However, when simple validation is required on a textbox i.e if a value entered should be between 0 And 100, is using the built in validation rule property for that control advisable to use or are there any issues associated with this when rolling the system out?

Many thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
using the built in validation, you have to accept the default message box, unlike the using VBA validation, you can format/compose your own text for the message box.
andrewpiconnectAuthor Commented:
im happy with the default msgbox and format (have got it to show my own title as opposed to "Microsoft Access" and composed my own text). My query is are there any pitfuls or reliability issues with using the built in validation rule/text.

my form has over 60 controls on it, 40 of which run adifferent function AfterUpdate and already has a fair amount of vba behind the scenes for other claculations and tasks, i was trying to avoid having another 40 vba sub procedures in the crowded form module but was concerned there are compromises apart from the msgbox formatting.

many thanks
Jeffrey CoachmanMIS LiasonCommented:
Here is the deal...
The reason why many new Access developers see the need for validation on each control is that they want to make sure the data is valid as the user cycles through each control in form.

Despite the allure of this design, it is inefficient.
It does not take into account the fact that the user may not follow the standard tab order,and it cannot predict every state the control, or form, will be in.

The more standard approach is to validate all the fields before the record is updated.

Think of this like a web form.

When you fill out a web form, you fill out each field, and nothing stops you from leaving a straight text field blank, for example (or enter an email address without the "@" symbol).

Only when you hit the "Submit" (or "Continue", ...ect.)button does the form alert you of any invalid entries.

This way all your validation code is in one sub.

Now, there are valid reasons for putting validation on all subs.
So, I see nothing "wrong", with doing this

If the values can only be between 1 and 100, then give them a combobox with only those values.
You can also employ the Tool tips (ControlTipText) as a way to "remind" users what is a valid entry

Finally, remember that we don't have access to your existing code, so we cant see if anything can be "tightened up".
We also don't know of all your "constraints".


andrewpiconnectAuthor Commented:
Hi Jeff,

thanks for your explanation.

I shall expand a little as there is a valid reason why i want validation on each control.....
My form has 40 controls that are defaulted to "0".
The user does not have to enter a value in all 40 controls...sometimes 2 controls, sometimes 30.
The value has to be between 1 and 100 but decimal places are allowed, so a user can enter 1.5, 9.6, 10.2, 15.6 etc ( a combo would have too many rows to allow selection of the reqd value).
I normally do my validation in vba only and this in the beforeUpdate event and alerts the user, highlight the incorrect field(s) in red that need amending etc and cancel the update.
In order to avoid showing the user possibly 40 incorrect values in the above controls after validation i wanted to validate each one after the user has entered data.
This will allow a more streamlined user journey throughout the form.
As each control is defaulted to "0" it doesnt matter whether or not they enter data as long as when they do, it is of the required format (Between 0 And 100 (decimals allowed)).

As i do not normally use the access built in validation properties i wanted to know whether they we're unreliable and prone to errors (whilst being aware of their limitations).

As always, your help and advice is appreciated
Jeffrey CoachmanMIS LiasonCommented:
No, there is nothing "wrong" with table level validation, ...only what the previous expert mentioned (they are not very "customizable")

One *advantage* to using validation at the table level is that the validation will follow the "Table", if you ever moved it to another Access db.
Another is that it "lightens" up the app a bit more. (more code, ...more problems)
Most issues with distributed Access apps is in the code, and lack of error handling.
So the less code you have, the less susceptible you are to these issues.

In other words, there is nothing wrong with using either approach, long as you are aware of the pros and cons of each.

Knowledge Is Power



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.