ACC: Parameter Limit for Functions in ValidationRule Property (105127)

MORE INFORMATION

This section of the article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications With Microsoft Access For Windows 95" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access for Windows 95, version 7.0) is called Access Basic in earlier versions. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in version 2.0.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

Steps to Reproduce Behavior

1. Open a new module and enter the following:

         '*************************************************************
         'Declarations section of the module.
         '*************************************************************
   
         Option Explicit
   
         '===============================================================
         ' The following function CanPass28 will be accepted when entered
         ' into a validation rule
         '===============================================================
   
         Function CanPass28 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, _
               p, q, r, s, t, u, v, w, x, y, z, a27, b28)
            Dim Parm28, Parm29                          'These 3 lines are
            Parm28 = Mid(b28, 1, InStr(b28, ";") - 1)   'explained in
            Parm29 = Mid(b28, InStr(b28, ";") + 1)      'step 5.
         End Function
   
         '===============================================================
         'The following function CannotPass29 will give an error message
         ' when used in a validation rule
         '===============================================================
   
         Function CannotPass29 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, _
               o, p, q, r, s, t, u, v, w, x, y, z, a27, b28, c29)
         End Function
   					

2. Open a new form. Add a text box to the form.
3. Enter the following expression for the ValidationRule property of the text box, and then press ENTER:

      -1 = CannotPass29 (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16, _
           17,18,19,20,21,22,23,24,25,26,27,28,29)
   						

   Notice that you receive the error message "The expression you entered is too complex" in version 7.0 or "Expression Too Complex" in versions 1.x and 2.0.
4. Change the expression for the ValidationRule property to the following, and then press ENTER:

      -1 = CanPass28 (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16, _
           17,18,19,20,21,22,23,24,25,26,27,28)
   						

   Note that you do not receive an error message.
5. Enter the following expression, which concatenates the last two parameters to one parameter, for the ValidationRule property. Press ENTER:

      -1 = CanPass28(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16, _
           17,18,19,20,21,22,23,24,25,26,27,28&";"&29)
   						

   The last two parameters are separated inside the function by searching for the semicolon with the following lines from the function created in step 1:

        Parm28 = Mid(b28, 1, InStr(b28, ";") - 1)
        Parm29 = Mid(b28, InStr(b28, ";") + 1)