Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - FormParametersForRecordsets
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

>
Want a good read? Try The Summer of His Life (available on Amazon!)

FormParametersForRecordsets

 Post Reply Post Reply
Author
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: FormParametersForRecordsets
    Posted: 20 Oct 2008 at 5:54am

FormParametersForRecordsets
Sample Db - Explanatory Notes

  
    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.
 
    Concatenation of hard values into SQL string suffers from the following drawbacks:
    (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
    (b) If text data has embedded quotes, these need to be fixed before concatenation.
    (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.
    (d) Care needed in handling Null values.
 
    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.
 
    Three different modes of form controls are covered:
    (a) Controls directly on main form (Main form is named F_C).
    (b) Controls on subform. (Subform control on main form, holding this subform is named SF_Sub).
    (c) Controls on sub-subform i.e. subform within a subform. (Subform control on first subform, holding this sub-subform is named SF_SubSub).
 
    For each form mode above, four styles of lookup execution are demonstrated:
    (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:
    (a) 1st text box (Txt_TargetFieldName) carries the name of field to be looked up
    (b) 2nd text box (Txt_TableName) has the name of data table or query.
    (c) 3rd text box (Txt_TextValue) has the text value to be compared.
    (d) 4th and 5th text boxes (Txt_TextFieldName1 and Txt_TextFieldName2) have names of text type fields meant for IN operation in criteria clause.
    (e) 6th text box (Txt_DateValue)  has the date value to be compared.
    (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.

    Reference required - DAO 3.6
 
    Version - Access 2000 file format

    Download >>  uploads/37/FormParametersForRecordsets.zip
 
 
--

Email: adtejpal@gmail.com
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.