Setting Validation rules to multiple fields using VBA code returns error 3309 (too large)

Question

I would like to ask you for help with (edited) VBA code I found while searching for solution. My goal is to add validation rule to multiple field of table at once by VBA code. I have to use VBA code because there is so many fields and manual input would be insane.
Validation rule: NOT LIKE "*"+Chr(10)+"*" OR "*"+Chr(13)+"*" OR "*"+Chr(9)+"*"
(user can't save "enter", "ctrl+enter" and "tabulator")
For using this rule in VBA function the syntax is quite different but input to MS Access is correct.

My VBA code (not directly my, I found it and edit to my needs)

Public Function SetFieldValidation()

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 = "NOT LIKE " & Chr(34) & "*" & Chr(34) & "+Chr(10)+" & Chr(34) & "*" & Chr(34) & " OR " & Chr(34) & "*" & Chr(34) & "+Chr(13)+" & Chr(34) & "*" & Chr(34) & " OR " & Chr(34) & "*" & Chr(34) & "+Chr(9)+" & Chr(34) & "*" & Chr(34)
strValidText = "Some error msg"

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Table_Name")

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

In the past this script works with rule NOT LIKE "*"+Chr(10)+"*" OR "*"+Chr(13)+"*".
Than tried to add OR "*"+Chr(9)+"*".
Since this modification the MS Access (VBA) returns error: Property value is too large. (Error 3309) while I run the script. When I tried return to previous version of script without +Chr(9)+, the error still remains (even I succesfully used this script in the past and validation rules are set in another tables).

I'm really beginer in VBA, honestly I only need to setup this rule to all field of all tables. Can someone experienced tell me what's wrong in the code?

Thank you very much.

Answer 1

I suggest not using Chr(). Since Chr will always return the same value for a particular number, e.g. Chr(65) always returns A, I don't think you need to use the function to get the value you want. So try setting your strValidRule as one long static string with the characters returned by the Chr() functions hard coded.

Answer 2

I did a research and probably the problem is somewhere else - looks like error in table design of specific table.
1.) First test: At first I tried to debug strValidRule string and compare two approaches for string (my and with quotes as @Rominus advised). The results are same but quoString is smoother approach. Than I run script (tried both approaches). Error msg said 'Property Value Too Large' error 3309 and the old valid rules remains in table design unchanged (rules were set in the past and remains).

Sub teststring()
    chrString = "chrString: NOT LIKE " & Chr(34) & "*" & Chr(34) & " +Chr(10)+ " & Chr(34) & "*" & Chr(34) & " OR " & Chr(34) & "*" & Chr(34) & " +Chr(13)+ " & Chr(34) & "*" & Chr(34)
    Debug.Print chrString
    quoString = "quoString: NOT LIKE ""*"" +Chr(10)+ ""*"" OR ""*"" +Chr(13)+ ""*"""
    Debug.Print quoString
End Sub

2.) Second test: From my script above I firstly 'commented' values of strValidRule and strValidText by ' and add empty values "". Script run without error msg and added empty values.
Than I 'uncommented' the values and run script with values mentioned above (quoString approach and some msg for strValidText). Scritp run with error msg 'Property Value Too Large' error 3309. BUT, when I checked table design, the valid rules were properly set and working even the error msg popped up.

3.) Third test: I tried to use script in another table and run it without previously adding empty values described in step 2. (because in table were set old valid rules too). Script successfully overwrote old valid rules by new ones without msg of error 3309.

So, I found the way how to set valid rules but there is still some bug that shows error msg in specific table. Don't know how to fix it but I hope it haven't affect of my main goal (setting new working valid rules).