Solved Form Close Time not stamped (1 Viewer)

[MajP]

I expect to see like that

I am confused. Why would you expect that? Your code does not do anything like that.
Your code creates a new record for the login event and then a new record for the logoff event. You have no code that would do that. You would have to check that User for an Existing Pending event prior to the LogOff and probably no other completed event between the Pending event.
Are you planning to Keep the Pending record and overwrite it as completed? Or do you just want the completed record to have the time of the login?

 

[MajP]

This code does this

    qdf.Parameters("[UserName]") = Environ("USERNAME")
    qdf.Parameters("[ComputerName]") = Environ("COMPUTERNAME")
    qdf.Parameters("[ComputerIP]") = "1.1.1"
    qdf.Parameters("[ActivityLogin]") = IIf(activity = "Login", Now(), GetLogIn(Environ("Username")))
    qdf.Parameters("[ActivityLogOff]") = IIf(activity = "Logoff", logOffTime, Null)
    qdf.Parameters("[ActivityStatus]") = IIf(activity = "Logoff", "Completed", "Pending")
    Debug.Print qdf.SQL
    qdf.Execute dbFailOnError
    MsgBox "Logoff event fired: " & Now() & " Activity is : " & Nz(activity, "Null")
    Debug.Print
    Set qdf = Nothing
    Set db = Nothing

End Sub
Public Function GetLogIn(UserName As String) As Variant
    GetLogIn = DMax("ActivityLogin", "UsersActivity", "OSUserName = '" & UserName & "'")
End Function

Query1 Query1

OS User Name	Computer Name	IP Address	Activity Login Date & Time	Activity LogOff Date & Time	Activity Status
MajP	DESKTOP-EPBQ18N	1.1.1	8/1/2023 10:10:56 AM​	​	Pending
MajP	DESKTOP-EPBQ18N	1.1.1	8/1/2023 10:10:56 AM​	8/1/2023 10:11:01 AM​	Completed
MajP	DESKTOP-EPBQ18N	1.1.1	8/1/2023 10:11:07 AM​	​	Pending
MaP	DESKTOP-EPBQ18N	1.1.1	8/1/2023 10:11:07 AM​	8/1/2023 10:11:11 AM​	Completed

So the question is do you want to keep the pending record or overwrite it as Completed with the logOff time.
If you want to update, you have no code to do that. You then need to check for log off and do an update query.
1. Find the ID of the previous Pending record and
Set ActivityStatus = complete and Logoff = Now where ActivityID = PreviousActivtyIDThatWasPendingForCurrentUser.

probably need both of these to get the ID of the last login.

Public Function GetLogIn(UserName As String) As Variant
    GetLogIn = DMax("ActivityLogin", "UsersActivity", "OSUserName = '" & UserName & "' AND ActivityStatus = 'Pending'")
