Custom Validation Rules

[TonyB]

In an access db is it possible to add vba code to provide customized
validation rules on a field ? For instance, say I I have an email field in a
record, and so I want to write some code to check that the field contains a
string of form string@string ? If so can anyone point me to some help on how
to do it ?
Regards
Tony

 

[Allen Browne]

To programatically set the Validation Rule property for a field named Email
in a table named Table1, try something like this:

Currentdb.TableDefs("Table1").Fields("Fee").ValidationRule = _
"Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))"

 

[TonyB]

Hi Allen,
Thanks for the reply. I didn't explain myself clearly. I was looking for a
general method of attaching some vba code to the field in a record so that
when a new record is entered that this code can be run to check the data for
that field is OK Basically I am trying to customize the validation rules you
get in table design view.
Since I posted I have been searching for info about this, and I think I have
concluded this is probably not possible in a table, and that this should be
done in the form used to enter the data into the table, rather than in the
table. Would you agree ?

Thanks
Tony

 

[Allen Browne]

Yes, you would not normally modify the ValidationRule property of a table
field once the database is in use, though you might need to create or clear
a rule as part of a version update on a database that is already installed.

If you have a Validation Rule in place in the field of the table or in the
table itself, you don't need any code. If the validation is too complex to
use the rule in the table, or if you want to give the user the chance to
override it and make the entry anyway, then it makes sense to use the form
events for this. Use the BeforeUpdate event of the control to validate one
field (e.g. to make sure it is in range), or the BeforeUpdate event of the
form to validate the record (e.g. to compare values between fields, or to
ensure that an entry was made in a field.)

Again, once you have this code in place, you would not normally need to
change the code, unless you need it for a version update.

Hope that's useful

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
Thanks for the reply. I didn't explain myself clearly. I was looking for a
general method of attaching some vba code to the field in a record so that
when a new record is entered that this code can be run to check the data
for
that field is OK Basically I am trying to customize the validation rules
you
get in table design view.
Since I posted I have been searching for info about this, and I think I
have
concluded this is probably not possible in a table, and that this should
be
done in the form used to enter the data into the table, rather than in the
table. Would you agree ?

Thanks
Tony

To programatically set the Validation Rule property for a field named Email
in a table named Table1, try something like this:

Currentdb.TableDefs("Table1").Fields("Fee").ValidationRule = _
"Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))"

 

[Jeff Boyce]

Tony (and Allen)

Pardon my intrusion...

In Access/JET, the tables have no "triggers" (unlike SQL Server). If you'll
be using JET as your back-end for your data, you'll want to do the (more
complex) validation via the forms. It is still possible to add a validation
rule to fields in a table, but only one, and only if it doesn't relate to
other fields. You can also set ONE table-level validation rule.

The consensus is that form-level validation testing is much more robust when
you are using Access/JET.

--
Good luck

Jeff Boyce
<Access MVP>

Hi Allen,
Thanks for the reply. I didn't explain myself clearly. I was looking for a
general method of attaching some vba code to the field in a record so that
when a new record is entered that this code can be run to check the data for
that field is OK Basically I am trying to customize the validation rules you
get in table design view.
Since I posted I have been searching for info about this, and I think I have
concluded this is probably not possible in a table, and that this should be
done in the form used to enter the data into the table, rather than in the
table. Would you agree ?

Thanks
Tony

To programatically set the Validation Rule property for a field named Email
in a table named Table1, try something like this:

Currentdb.TableDefs("Table1").Fields("Fee").ValidationRule = _
"Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))"

 

[Allen Browne]

As always, Jeff, your comments are welcome.

I'm interested in your suggestion that code is more robust? That's not my
experience. I strongly prefer the engine-level rules where they are
suitable, because it takes a load off me as developer to remember to enforce
them regardless of how the data is added (e.g. action query).

So, in every database I use:
- enforced RI on most foreign keys;
- Required property of appropriate fields;
- AllowZeroLength set to No for almost all text fields;
- Field-level Validation Rules for range checking;
- Table-level Validation for comparison between fields.

The fact that there's only one table-level rule has never bothered me: you
can combine rules with AND, OR, etc, with appropriate bracketing.

Certainly, the form-level code is more powerful, and the only choice for
warnings that you allow the user to override. And I will admit to running
Form-level code for most of the cases above as well, because it seems good
practice to avoid unnecessary write-attempts. Maybe that's what you were
referring to?

Overall, though, the idea of engine-level validation for the simple and
crucial rules is very appealing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tony (and Allen)

Pardon my intrusion...

In Access/JET, the tables have no "triggers" (unlike SQL Server). If
you'll
be using JET as your back-end for your data, you'll want to do the (more
complex) validation via the forms. It is still possible to add a
validation
rule to fields in a table, but only one, and only if it doesn't relate to
other fields. You can also set ONE table-level validation rule.

The consensus is that form-level validation testing is much more robust
when
you are using Access/JET.

--
Good luck

Jeff Boyce
<Access MVP>

Hi Allen,
Thanks for the reply. I didn't explain myself clearly. I was looking for
a
general method of attaching some vba code to the field in a record so
that
when a new record is entered that this code can be run to check the data for
that field is OK Basically I am trying to customize the validation rules you
get in table design view.
Since I posted I have been searching for info about this, and I think I have
concluded this is probably not possible in a table, and that this should be
done in the form used to enter the data into the table, rather than in
the
table. Would you agree ?

Thanks
Tony

To programatically set the Validation Rule property for a field named Email
in a table named Table1, try something like this:

Currentdb.TableDefs("Table1").Fields("Fee").ValidationRule = _
"Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))"

 

[Jeff Boyce]

Allen

I'll wax philosophical for a moment...

I trust RI to the system, and don't try to handle this myself. Similarly,
zls and "required" settings I handle in the table.

But I find that much of the field-level validation I need to enforce loosely
falls into the category of "business rules". In the interest of keeping
those separated from the actual data (?n-level? design), I place these in
the forms.

Enough with the philosophy, where's Doug S. -- I think he owes me a dark,
chewey beer!

Jeff Boyce
<Access MVP>