Microsoft KB Archive/105127

= ACC: Parameter Limit for Functions in ValidationRule Property =

Article ID: 105127

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q105127



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



SYMPTOMS
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
  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  Open a new form. Add a text box to the form.  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.   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. </li>  Enter the following expression, which concatenates the last two parameters to one parameter, for the ValidationRule property. Press ENTER: <pre class="fixed_text">  -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) </li></ol>

<div class="references_section">