How to change the validation rules for all the fields in a table with VBA?

[Alex Volovisky]

Hello

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

[ADezii]

The following Function will set the Validation Rule and Validation Text of every Field, except [Record ID], to those values that you specify, for the Table Name that you pass to the Function.

Function SetFieldValidation(strTblName As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strValidRule As String
Dim strValidText As String
Dim bytNumOfFields As Byte

strValidRule = ">=1 And <=5"
strValidText = "Please use a value between 1 and 5."

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTblName)

For bytNumOfFields = 0 To tdf.Fields.Count - 1
  With tdf.Fields(bytNumOfFields)
    If .Name <> "Record ID" Then
      .ValidationRule = strValidRule
      .ValidationText = strValidText
    End If
  End With
Next
End Function

Call SetFieldValidation("Table1")

[NeoPa]

Try this before the definition of your function :

Dim strValidRule As String, strValidText As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
strValidRule = ">=1 And <=5"
strValidText = "Please use a value between 1 and 5."
For Each tdf In db.TableDefs
    For Each fld In tdf.Fields
        If fld.Name <> "ID" Then
	    Call SetFieldValidation(strTblName:=tdf.Name, _
	                            strFldName:=fld.Name, _
	                            strValidRule:=strValidRule, _
	                            strValidText:=strValidText)
        End If
    Next fld
Next tdf

[ADezii]

I'm not sure, but I think you can get into trouble if you do not Filter the Table Names, eliminating the System Tables. I am referring to Code Lines 12 and 21.

Dim strValidRule As String, strValidText As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
  
Set db = CurrentDb

strValidRule = ">=1 And <=5"
strValidText = "Please use a value between 1 and 5."

For Each tdf In db.TableDefs
  If Mid$(tdf.Name, 2, 3) <> "Sys" Then
    For Each fld In tdf.Fields
      If fld.Name <> "ID" Then
        Call SetFieldValidation(strTblName:=tdf.Name, _
                                strFldName:=fld.Name, _
                                strValidRule:=strValidRule, _
                                strValidText:=strValidText)
      End If
    Next fld
  End If
Next tdf

[NeoPa]

You're absolutely right of course ADezii. My code was hopefully to point in the right direction, rather than definitive code for the task.

Another approach is to check the .Attributes of the table (Tabledef) to see if dbSystemObject is set (rather than the approach of checking the name stub - although I'm pretty sure that is also quite reliable).