Altering validation rules for all fields in a table through VBA

[alevol]

Hi all.

I'm new to Access and VBA. I have to change the validation rules of every field (except the record ID) in several tables that have about 200 fields each (they were made to for entering the answers of research questionnaires). I'm thinking the best way to do this is with a VB script. I found this on a microsoft developers website and was hoping it can be adapted to cycle through all the fields in a table and change the validation rule property (which will always be the same for all the fields) and was hoping someone might be able to help me out with how to do this. Thanks a lot!

Dim strTblName As String, strFldName As String
Dim strValidRule As String
Dim strValidText As String, intX As Integer

strTblName = "APQ"
strFldName = "Question1"
strValidRule = ">=1 And <=5"
strValidText = "Please use a value between 1 and 5."
intX = SetFieldValidation(strTblName, strFldName, _
strValidRule, strValidText)

Function SetFieldValidation(strTblName As String, _
strFldName As String, strValidRule As String, _
strValidText As String) As Integer

Dim dbs As Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTblName)
Set fld = tdf.Fields(strFldName)
fld.ValidationRule = strValidRule
fld.ValidationText = strValidText
End Function

 

[SkipVought]

Hi,

Please post MS Access VBA questions in forum705

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]