Dlookup username

Dear Experts.

In Access 2010 I'm struggling with a Dlookup ...

I have a Function that gets the user's network Login (GetOSUserName()) & this happily displays the login name all correctly in a text box ("UserNameTextBox").  What I am trying to do is create another text box whose data is derived from a dlookup of that username in a table called "TBL-Users".  This table contains various fields, the one i want to have returned is "FirstName"

I have got this far:

Control Source = DLookUp("FirstName","TBL-Users","[Login] = '" & [UserNameTextBox] & "")

But I just get a #Error returned

I have also tried to bypass using the [UserNameTextBox] and go straight for a lookup on the Function using

Control Source = DLookUp("FirstName","TBL-Users","[Login] = '" & GetOSUserName() & "")

But I just get a #Error returned

Can anybody help
correlateAsked:
Who is Participating?
 
mbizupCommented:
Missing an end quote, too:

= DLookUp("FirstName","[TBL-Users]","[Login] = '" & [UserNameTextBox] & "'")
0
 
mbizupCommented:
Try bracketing your table name:

= DLookUp("FirstName","[TBL-Users]","[Login] = '" & [UserNameTextBox] & "")


(and avoid spaces and special characters in your naming conventions)

0
 
correlateAuthor Commented:
Fantastic - thank you very much
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
check this out, you where missing the closing quote.
also, you need to make your code fail-safe against sql injection:

Control Source = DLookUp("FirstName","TBL-Users","[Login] = '" & replace([UserNameTextBox], "'", "''") & "' ")
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<This table contains various fields, the one i want to have returned is "FirstName">>

  Just a word of caution; if your trying to fetch more then one or two fields from that user record, you'd be better off to open a recordset in code and only fetch the record once and have all the fields available then you will be to make repeated DLookup() calls.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.