ACC: Parameter Limit for Functions in ValidationRule Property

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you are creating a ValidationRule property for a table or control, you may receive the error message "The expression you entered is too complex" in Microsoft Access for Windows 95 version 7.0 or "Expression Too Complex" in Microsoft Access versions 1.x and 2.0.

CAUSE

You can pass a maximum of 28 parameters to a function when you call the function from the ValidationRule property of a control. Attempts to pass more than 28 parameters will result in the error message stated above. (This also applies to table validation rules which can reference user- defined functions only in Access version 1.x.)

RESOLUTION

You can pass more than 28 parameters by concatenating parameters. Each parameter will be separated by a character (for example, a semicolon). After passing the individual parameters as one long parameter, you can parse the individual parameters by searching for the separator character (the semicolon), using the InStr() and Mid() functions.

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)
   
   

REFERENCES

For more information about Validation rules, search for "Validation Rule property," and then "ValidationRule, ValidationText Properties" using the Microsoft Access for Windows 95 Help Index.

Keywords          : kbusage FmsProp TblFldp GnlValid
Version           : 1.0 1.1 2.0 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided