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

[Jim Dettman]

<< any time you edit code stuff ends up decompiled>>

 Not always.  Depends on the change.

<< 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.>>

  As soon as you enter a line, it is tokenized and stored as p-code.  Basic
syntax checking is performed at that point.   When you get a line
highlighted in red, it means VBA couldn't figure the syntax out and the
source line has yet to be stored as p-code.

  When you click for a compile, then additional steps are carried out on the
p-code.   Last I heard, VBA had sixteen different levels of compilation, all
of which must be run through to end up with code that is considered
"compiled".  But with an interpretive compiler, it's always the p-code that
is executed.  The difference between "compiled" and "uncompiled" is just if
all that checking is done at runtime or not.

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

 With Access and VBA, I'm not sure.   It used to be that the modules stood
on their own and each one could have different compile states.   But the
Access team changed that behavior (starting with A2000 I believe) and since
then, everything is treated as one.   I believe that was just a difference
in the way they stored things (multiple projects vs one), and from the VBA
viewpoint, it is always working with a project file.

 Wayne Phillips would know for sure.   He just came out with Twin Basic,
which at some point will be a viable replacement for VBA.   Right now, it's
only good for writing code outside of Access, but he plans to continue
enhancing it.   Wayne is the guy that wrote vbWatchDog and other products
and has delved deep into VBA. 

Jim.


-----Original Message-----
From: AccessD On Behalf Of John Colby
Sent: Thursday, May 20, 2021 9:16 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

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