jwcolby
jwcolby at colbyconsulting.com
Wed Aug 22 19:31:21 CDT 2007
Y yo tambien. I might also say that you can pass values between forms and
between code modules as well. You do need to be careful though as these
turn into "global variables" and we all know what THAT means. ;-)
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, August 22, 2007 6:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query Criteria problem
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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com