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

[Borge Hansen]

John,
As to what I am trying to determine ...
I just want to query the tblSystem table directly instead of going via
the Forms!frmSystem.
tblSystem holds the User Profile properties for each of the Users of
the application, so it's a small table.
Speed is not an issue here...
And as you saw the whole question came about because I confused myself
and forgot
it's a matter of just using single quotes instead of double quotes for
the parameter in the function call.

/borge



On Thu, May 20, 2021 at 10:43 PM John Colby <jwcolby at gmail.com> wrote:
>
> 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