FormParametersForRecordsets

SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.

(c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.

The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.

Sample db named FormParametersForRecordsets demonstrates the syntax for alternative styles of handling the embedded form parameters. These examples are based upon Allen Browne's ELookup() function which is meant to provide greater flexibility and other advantages over built-in DLookup() function.

(a) Using function ELookup_A() - This is a modified version of ELookup(), with built in capability to fix embedded form parameters (if any) in criteria clause. The process involves wrapping each embedded form reference in EVal() function after enclosing it in outer single quotes. So far as the user is concerned, the syntax of passing embedded form references is just similar to that used for DLookup().

(b) Using function ELookup() in its original form. Here, it is necessary for the user to wrap each embedded form reference in EVal() function after enclosing it in outer single quotes - before supplying the arguments.

(c) Using custom function Fn_CV(), which accepts the form name and upto three control names as its arguments. Arguments for 2nd and 3rd control names are optional - needed only if the control is located on a subform or sub-subform. In such a case, control names other than the final one, represent subform controls.

(d) Using in-built DLookup() function. This is meant to provide ready appreciation at a glance, as to how the syntax for passing arguments in three alternative methods in (a) to (c) above, compares with that for DLookup().

Note - Function ELookup_A() is self contained, incorporating the code for fixing embedded form parameters. Parallel function ELookup_B() has also been given in the general module. It is similar to ELookup_A(), with the only difference that auxiliary function named Fn_FixFormParamsForRecordsets() is used for fixing the form parameters. The syntax for passing arguments to ELookup_B() is same as that for ELookup_A(). The modified function also takes care of spaces if any, in names of forms and controls.

For the selected modes of forms (main, sub, sub-sub) and functions, the suggested syntax is displayed in an unbound text box named TxtCode at bottom right. The purpose is to look up the ID matching the criteria determined by combined effect of various text boxes on the form as follows:

(d) 4th and 5th text boxes (Txt_TextFieldName1 and Txt_TextFieldName2) have names of text type fields meant for IN operation in criteria clause.

(f) 7th and 8th text boxes (Txt_DateFieldName and Txt_NumberFieldName) carry the names of date and number fields featuring in ORDER BY clause. The date field features in criteria clause also.

Note - The set of eight text boxes each on the subform and sub-subform are named identical to those on the main form.

Clicking the command button captioned "LookUp" executes the displayed code and shows the result (matching ID) in adjacent text box (TxtResult). Simultaneously, the matching record in subform at top right gets highlighted in light blue.

The above arrangement has inter-active feature. The user can play with the code displayed in text box (TxtCode), tinker with the suggested syntax as if it is live code and see the results by clicking on command button. If at any stage, it is desired to revert back to the originally suggested syntax, just select a different option from any of the two option groups (Form / Function) and then re-select the original option. The correct syntax will re-appear in TxtCode.

Important - Form references holding field names are not to be embedded. These are to be concatenated, without using any delimiters. This is equally applicable to DLookup() as well.