programmatically setting validation rules

[Demyan]

Hi,
Could you please suggest me a way to set validation rules programmatically,
rather than manually, opening a table in 'Design' view and setting
'Validation rule' property for each data column?
Thank you very much!

 

[Allen Browne]

Try something like this:

Currentdb.TableDefs("MyTable").Fields("MyField").ValidationRule = "Is Not
Null"

For an example of how to loop through the Fields of a TableDef, see:
http://members.iinet.net.au/~allenbrowne/func-06.html

 

[Demyan]

Thank you, Allen!

 

[Demyan]

Allen, when I try to run the following simple code - I go through variables
have in the database, and set for some of them a 1-to-5 value range -

Option Compare Database
Sub DimaTest()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
For Each tdf In db.TableDefs
For Each fld In tdf.Fields
fName = Mid(fld.Name, 2, 4)
If fName = "data" Or fName = "dyta" Then
fld.ValidationRule = "BETWEEN 1 AND 5"
End If
Next
Next
End Sub

I get error message 'Property value is too large', with validation-rule
highlighted. Can you tell me what's wrong?
Thank you very much.

 

[Allen Browne]

If you have a large number of fields in the table (150+), you may have
struck this problem:
http://support.microsoft.com/?id=323657

 

[Demyan]

Hi, Allen,
No, my database is small, and field names short. But I accidentally solved
the problem, whatever it was, by re-typing 'BETWEEN' without caps!
Thank you so much for help!

 

[Allen Browne]

Really? What version?

(I tried it in A2003, using caps. It accepted the entry, but converted to
proper case in the rule.)