Dlookup username

Posted on 2011-10-19
Last Modified: 2012-05-12
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
Question by:correlate
    LVL 61

    Expert Comment

    Try bracketing your table name:

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

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

    LVL 61

    Accepted Solution

    Missing an end quote, too:

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

    Author Closing Comment

    Fantastic - thank you very much
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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], "'", "''") & "' ")
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now