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

[John Colby]

any time you edit code stuff ends up decompiled.  It is my understanding
(useless I know) that the next time you try to run any code the source will
be run through the compiler, turning everything back into p-code, which as
I understand it, is what is actually interpreted.

TBH I have no idea whether it recompiles everything or just the module that
changed.

On Thu, May 20, 2021 at 9:02 AM Jim Dettman via AccessD <
accessd at databaseadvisors.com> wrote:

>
>  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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
John W. Colby
Colby Consulting