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

[Gustav Brock]

Hi Børge

I'm not sure what your question is, but this could be written as:

? DLookup("NextAvailableUFN", "tblSystem", "UserName =  fGetApplicationParameter('CurrentLoggedInUser')")

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Borge Hansen
Sendt: 20. maj 2021 04:47
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] Dlookup - where parameter being a form control vs a function

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")));
--