End Function
Public Function GetPreviousPending(UserName As String) As Variant
  Dim Lastlogin As Variant
  Lastlogin = GetLogIn(UserName)
  If Not Lastlogin = "" Then
    Lastlogin = Format(Lastlogin, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
   GetPreviousPending = DLookup("UserAccessID", "UsersActivity", "ActivityLogin = " & Lastlogin)
  End If
End Function

 

[June7]

Login should never be Null.

Your code needs to find existing record and UPDATE the logoff and status, not INSERT a new record.

Since form is BOUND and set for DataEntry, use form Load instead of Open event to set values of new record and it should still be on the new record when form closes. You don't need that complex sub.

Private Sub Form_Close()
CurrentDb.Execute "UPDATE UsersActivity SET ActivityLogoff = Now(), ActivityStatus='Completed' WHERE UserAccessID=" & Me.UserAccessID
End Sub

Private Sub Form_Load()
With Me
    .OSUserName = Environ("USERNAME")
    .ComputerName = Environ("COMPUTERNAME")
    .ComputerIP = GetComputerIP()
    .ActivityLogin = Now()
    .ActivityStatus = "Pending"
End With
DoCmd.RunCommand acCmdSaveRecord
End Sub

Can bind form directly to table instead of query.

 

[smtazulislam]

Thank you very much sir. I appreciate you.
Your code is given two recored, "Pending " & "Completed"

However, exactly I would like to Login recored ID had time stamped and same record to LogOff to saved in the table.
I modified your function :

Private Sub LogActivity(activity As String)
    Dim db As DAO.Database
    Dim logOffTime As Variant

    Set db = CurrentDb()
    Debug.Print "Logoff event fired: " & Now(), activity

    If activity = "Logoff" Then
        logOffTime = Now()
    Else
        logOffTime = Null
    End If

    Dim strSQL As String
    Dim UserAccessID As Variant
    UserAccessID = GetPreviousPending(Environ("Username"))

    If activity = "Login" Then
        If Not IsNull(UserAccessID) Then
            strSQL = "INSERT INTO UsersActivity (OSUserName, ComputerName, ComputerIP, ActivityLogin, ActivityStatus) " & _
                     "VALUES ('" & Environ("USERNAME") & "', '" & Environ("COMPUTERNAME") & "', '1.1.1', #" & Now() & "#, 'Pending');"
        Else
            strSQL = "UPDATE UsersActivity SET ActivityLogOff = #" & Now() & "#, ActivityStatus = 'Completed' " & _
                     "WHERE UserAccessID = " & UserAccessID & ";"
        End If
    End If
    
    If activity = "Logoff" Then
        If Not IsNull(UserAccessID) Then
        strSQL = "UPDATE UsersActivity SET ActivityLogOff = #" & Now() & "#, ActivityStatus = 'Completed' " & _
                 "WHERE UserAccessID = " & UserAccessID & ";"
        End If
    End If

    If strSQL <> "" Then
        db.Execute strSQL, dbFailOnError
    End If

    Set db = Nothing
End Sub

Now result is :
UsersActivity

User Access ID	OS User Name	Computer Name	IP Address	Activity Login Date & Time	Activity LogOff Date & Time	Activity Status
-1789256303	smtaz	TAZUL_ISLAM	1.1.1	08/01/2023 18:08:32	08/01/2023 18:08:34	Completed
1099345566	smtaz	TAZUL_ISLAM	1.1.1	08/01/2023 18:09:47	08/01/2023 18:09:50	Completed
1690902577	smtaz	TAZUL_ISLAM	1.1.1	08/01/2023 18:09:37	08/01/2023 18:09:39	Completed

again thank for your help.

 

[June7]

Did you see post #23?

 

[smtazulislam]

Login should never be Null.

Your code needs to find existing record and UPDATE the logoff and status, not INSERT a new record.

Since form is BOUND and set for DataEntry, use form Load instead of Open event to set values of new record and it should still be on the new record when form closes. You don't need that complex sub.

Private Sub Form_Close()
CurrentDb.Execute "UPDATE UsersActivity SET ActivityLogoff = Now(), ActivityStatus='Completed' WHERE UserAccessID=" & Me.UserAccessID
End Sub

Private Sub Form_Load()
With Me
    .OSUserName = Environ("USERNAME")
    .ComputerName = Environ("COMPUTERNAME")
    .ComputerIP = GetComputerIP()
    .ActivityLogin = Now()
    .ActivityStatus = "Pending"
End With
DoCmd.RunCommand acCmdSaveRecord
End Sub

Can bind form directly to table instead of query.

Nice ! Thank you very much, I appreciate you.

I test your code, Its worked .
if you have to put 'Form_Unload' event.

I can't put 'close button' in this form, I had setting this form open and hide. when user Log off then it is closed.

 

[June7]

Okay, so with Unload event instead of Close, don't need SQL.

Private Sub Form_Unload(Cancel As Integer)
Me.ActivityLogOff = Now()
Me.ActivityStatus = "Completed"
End Sub