Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    79

    Unhappy Unanswered: User Login/Password using Dlookup not working

    If anyone can help me with this, it will be greatly appreciated. I have a form with 2 boxes on it, one is; txtPassword and the other is cboUsername. This is the code that I have, but for some reason, I continue to get a run-time error 2471 "The expression you entered as a query parameter produced this error: 'johnson'

    Private Sub cmdLogin_Click()
    'Check to see if data is entered into the UserName combo box
    If IsNull(Me.cboUsername) Or Me.cboUsername = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.cboUsername.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
    End If

    'Check value of password in tblLogin to see if this
    'matches value chosen in combo box
    If Me.txtPassword.Value = DLookup("Password", "tblUserLogin", _
    "[Username]=" & Me.cboUsername.Value) Then

    'Close logon form and open switchboard
    DoCmd.Close acForm, "frmLogon", acSaveNo
    DoCmd.OpenForm "switchboard"
    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database. Please contact admin.", _
    vbCritical, "Restricted Access!"
    Application.Quit
    End If

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have to put string delimiters around your criteria.

    You need the final criteria to end up looking like this:

    [Username] = "SomeRandomUser"

    What you've got now is:

    [Username] = SomeRandomUser

    Without the quotes, Access doesn't know how to treat that username value.

    This should take care of the current error by wrapping whatever was in cboUsername.Value in double quotes before asking Access to evaluate the expression:
    DLookup("Password", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2005
    Posts
    79

    Smile Thank You

    Teddy you are wonderful. Thank you very much. I have been staring at this for a long time trying to figure it out. Thanks again.

  4. #4
    Join Date
    Nov 2005
    Posts
    79

    Another Question

    Can you tell me what would I need to type to add to the above statement to include the field in the table called AccessLevel to equal Admin. I have tried many options to no avail.

    If Me.txtPassword.Value = DLookup("Password", "tblUserLogin", _
    "[Username]=""" & Me.cboUsername.Value & """" & "[AccessLevel]=""Admin""") Then

  5. #5
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by miracleblake View Post
    Can you tell me what would I need to type to add to the above statement to include the field in the table called AccessLevel to equal Admin. I have tried many options to no avail.

    If Me.txtPassword.Value = DLookup("Password", "tblUserLogin", _
    "[Username]=""" & Me.cboUsername.Value & """" & "[AccessLevel]=""Admin""") Then
    Try:

    Code:
    
    
    If Me.txtPassword.Value = DLookup("Password", "tblUserLogin", _
    "[Username]=""" & Me.cboUsername.Value & """ & [AccessLevel]=""Admin""") Then
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    Nov 2005
    Posts
    79

    Kind of worked

    The code ran, but not correctly. The username and the password are correct; the accesslevel is equal to admin, however, the code that is listed after the if statement is bypassed and jumps to the end if statement.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Again, think of what you would need to state in order to make this work in a WHERE clause if you were working with a query:

    WHERE [Username] = "foo" AND [AccessLevel] = "bar"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2005
    Posts
    79

    Problem with Quotes

    I think my biggest problem is quotes. I don't know where to put them.

    Here is what I would like for it to say. If the username and password from the Login form are equal to what is in tblLogin And in the tblLogin table, accessLevel is equal to admin then do blah, blah, blah.

    Thanks for all of your help.

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by miracleblake View Post
    I think my biggest problem is quotes. I don't know where to put them.
    Quotes are used to define literal strings.

    Quote Originally Posted by miracleblake View Post
    Here is what I would like for it to say. If the username and password from the Login form are equal to what is in tblLogin And in the tblLogin table, accessLevel is equal to admin then do blah, blah, blah.
    You are not writing your code logic to match what you just said.

    Match the Password AND match the Security level.

    Try:

    Code:
    
    If ( Me.txtPassword.Value = DLookup("Password", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") Then
    
       If DLookup("[AccessLevel]", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") = "Admin" Then 
    
         ' blah blah blah
       Else
         ' not admin
    
       End If
    
    End If

    or

    Code:
    
    If ( Me.txtPassword.Value = DLookup("Password", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") And    If DLookup("[AccessLevel]", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") = "Admin" Then 
    
        ' blah blah blah
    
    
    End If
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Nov 2005
    Posts
    79
    Thank you, thank you, thank you. The first one worked. Can you give me any hints on how to figure out where single quotes/double quotes go, parenthesis, and AND's? Thanks again for all of your help; it is greatly appreciated.

  11. #11
    Join Date
    Nov 2005
    Posts
    79

    Just Plain Frustrated

    Quote Originally Posted by HiTechCoach View Post
    Quotes are used to define literal strings.



    You are not writing your code logic to match what you just said.

    Match the Password AND match the Security level.

    Try:

    Code:
    
    If ( Me.txtPassword.Value = DLookup("Password", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") Then
    
       If DLookup("[AccessLevel]", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") = "Admin" Then 
    
         ' blah blah blah
       Else
         ' not admin
    
       End If
    
    End If

    or

    Code:
    
    If ( Me.txtPassword.Value = DLookup("Password", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") And    If DLookup("[AccessLevel]", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") = "Admin" Then 
    
        ' blah blah blah
    
    
    End If
    I got the first part to work. I tried on my own to do another. I am so frustrated because I have to keep asking. This one looks like the one above, but for some reason it will not work. I have been sitting here for hours trying to figure this out on my own to no avail. Branch should equal CIED and userlogin in table should match cboUsername.

    If DLookup("[Branch]", "tblUserLogin", "[Username]=""" & Me.cboUsername.Value & """") = "CIED" Then

  12. #12
    Join Date
    Nov 2005
    Posts
    79

    Whooohooo

    I got it, I got it. Thank you so much. Just kept looking and looking. My database said CIED Branch instead of CIED, hence the reason it would skip code. Details matter.

  13. #13
    Join Date
    May 2010
    Posts
    601

    Thumbs up

    Quote Originally Posted by miracleblake View Post
    I got it, I got it. Thank you so much. Just kept looking and looking. My database said CIED Branch instead of CIED, hence the reason it would skip code. Details matter.
    You're welcome!

    You are learning. Great job!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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