Results 1 to 8 of 8
  1. #1
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117

    Help with Looping Records

    Hello, my problem is that on my DB I have a user login screen. This screen has a checkbox which allows the user to remember their credentials on their current computer. The problem I have is that the only way I knew to do this was to use the following code:

    This section is in the On Open event for the login screen-
    Code:
    Private Sub Form_Open(Cancel As Integer)Dim User1 As String
    Dim User2 As String
    Dim User3 As String
    Dim User4 As String
        On Error GoTo Err_Bellingham
    
    User1 = Nz(DLookup("empRemLocat", "tblLogin", "[empID] = 1300"), "")
    User2 = Nz(DLookup("empRemLocat", "tblLogin", "[empID] = 1296"), "")
    User3 = Nz(DLookup("empRemLocat", "tblLogin", "[empID] = 1309"), "")
    User4 = Nz(DLookup("empRemLocat", "tblLogin", "[empID] = 1198"), "")
    
    
    '1************************
        If User1 = Environ("ComputerName") Then
            If "1" = DLookup("empRemember", "tblLogin", "[EmpID] = 1300") Then
                    Me.cboEmployee.Value = "1300"
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                            Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
            End If
    '2************************
            ElseIf Environ("ComputerName") = User2 Then
                If "1" = DLookup("empRemember", "tblLogin", "[EmpID] = 1296") Then
                    Me.cboEmployee.Value = "1296"
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                             Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
                End If
    '3************************
            ElseIf Environ("ComputerName") = User3 Then
                If "1" = DLookup("empRemember", "tblLogin", "[EmpID] = 1309") Then
                    Me.cboEmployee.Value = "1309"
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                            Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
                End If
    '4************************
            ElseIf Environ("ComputerName") = User4 Then
                If "1" = DLookup("empRemember", "tblLogin", "[EmpID] = 1198") Then
                    Me.cboEmployee.Value = "1198"
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                            Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
                End If
                        
        End If
    '5************************
        Me.cboEmployee.SetFocus
        intLogonAttempts = 0
        
        Call UserClockIn
        
        DoCmd.OpenForm "frmLogoutTimer", , , , , acHidden
        
        
    '6************************
    Exit_Bellingham:
        Exit Sub
    Err_Bellingham:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Call LogError(Err.Number, Err.Description, "Bellingham()")
        Resume Exit_Bellingham
    End Sub
    This section is in the on click event for the login button-



    Code:
    If Me.chkRememberMe.Value = False Then        Set rst = CurrentDb.OpenRecordset("tblLogin", dbOpenDynaset)
                rst.FindFirst "EmpID =" & Me.cboEmployee.Value
                    rst.Edit
                        rst![empRemember] = "-1"
                        rst![empRemLocat] = "Not Remembered"
                    rst.Update
                rst.Close
            Else
                Set rst = CurrentDb.OpenRecordset("tblLogin", dbOpenDynaset)
                    rst.FindFirst "EmpID =" & Me.cboEmployee.Value
                        rst.Edit
                            rst![empRemember] = "1"
                            rst![empRemLocat] = Environ("ComputerName")
                        rst.Update
                    rst.Close
        
        End If
    As you can see this will work...but only because I put the user information in the code, and this was only possible because when originally developed there was only going to be 4 people with access to this DB....now there is going to be about 50-75 and I do not know a better way to do this. My initial guess is that i need to use some type of recordset loop, but I have never done anything with looping and only the bare minimum with record sets. Any tips or hints in the right direction will be immensely appreciated.

  2. #2
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    I'm not sure why you are putting an Emplyee ID in there. Allthough it seems the same i would work with usernames.
    The username i would store in a hidden table with the (masked) password.
    Your user enters the correct username and password and you get access.

    Am i missing somthing ?

  3. #3
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Quote Originally Posted by JeroenMioch View Post
    I'm not sure why you are putting an Emplyee ID in there. Allthough it seems the same i would work with usernames.
    The username i would store in a hidden table with the (masked) password.
    Your user enters the correct username and password and you get access.

    Am i missing somthing ?
    Right sorry for the confusion- to log in, the user selects their username from a dropdown list populated from the hidden login information table.
    The password is then entered and upon clicking the logon button this password is compared to the masked password in the aforementioned login information table. However, I have a checkbox which allows the user to remember their login credentials on their specific workstation. When this box is checked the previously listed code comes into play so that when the user goes to login the username/password boxes are automatically populated and the user can simply enter the database. What I don't know how to make work is to do this "remember me" feature where it will automatically recognize a user on their specific workstation so they do not need to enter username/password every time. This is a feature requested by my superior, so while it may seem superfluous it is also a requirement. Hopefully that is a little bit clearer than mud.

    Thanks

  4. #4
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Im guessing every user has its own workstation and thus its own computername.
    I would store the computername, username, password and a yes/no field in the hidden table.
    Upon opening the application the form checks the computername and dlookup if that specific user has a yes or no at the remember me field.
    If so, the ligin details are auto filled in, if not then the fieldd ate left blank.

    I made a similair login system. If you problem is not solved by my or other replys i will have a look for the code in my application. Cant do that now as im replying on my phone

  5. #5
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Quote Originally Posted by JeroenMioch View Post
    Im guessing every user has its own workstation and thus its own computername.
    I would store the computername, username, password and a yes/no field in the hidden table.
    I have this set up currently.

    Upon opening the application the form checks the computername and dlookup if that specific user has a yes or no at the remember me field.
    If so, the ligin details are auto filled in, if not then the fieldd ate left blank.
    WOW, that is simple...and I have been staring at my computer too long. Okay I will need to implement that but I was trying to make this way to hard. I will report back when successful, thanks for getting me on the right track!

  6. #6
    jtm013 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2014
    Posts
    117
    Well thanks goes to JeroenMioch! I was trying to make this process much harder than it needed to be.
    Final code to check and implement a remembered user is as follows:
    Code:
    If 1 = DLookup("empRemember", "tblLogin", "[empRemLocat] ='" & Environ("ComputerName") & "'") Then                    Me.cboEmployee.Value = DLookup("empID", "TblLogin", "[empRemLocat] ='" & Environ("ComputerName") & "'")
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                            Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
            End If

  7. #7
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Sometimes i spent a day unsuccesfully staring at a problem wich i solve in five minutes the next day. In other words, sometimes you need a fresh look at things

    By the way, when this works you can expand your login system with many features like a password management form for the admin. Here he can set up new users, and give them access indefinately or certain period of time. Give people access levels and more...

    The best feature my login system has is the the admin can allow design mode on the application or not by using a radiobutton. When design mode is disabled the application cant be opened in design mode so shift or F11 wont work.

    Let me know if you need help

  8. #8
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Quote Originally Posted by jtm013 View Post
    Well thanks goes to JeroenMioch! I was trying to make this process much harder than it needed to be.
    Final code to check and implement a remembered user is as follows:
    Code:
    If 1 = DLookup("empRemember", "tblLogin", "[empRemLocat] ='" & Environ("ComputerName") & "'") Then                    Me.cboEmployee.Value = DLookup("empID", "TblLogin", "[empRemLocat] ='" & Environ("ComputerName") & "'")
                        Me.chkRememberMe = True
                        Me.txtPassword.Value = DLookup("empPwd", "tblLogin", "[EmpID] =" & Me.cboEmployee.Value)
                    Else
                        Me.cboEmployee.SetFocus
                            Me.chkRememberMe = False
                            Me.chkRememberMe.DefaultValue = False
                                intLogonAttempts = 0
                                Exit Sub
            End If
    Your very welcome mate !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-09-2014, 10:42 AM
  2. do Loops - Not looping thru all records
    By kfschaefer1 in forum Programming
    Replies: 1
    Last Post: 03-27-2014, 11:42 PM
  3. Looping
    By ddrew in forum Forms
    Replies: 8
    Last Post: 10-08-2012, 01:48 AM
  4. Looping through records in a continous form
    By accessnihon in forum Forms
    Replies: 3
    Last Post: 01-04-2012, 01:04 AM
  5. Looping through Records in SQL
    By make me rain in forum Queries
    Replies: 13
    Last Post: 07-17-2011, 08:58 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