Altering validation rules for all fields in a table via 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

[Rawb]

Assuming you're using the same Validation Rule/Text for all the fields in each Table, try this function:

Public Function SetValidationRule(strTableName As String, strValidationRule As String, strValidationText As String) As Boolean
  Dim rst As DAO.Recordset

  Set rst = CurrentDb().OpenRecordset(strTableName, dbOpenTable)

  For i = 0 To rst.Fields.Count - 1
    If InStr(0, UCase(rst.Fields(i).Name), "ID") = 0 Then
      rst.Fields(i).ValidationRule = strValidationRule
      rst.Fields(i).ValidationText = strValidationText
    End If
  Next i

  rst.Close

  Set rst = Nothing
End Function

That should update the Validation Text and Rule for every Field in the Table as long as the Field Name doesn't have "ID" in it.

[alevol]

Thanks a lot Rawb!

It wasn't accepting the syntax of the If condition, so I just went for this (since the ID field is always the first one) and it worked like a charm:

Function SetFieldValidation(strTblName 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)
For i = 1 To tdf.Fields.Count - 1
Set fld = tdf.Fields(i)
fld.ValidationRule = strValidRule
fld.ValidationText = strValidText
Next i
End Function