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

[John Colby]

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