Arthur Fuller
fuller.artful at gmail.com
Wed Aug 22 17:50:40 CDT 2007
I would like to add one thing to this discussion of static functions. What I
dislike about form references embedded in queries is that the form must be
open in order to run the query | report | form that depends on them. With
static functions, I can set the value(s) of concern from the immediate
window then run the query | report | form without issues. That's why I love
them.
Arthur
On 8/22/07, Doug Murphy <dw-murphy at cox.net> wrote:
>
> A.D.
>
>
> Thank you for the information. I don't have any reservations with using
> static functions in queries and do it when required. I use form derived
> values for query criteria since it is easy to do and up to now it worked.
> Since I couldn't seem to understand why this isn't working in this case I
> went to the static functions and things are progressing nicely. I would
> still like to understand why using form values in a queries criteria does
> not work in this project. Actually it is just in a couple of queries. I
> am
> sure there is a reason, I just can't figure it out.
>
> I will keep your message in my archive for future reference.
>
> Thanks again.
>
> Doug
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
> Sent: Tuesday, August 21, 2007 11:56 PM
> To: Access Developers discussion and problem solving
> Cc: A.D.TEJPAL
> Subject: Re: [AccessD] Query Criteria problem
>
> Doug,
>
> Expressing reservation in use of static functions, you have stated:
> "If I use the static function I still have to set it when the form is
> updated to hold the value that is already on the form."
>
> This limitation can be overcome by pulling (instead of
> pushing) form's
> data into the function, as demonstrated in sample functions named
> Fn_CustID() and Fn_EmpID() given below. This ensures that always the
> latest
> value in form's control is returned whenever the function is called.
>
> There are certain other strong reasons to prefer use of such
> functions,
> since direct reference to form's controls (in query SQL) suffers from the
> following drawbacks:
>
> (a) For a query with parameters clause, a recordset can not be created
> directly via Currentdb.OpenRecordset method. Leads to Error 3061 (Too few
> parameters. Expected 1).
>
> (b) Action query with parameters clause can not be run directly via
> Currentdb.Execute method. Leads to Error 3061 (Too few parameters.
> Expected
> 1).
>
> A self contained note on the subject is placed below, for ready
> reference.
>
> Best wishes,
> A.D.Tejpal
> ---------------
>
> Queries using form based values
> (Use of functions in lieu of parameters) ===========================
>
> 1 - Use of parameters in queries suffers from following limitations:
>
> 1.1 - For a query with parameters clause, a recordset can not be
> created directly via Currentdb.OpenRecordset method. Leads to Error 3061
> (Too few parameters. Expected 1).
>
> 1.2 - Action query with parameters clause can not be run directly
> via Currentdb.Execute method. Leads to Error 3061 (Too few parameters.
> Expected 1).
>
> 1.3 - Parameter clause can not be too long (there are reports of
> problems encountered on Windows Vista, if it is beyond 63 characters).
> An extract from a post (Jul-2007) by Allen Brown in one of the news
> groups,
> is placed below:
> "Paul Overway alerted me to this bug, which occurs only under
> Access
> 2000 - 2003 on Windows Vista. If a parameter name exceeds 63 characters,
> and your code refers to the parameter of the QueryDef by name, the code
> fails with Access Error 3000, reporting: Reserved Error -1038. There is no
> message for this error."
>
> 1.4 - Confusion in dealing with Null value, if data type has been
> explicitly declared as text - in parameters clause of the query. (This
> anamoly was brought up in one of Allen Browne's posts in July 2007).
>
> 2 - Suggested Universal Remedy:
>
> 2.1 - The limitations outlined above can be overcome by replacing
> form based parameters with user defined functions.
>
> 2.2 - As an illustration, functions Fn_CustID() returning text
> type,
> and Fn_EmpID() returning number type, are given below. These functions
> grab
> the values entered in relevant controls (TxtCustomerID and TxtEmployeeID
> respectively) on the target form (Form1).
>
> 2.3 - Use of functions in this manner greatly simplifies the SQL,
> at
> the same time getting rid of parameters clause. Sample select queries
> Q_CustSelect and Q_EmpSelect, based upon this approach, are given below.
> These have criteria clause based upon CustomerID and EmployeeID fields
> respectively. In both cases, if the target text box on the form is blank,
> all records get included.
>
> 2.4 - Sample append query Q_CustEmpAppend, using functions in lieu
> of form based parameters, as given below, can be run directly by using
> Currentdb.Execute method.
>
> A.D.Tejpal
> ---------------
>
> Q_CustSelect
> (Query Filtered as per CustomerID (text type field))
> ===================================
> SELECT Orders.*
> FROM Orders
> WHERE (Fn_CustID() = "") Or (Orders.CustomerID=Fn_CustID());
> ===================================
>
> Q_EmpSelect
> (Query Filtered as per EmployeeID (number type field))
> ===================================
> SELECT Orders.*
> FROM Orders
> WHERE (Fn_EmpID() = 0) Or (Orders.EmployeeID=Fn_EmpID());
> ===================================
>
> Q_CustEmpAppend
> ===================================
> INSERT INTO Orders ( CustomerID, EmployeeID ) VALUES (Fn_CustID(),
> Fn_EmpID()); ===================================
>
> Fn_CustID()
> ===================================
> Function Fn_CustID() As String
> On Error Resume Next
> Dim Rtv As String
>
> Rtv = Nz(Forms("Form1")("TxtCustomerID"), "")
> Fn_CustID = Rtv
> On Error GoTo 0
> End Function
> ===================================
>
> Fn_EmpID()
> ===================================
> Function Fn_EmpID() As Long
> On Error Resume Next
> Dim Rtv As Long
>
> Rtv = Nz(Forms("Form1")("TxtEmployeeID"), 0)
> Fn_EmpID = Rtv
> On Error GoTo 0
> End Function
> ===================================
>
> ----- Original Message -----
> From: Doug Murphy
> To: 'Access Developers discussion and problem solving'
> Sent: Wednesday, August 22, 2007 03:16
> Subject: Re: [AccessD] Query Criteria problem
>
>
> Hi Arthur,
>
> I use static functions where they make sense but in this case I should
> be,
> and have always been, able to just refer to the value of a control on an
> open form. If I use the static function I still have to set it when the
> form is updated to hold the value that is already on the form. I was
> looking for a reason why am am getting this error. I like to understand
> why things happen.
>
> If I can't figure this out I may go with your suggestion just to get
> this
> done, but I won't feel comfortable that the original problem is still
> lurking.
>
> Doug
>
> << SNIP >>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>