Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010

    Unanswered: Altering validation rules for all fields in a table via VBA in access

    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

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    I think you'll get the best answers for this one in the Access forum, moving accordingly...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts