Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47

    Runtime Error 2001 caused by DLOOKUP line in "Login" form

    Runtime Error 2001 shows up because of the BOLD text line. I have no idea what is wrong.


    [Option Compare Database
    Private intLogonAttempts As Integer


    Private Sub Form_Open(Cancel As Integer)

    'On open set focus to text box
    Me.txtUsername.SetFocus
    End Sub



    Private Sub txtUsername_AfterUpdate()
    'After selecting user name set focus to password field
    Me.txtPassword.SetFocus
    End Sub

    Private Sub cmdlogin_Click()

    'Check to see if data is entered into the UserName textbox

    If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.txtUsername.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password textbox

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
    End If

    'This section checks the value of password in tblUsers to see if this matches value chosen in textbox on the form


    If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserID]=" & Me.txtPassword.Value) Then

    MyUserID = Me.txtUsername.Value


    'Close logon form and open splash screen

    'DoCmd.Close acForm, "frmLogon", acSaveNo

    DoCmd.OpenForm "frmSplash_Screen"

    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 your system administrator.", vbCritical, "Restricted Access!"
    Application.Quit
    End If

    End Sub]

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are maybe 10,000 error numbers - all or part of the message would be nice. I'm going to assume it's a data related message because your password field is empty, thus it's a Null issue. If not, then post something about the message as I'm feeling lazy today.

    It looks like you attempted to post your code in code tags because it begins and ends with brackets, which is nice that you attempted, but it didn't work. Can you fix it please?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Sorry, the message was "You canceled the previous operation". In regards to the Code Tags, what do I need to do. I didn't understand.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It could be the lack of proper delimiting of the password lookup (assuming it's text data type), but I've never known it to generate that error. I prefer to use the single quote method rather than nesting doubles, as in
    DLookup("Password", "tblUsers", "[UserID]= '" & Me.txtPassword & "'")

    Here's other observations for consideration
    - if I get it right on the 4th try, I will get kicked out. Attempt should be =, not >
    - you are asking for trouble by not having Option Explicit at the top of every module. Should be there by default. See Options>Require Variable Declaration in vb editor.

    As for code tags, you either click # on toolbar and insert your code. Ensuring it is nicely indented for easy reading is a great bonus. If the code is already there, you'd highlight it then click #. If you edit your post (would be nice and good practice) make sure you remove the leading and ending brackets you have.

  5. #5
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Thanks Micron for the guidance. I made the change to the DLookup line. Error still there.


    Code:
    Private Sub cmdlogin_Click()
    
    'Check to see if data is entered into the UserName textbox
    
    
        If IsNull(Me.txtUsername) Or Me.txtUsername = "" Then
                MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
                Me.txtUsername.SetFocus
            Exit Sub
        End If
    
    
    'Check to see if data is entered into the password textbox
    
    
        If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
                MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
                Me.txtPassword.SetFocus
            Exit Sub
        End If
    
    
    'This section checks the value of password in tblEmployees to see if this matches value chosen in textbox on the form
    
    
    
    
            If Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserID]= '" & Me.txtPassword & "'") Then
    
    
        MyUserID = Me.txtUsername.Value
    
    
        
    'Close logon form and open splash screen
            
            'DoCmd.Close acForm, "frmLogon", acSaveNo
    
    
            DoCmd.OpenForm "frmSplash_Screen"
    
    
            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 your system administrator.", vbCritical, "Restricted Access!"
            Application.Quit
        End If
        
    End Sub
    
    
    
    
    Private Sub btnExit_Click()
    On Error GoTo Err_btnExit_Click
    
    
    
    
    Exit_btnExit_Click:
        Me.Visible = False
        
        Exit Sub
    
    
    Err_btnExit_Click:
        MsgBox Err.Description
        Resume Exit_btnExit_Click
        
    End Sub

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    PMFBI

    The code also looks OK to me (assuming Password is a text field) though I agree with Micron's other comments.
    You could try adding error handling to bypass error 2001
    Code:
    Err_btnExit_Click:
       If err=2001 Then Resume Next
        MsgBox "Error " & err.Number & " "  & Err.Description
        Resume Exit_btnExit_Click
        
    End Sub
    ... but it would be better to find the cause of the error which in this case can be obscure
    Check for silly things like the spelling of field & control names.

    You can omit the .Value and the []. Also try using Nz just in case:
    Code:
    If Me.txtPassword = Nz(DLookup("Password", "tblUsers", "UserID= '" & Me.txtPassword & "'"),"") Then
    However, once you get this working there is a huge security issue - storing passwords without encryption is asking for trouble
    If there is a security breach, you will be liable.

    Attached is a similar login system using 128-bit encryption which I recommend you look at.
    For more details see this page on my website: http://www.mendipdatasystems.co.uk/p...gin/4594469149
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with the Nz because if the Lookup returns null it will cause this error. However, as written I'm seeing a result of True regardless of whether the lookup returns a value or not;
    e.g. it will either be
    If Me.txtPassword = "" or If Me.txtPassword = "MyPassword" which is incomplete
    I think the usage would be
    If Nz(DLookup("Password", "tblUsers", "UserID= '" & Me.txtPassword & "'"),"") = Me.txtPassword Then
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ummm.... actually I'm fairly sure both of those are wrong!

    No point checking the password field for UserID!!!!
    Is there a UserID control on the form.... if not use txtUserName

    In other words, one of these might be better!
    If Me.txtPassword = Nz(DLookup("Password", "tblUsers", "UserID= '" & Me.txtUserID & "'"),"") Then

    If Me.txtPassword = Nz(DLookup("Password", "tblUsers", "UserName= '" & Me.txtUserName & "'"),"") Then

    or quite possibly one of these written in reverse as in Micron's answer
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    regardless of the correct syntax for text/numbers I'm confused by
    Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserID]=" & Me.txtPassword.Value)
    why would a user have the same ID as their password? Shouldn't this be

    Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserID]=" & Me.txtUsername.Value)

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    why would a user have the same ID as their password? Shouldn't this be
    Good point. I don't always agree with what others do and just ignored that the user id seemed to be the password. This may very well be the reason that the lookup is returning Null, which as I noted, is supposed to cause this error. Moreover, initially I was going to preach about passwords being passe' but I lost that argument with Isladogs long ago!

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    Good point. I don't always agree with what others do and just ignored that the user id seemed to be the password. This may very well be the reason that the lookup is returning Null, which as I noted, is supposed to cause this error. Moreover, initially I was going to preach about passwords being passe' but I lost that argument with Isladogs long ago!
    I don't even remember that discussion...but I'm happy to take your word for it 😉.
    I didn't even notice the UserID/Password error until after my first reply - hence my post #8
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Quote Originally Posted by Ajax View Post
    regardless of the correct syntax for text/numbers I'm confused by

    why would a user have the same ID as their password? Shouldn't this be

    Me.txtPassword.Value = DLookup("Password", "tblUsers", "[UserID]=" & Me.txtUsername.Value)
    Tried that. Still have the error.

  13. #13
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    isladogs,

    Tried both. Still get error.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Going back to my first reply in post #6, check all field and control names. If any were wrong, retry those suggestions.
    Then try error handling.
    Also do consider encrypting your passwords.
    Look at the example file I suggested in that post.
    You are welcome to use it if you like it.

    If after all that, you still can't solve your error, suggest you post a cut down version of your dB
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Summit_IT is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jan 2019
    Location
    Virginia
    Posts
    47
    Thankyou, I will try that and let you know.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  2. Replies: 3
    Last Post: 11-21-2015, 07:02 PM
  3. Replies: 3
    Last Post: 05-15-2015, 04:19 PM
  4. Replies: 0
    Last Post: 03-11-2012, 09:19 AM
  5. Replies: 2
    Last Post: 12-02-2010, 02:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums