Results 1 to 6 of 6

Thread: DLookup Problem

  1. #1
    Join Date
    Jun 2009
    Posts
    48

    Unanswered: DLookup Problem

    In the following sub DLookup returns Engineer Name as Empty and execution
    proceeds to Exit Sub even though the table has an entry where the field
    UserName = CurrentUser().

    The purpose of the Dlookup is to look in table Engineers-ECN and return the
    value of the [Engineers & Designers] field if the UserName field matches the
    CurrentUser name. Note: Except for UserName, I did not choose the table or field names used here.


    Private Sub Engineering_DblClick(Cancel As Integer)

    Dim EngineerName As Variant
    Dim strCurrentUser As String

    On Error GoTo ExitError
    If [Status] = "please Sign" Then
    strCurrentUser = CurrentUser()
    EngineerName = DLookup("[Engineers & Designers]", "Engineers-ECN", "UserName = " & strCurrentUser)
    If IsNull(EngineerName) Then Exit Sub
    End If
    If [Status] = "please Sign" Then [Engineering] = EngineerName
    If [Status] = "please Sign" Then [E-Date] = Date
    If [Materials] = "_" Or [Production] = "_" Or [Engineering] = "_" Or [QC] = "_" Then Else [Status] = "Approved"
    If [Checked By] = "_" Then Else [Status] = "Completed"
    If [Checked By] = "Cancel" Then [Status] = "** Cancel **"

    ExitError:
    Exit Sub

    End Sub

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hello cmelias,

    You'll want to add single quotes around strCurrentUser, i.e.:
    Code:
    EngineerName = DLookup("[Engineers & Designers]", "Engineers-ECN", "UserName = '" & strCurrentUser & "'")
    As long as UserName is a field in Engineers-ECN, you're set.

    Regards,

    Ax

  3. #3
    Join Date
    Jun 2009
    Posts
    48

    DLookup Problem

    Ax,

    Made the changes and it works! Thanks.

    I really am confused about all of the quotes needed in DLookup and in Visual Basic queries. I used the Visual Basic Help to come up with the DLookup syntax that didn't work. There have to be some rules/logic about how to use the quotes. Do you know where I might find these?

    Charles

  4. #4
    Join Date
    May 2009
    Posts
    258
    Great, glad to hear it!

    Numeric fields do not need quoted values. Text fields do need quoted values.

    Access Tips: Access and SQL Part 2: Putting VBA and SQL Together

    Regards,

    Ax

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Quote Originally Posted by Ax238 View Post
    Hello cmelias,

    You'll want to add single quotes around strCurrentUser, i.e.:
    Code:
    EngineerName = DLookup("[Engineers & Designers]", "Engineers-ECN", "UserName = '" & strCurrentUser & "'")
    As long as UserName is a field in Engineers-ECN, you're set.

    Regards,

    Ax
    Good answer BUT what if the username is O'brian

    Code:
    EngineerName = DLookup("[Engineers & Designers]", "Engineers-ECN", "UserName = """ & strCurrentUser & """")
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,764
    Provided Answers: 19
    Ah, that's silly! Everyone knows that people with apostrophes in their names are troublemakers! Who's going to hire them as engineers? That's like hiring people whose names start with Mc!

    Seriously, myle makes a good point.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •