access 2010 form validation rule property versus vba validation

Posted on 2013-09-20
Medium Priority
Last Modified: 2013-09-20

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
Question by:andrewpiconnect
  • 2
  • 2
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 39508911
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.

Author Comment

ID: 39508949
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39509172
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".



Author Comment

ID: 39509264
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
LVL 74

Accepted Solution

Jeffrey Coachman earned 1600 total points
ID: 39509899
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, ...as long as you are aware of the pros and cons of each.

Knowledge Is Power



Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

588 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