Consulting

Results 1 to 8 of 8

Thread: Please Help! DLookup... What Am I Doing Wrong!!!

  1. #1

    Please Help! DLookup... What Am I Doing Wrong!!!

    This DLookup has me stumped!

    In a form [frm_MCOs], I have 2 fields, [ChangeAnalystApproval], [DocControlPassword]
    In a table [tbl_Employees], I have 2 fields [EmployeeName], [Passcode]

    I would like the database to compare the fields in [frm_MCOs] form to fields in [tbl_Employees] table like so:
    [frm_MCOs]![ChangeAnalystApproval] must equal [tbl_Employees]![EmployeeName]
    [frm_MCOs]![DocControlPassword] must equal [tbl_Employees]![Passcode]
    If Password is blank, display msgbox1
    If Passcode and DocControlPassword match, display the msgbox2
    If Passcode doesn’t match DocControlPassword, display msgbox3

    It recognizes the "blank" Password but the problem I'm having is that it doesn't seem to be comparing the Passcode and DocControlPassword.

    Here is my code:

    Private Sub DocControlCheckPassword_Click()
    ' msgbox1 If IsNull(Me.DocControlPassword.Value) Then MsgBox "You cannot enter a blank Password. Try again." Me.DocControlPassword.SetFocus End If ' msgbox2 If DLookup("Passcode", "tbl_Employees", "EmployeeName='" & Me.DocControlPassword & "'") Then MsgBox "You have approved this change." Else ' msgbox3 MsgBox "Password does not match. Please try again." Me.DocControlPassword.SetFocus End If
    End Sub
    "The quality of a man's life is in direct proportion to his commitment to excellence, regardless of his chosen field of endeavor.” ~ Sherman Alexie, The Absolutely True Diary of a Part-Time Indian

  2. #2
    the problem I'm having is that it doesn't seem to be comparing the Passcode and DocControlPassword.

    ... "EmployeeName='" & Me.DocControlPassword & "'" ....
    Looking at that line of code, the cause of problem is pretty obvious, isn't it?
    Learn VBA from the ground up with my VBA Online Courses.

  3. #3
    Quote Originally Posted by PhilS View Post
    Looking at that line of code, the cause of problem is pretty obvious, isn't it?
    Thanks for that tip PhilS
    But correcting the code to
    "Passcode=" & Me.DocControlPassword = "'"
    didn't help.
    It's still not comparing the 2 fields. If I type in the wrong or right password, it tells me that it's not correct.
    "The quality of a man's life is in direct proportion to his commitment to excellence, regardless of his chosen field of endeavor.” ~ Sherman Alexie, The Absolutely True Diary of a Part-Time Indian

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Shouldn't that be
    "Passcode= '" & Me.DocControlPassword = "'"

  5. #5
    OPB, it doesn't work that way either.

    Des this help clarify?
    ChangeAnalystApproval [EmployeeName] is a combo box from a query.
    Find the [EmployeeName] in the [tbl_Employees] and retrieve the [Passcode]
    Then the [frm_MCOs]![DocControlPassword] must equal [tbl_Employees]![Passcode]If Password is blank, display msgbox1
    If Passcode and DocControlPassword match, display the msgbox2
    If Passcode doesn’t match DocControlPassword, display msgbox3

  6. #6
    If you want to check if EmployeeName AND Passcode match, you need to check both, either in the criteria expression or by comparing the result of the lookup with the value in the form.

    The direct, boolean evaluation of the DLookup return value looks fishy to me. I suspect it will not work. So, I replaced the DLookup with a DCount.

    If DCount("*", "tbl_Employees", "EmployeeName='" & Me!ChangeAnalystApproval & "' AND Passcode='" & Me!DocControlPassword & "'") Then
    Learn VBA from the ground up with my VBA Online Courses.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have the Password (in Password format & set to Invsible) and the Names on the login form, so there is no looking up to do.

  8. #8
    Thank you, thank you, thank you PhilS!!!
    I was having such a difficult time wrapping my head around this but it works perfectly now!

Tags for this Thread

Posting Permissions

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