How can a set of validation rules be applied for all forms that save to a specific table?

Question

I have multiple forms that write to the same table. I want all data written to that table to be validated with the same rules. I can not find a way to apply the validation at the table level. It looks like it will have be done on a per-form basis using BeforeUpdate to call my vba.

I am running Access 2013. The validation of some fields will be based on the contents of other fields in the same record. I will need to do string manipulations along the lines of the following pseudo-code:

for field.Serial; do
  if ( field.Model == "FORKLIFT" ); then
    validation_good_if( (left(field.Serial, 2) == "FL") && (length(field.Serial) == 5) && (right(field.Serial, 3) isNumeric) )
endFor

for field.AssetTag; do
  field.AssetTag = right("0000" & field.AssetTag, 8)
endFor

for field.Model, field.Location; do
  toUpperCase
endFor

What is the best way to make sure all inserts/updates to my table undergo the same validation?

Which version of Access are you using? It may be that data macros (like triggers) will suit. These are available since 2010: http://stackoverflow.com/a/15044797/2548 — Fionnuala, Jan 20 '14 at 16:44
I had considered using the Before Change macro trigger on the table but could not find a way to leverage the power of VBA to do string processing. — pierce.jason, Jan 20 '14 at 16:53
How much string processing? Perhaps you could add an example? — Fionnuala, Jan 20 '14 at 16:58
You can most certainly use expressions in the table macros that are public VBA functions. However, if possible I would stick to 100% native table code if possible. The basic string functions such as left, right, mid, instr are available. And the "regex" like pattern matching also is available. So I much with others here – you might want to give some examples of the kind of string processing required since to my knowledge much if the not the same set of string functions are available in table macros. — Albert D. Kallal, Jan 21 '14 at 01:29
Related post: [What functions can be used in MS Access data macros?](http://stackoverflow.com/questions/21288011) — pierce.jason, Jan 22 '14 at 16:08

Gord Thompson · Accepted Answer · 2014-01-21T20:13:15.967

This question sounds like a good candidate for a Data Macro. Here's what I came up with:

enter image description here

I also tested this by trying to perform an INSERT from an external program (C#, using ODBC) and the Data Macro works in that context, too:

cmd.CommandText = "INSERT INTO Table1 ([Model], [Serial]) VALUES (?,?)";
cmd.Parameters.AddWithValue("?", "FORKLIFT");
cmd.Parameters.AddWithValue("?", "FL21");
try
{
    cmd.ExecuteNonQuery();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
}

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Validation failed for FORKLIFT: Serial Number must be five (5) characters long.

When the validation succeeds the record is correctly inserted into the table.

How can a set of validation rules be applied for all forms that save to a specific table?

1 Answers1