[AccessD] Dlookup - where parameter being a form control vs a function

[Jim Dettman]

 When it's SQL, it's not that it must be interpreted, but rather a
combination of Access helping you out, and that the statement is funneled
through the JET engines expression service.

Between the two, that's what takes care of resolving things like form
references and VBA expressions.

 Source is always compiled into p-code btw.  Source is never executed
directly.

Jim.

-----Original Message-----
From: AccessD On Behalf Of John Colby
Sent: Thursday, May 20, 2021 8:43 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Dlookup - where parameter being a form control vs a
function

Code in access has to go through the interpreter, which may or may not
involve compiling to p-code first.  In any event it is a very different
animal from getting the value of a control on a form.  Very old memories
are telling me that dlookup is way slower than equivalent vba.  Not to
mention that the jet engine has to process the sql.  How and when it will
cause the function to be handed off to the interpreter is unknown.

What are you trying to determine?

On Wed, May 19, 2021 at 10:46 PM Borge Hansen <pcs.accessd at gmail.com> wrote:

> Hello everyone,
> Anyone got an answer to this question?
>
> So I've been replacing
> [forms]![frmSystem]![txtUserName]
> with a function
> fGetApplicationParameter("CurrentLoggedInUser")
> that returns the same text string
> i.e. the UserName of the current logged in User in a User Profile
> table called tblSystem
>
> Below is the code for testing and comparing Dlookup with the similar
> SQL select statement.
> Notice that when we use the function on the DLookup it has to be
> resolved outside of the third parameter of the DLookup whereas the
> forms!frmSystem!txtUserName can be resolved as part of the third
> DLookup parameter.
>
> And using a plain SQL Select statement the Forms!frmSystem!txtUserName
> is interchangeable with the function.
>
> Why  the difference on the DLookup whereas the two SQL select
> statements are similar in form?
>
> /borge
>
> === code ===
> ?DLookup("NextAvailableUFN", "tblSystem", "UserName =
> forms!frmSystem!txtUserName")
>  900005
> or
> SELECT tblSystem.NextAvailableUFN, tblSystem.UserName
> FROM tblSystem
> WHERE (((tblSystem.UserName)=[forms]![frmSystem]![txtUserName]));
>
> => notice the difference here: UserName = ' " & etc & " '
> ?DLookup("NextAvailableUFN", "tblSystem", "UserName = '" &
> fGetApplicationParameter("CurrentLoggedInUser") & "'")
>  900005
> or
> => notice: similar to =[forms]![frmSystem]![txtUserName])); above
> SELECT tblSystem.NextAvailableUFN, tblSystem.UserName
> FROM tblSystem
> WHERE
> (((tblSystem.UserName)=fGetApplicationParameter("CurrentLoggedInUser")));
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
John W. Colby
Colby Consulting
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com