Login Date/Time Log

[data808]

I have a Login form that has 2 text boxes that are currently unbound. One for Username and the other for Password. I created a backend table called tbl_LoggedIn where I plan to keep records of when the users login and logout. In that table I created these fields: UserID, Login Date, Login Time, Logout Date, and Logout Time. I made the record source for the Login form to be with tbl_LoggedIn and bound the username text box to UserID that is in the tbl_LoggedIn table. I have code behind the login button that will do the check for the username and password in the tblUser table and if there is a match it will set the Login form to hidden and load a splash screen and take the user to the main menu. The reason why the Login form is set to hidden is because I have other unbound text boxes throughout the database to let the users know who is logged in at the moment. It pulls the info from the hidden Login form's username textbox.

I assume to get this Login date/time log going for the tbl_LoggedIn table, I would need to do some VBA for the login button. Correct me if I'm wrong, but if the user is granted access based on their UserID credentials, I can have VBA do a save for the Login form and that will cause the username to be saved to the tbl_LoggedIn table. Then I can do more VBA to auto-populate the Login Date and Login Time for that same table correct? I won't worry about the logout process yet as I would like to focus on this first logging-in part first.

Here is the current code for the login button on the Login form:

Private Sub cmdLogin_Click()

Dim validCredentials As Integer
Dim userLevel As Variant
Dim ID As Integer
validCredentials = DCount("Username", "[tblUser]", "[Username] ='" & txtUsername & "' AND [Password]='" & txtPassword & "'")
        
If IsNull(Me.txtUsername) Then
    MsgBox "Please Enter Username", vbInformation, "Username Required"
    Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.txtPassword.SetFocus
Else
    ID = DLookup("UserID", "tblUser", "Username = '" & Me.txtUsername.Value & "'")
    If validCredentials = 1 And Me.txtPassword = "password" Then
        DoCmd.Close
        MsgBox "Please Change Password", vbInformation, "New Password Required!"
        DoCmd.OpenForm "Change Password", , , "[UserID] = " & ID
    Else
        If validCredentials = 1 Then
        userLevel = Nz(DLookup("UserSecurity", "[tblUser]", "[Username]='" & txtUsername & "'"), 0)
        If userLevel = "Admin" Then
            DoCmd.Close
            DoCmd.OpenForm "Main Menu_admin"
        ElseIf userLevel = "Supervisor" Then
            DoCmd.OpenForm "Splash Screen Load_sup"
        Else
            DoCmd.OpenForm "Splash Screen Load"
        End If
    Else
        MsgBox "Invalid Username Or Password!", vbExclamation, "UNAUTHORIZED!"
        Me.txtPassword.SetFocus
    End If
End If
End If
Exit Sub


End Sub

Would adding the DoCmd.RunCommand acCmdSaveRecord right before the user gets the splash screen load save the username into the tbl_LoggedIn table? If so, how would I also get a date and time into that table if there are no fields for it in the Login form? Also, would you have any suggestions on if I should also count the password change or even going into the admin side of the database as an official Login with a date and time for when that user did those things? Or should those types of logins be ignored?

Thanks for the help.

[CJ_London]

Use a single field for date and time and use the now function to populate it

Don’t forget to consider how to handle situations where the users computer suffers a power failure. Probably won’t matter if you are not trying to track usage

[data808]

Use a single field for date and time and use the now function to populate it

Don’t forget to consider how to handle situations where the users computer suffers a power failure. Probably won’t matter if you are not trying to track usage

Thanks for the suggestion. I changed the Login Date to Login Date Time and removed the Login Time field and did the same thing for the Logout fields.

Would you be able to write VBA using the Now function pulling a value from another hidden form?

[data808]

I created 2 hidden text boxes on the Login form that is bound to Login Date Time and Logout Date Time on the tbl_LoggedIn table.

Now I have tested these boxes on a logout button that is on the menu form just to see if it would work and it does work with the Now() function. It will do a date/time stamp when I first login and then do another date/time stamp when I logout.

However, to save time I been trying really hard to put this code with the Close Event on the Login form and I can't get it to work. An error comes up:

Run-Time error '2448': You can't assign a value to this object

And it points to one of these lines as I have tried both:

[Forms]![Login]![txtLogoutDateTime] = Now()
OR
Me.txtLogoutDateTime.Value = Now()

So basically, after the user logs in, the Login form will remain hidden for the remainder of that session. So I put VBA to handle the close event that when the user clicks on any of the logout buttons I have throughout the database, I already had it to always close the Login form, give a logging out splash screen, then reopen the Login form to start a new fresh session for the next user login.

If I can get the code to work on the Login form for the Close event that would save a lot of time instead of me having to code behind every single logout button throughout the database to have it add the Logout Date and Time. I just can't figure out why it won't let me add a Now() value to the txtLogoutDateTime when it's closing. Is it because the close trigger is coming from another form (Main Menu logout button) and so that close trigger is happening before the close event trigger of the actual Login form can take place?

[Gicu]

Try the Unload event instead of Close....

Cheers,

[data808]

Try the Unload event instead of Close....

Cheers,

Thanks that worked! Now I’m having issues with if the user starts typing in the username or password then changes their mind and closes the form it will save their username into the table. To try and remedy this I have added this to the Close and also tried the Unload as well with no success:

Docmd.Runcommand accmdUndo

So far this line does nothing. I would like it to undo what the user typed into the username text box and not save it. I don’t want unnecessary login attempts that were not successful in the backend table. I have also tried this in the Close and Unload event:

If Me.Newrecord or Me.Dirty Then
Docmd.Runcommand accmdUndo
Exit sub
End If

And that does nothing for the undo.

[Gicu]

Try Me.Undo instead (and use AND instead of OR).

[data808]

Got it working. Thanks for the help! I focused all VBA to go into the Login form and did not have to touch anything else in the database.

I used Open even to open to a new record. Then Unload event to handle the txtLogoutDateTime with Now() value. Then I added VBA to the Login button to Me.Undo certain situations if the user changes their mind and doesn't want to login and quits the app instead. The quit button also had to add the Me.Undo so that it doesn't save any dirty values for logins that were not successful.

Anyway, thanks again for the suggestions.

[data808]

What happens during a power outage? I'm thinking the Logout Date/Time stamp will not function so how would you record something like that? Is there something that can add data for those situations? Or is it something that you have to link to the user for when they login again it will do a check for any Null values for the Logout Date/Time field for their UserID and if so, auto-populate that there was some kind of error or power outage on their previous sign-in?

[Gicu]

I think you got it, if the computer shuts down unexpectedly from whatever reason the logout will remain Null. So, depending on your business rules, you can run an update query and populate it with a default interval from the login date\time (0 minutes or 5 or 1 hour) and have a notes or comments field in the table populated with "outage" or whatever or you just leave it blank and use another query to identify those records and treat them as "power outage".

Cheers,