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,418
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    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
  •