Evaluating validationrule

[Leon L]

Hi,

In a VBA routine I want to check a value against a field.validationrule, but
I don't know how to implement this in a generic way (maybe with Eval()?).

Example:
A field called "ABC" with validationrule: "A" or "B" or "C"
Now I want to check if a VBA variable complies to this rule.

The only way I see is to parse the validationrule string and change it so
that I get something like:

(pseudocode)
If var="A" or var ="B" or var="C" then
OK
else
not OK
end if

But this technique is probably getting too complex to use it in a generic
way for all possible validationrule expressions. Would there be a way to use
Access builtin functionality to evaluate the expression?

Any suggestions are welcome!

Leon

 

[Tim Ferguson]

But this technique is probably getting too complex to use it in a
generic way for all possible validationrule expressions.

I cannot think of any reason why one would want a generic solution for
this. There is not much reason not to know what the VR is for a field you
are creating a control for: it should be part of the documentation of the
db interface. As a fall-back, you can catch the Form_Error event to prevent
the user from seeing any database engine error messages.

If you want to do it like this, then I don't think you can always write the
ValidationRule expressions so that they are Eval() friendly:

MyField In("A","B","C") or MyField Is Null

will fail because the second part needs to be IsNull(MyField) rather than
the SQL expression. I guess it would be possible to write an entire
expression parser to test the value, but it would be huge and, in my view,
redundant.

If you want to read the VR for interest anyway, it's in the field's
Properties collection

Public Function VRule(SomeField as DAO.Field) As String
' the property does not exist until a rule is created
On Error Resume Next

' look up the properties collection
VRule = SomeField.Properties("ValidationRule").Value

' No value?
If Err.Number <> 0 Then
' A rule of TRUE will always evaluate to TRUE!!
VRule = "TRUE"

End If

' All Over
End Function


Hope that helps


Tim F

 

[Leon L]

Tim,

Thanks for your respons!

I cannot think of any reason why one would want a generic solution for
this. There is not much reason not to know what the VR is for a field you
are creating a control for: it should be part of the documentation of the
db interface. As a fall-back, you can catch the Form_Error event to prevent
the user from seeing any database engine error messages.

I'm writing a little import program to add records to a table of choice in a
database.
Now I want to make sure that the whole import file can be added and there
are no validation errors (as I see so often if I write an append query).

So I test the values for all columns in all the rows of the import file
whether they pass all the various constraints that may be on (the fields of)
a table (like required, lookup, validation rule, etc.)

All results are shown visually in a form and written to a logfile.
This is all done through vba.
That is the explanation why I would like the solution to be generic.
Pity there doesn't seem to be a solution except of writing a complex
expression parser. I was hoping I could use access' built in parser for that.

Now another way might be to let access do the work:
1. Create an unbound control (textbox) on the form
2. Set validationrule and validationtext to that of the corresponding table
field
3. and trap the validation check while setting the value of that textbox to
the value of the import column for a specific row through vba.

I don't know how to that. Maybe you have a suggestion for this.

Leon

 

[Tim Ferguson]

I'm writing a little import program to add records to a table of
choice in a database.
Now I want to make sure that the whole import file can be added and
there are no validation errors (as I see so often if I write an append
query).

I'm sorry: this really sounds like a Design Problem in search of a
solution. "A table of choice" is really worrying: you just cannot dump a
list of OrderLines into the CustomerAddresses table because the user
"chooses" to. If you are importing Addresses, then you already know the
domain constraints for each of the fields in the Addresses table; and so
on.

One way round would be to use Access itself to do the import rather than
VBA. Use DoCmd.RunSQL with a SELECT INTO command, or use the
TransferWhatever command: this should give you a table of ImportErrors
which you can then read back into your log. If neccessary, you can tag the
imported records and delete them back out again if the import was
unsuccessful.

IME, data cleaning is something that has to be done slowly and by hand,
record by record. Programmatic cleaning seems to be something of a
specialist skill.

Just a thought


Tim F