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

[Ryan Wehler]

Is your function returning a string or a int/dbl/long ? If it’s a string it’s going to require that concatenation where as I think form references get resolved by the JET engine first.  I’m having a sleepless night so forgive me if my brain fog has me off course. 



> On May 19, 2021, at 9:47 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