Solved Form Close Time not stamped (1 Viewer)

[smtazulislam]

Hi, appreciate any help.
I tried is twice ways, however its not worked.

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

    Set db = CurrentDb()

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

    Dim strSQL As String
    strSQL = "INSERT INTO UsersActivity (OSUserName, ComputerName, ComputerIP, ActivityLogin, ActivityLogOff, ActivityStatus) " & _
             "VALUES ([UserName], [ComputerName], [ComputerIP], [ActivityLogin], [ActivityLogOff], [ActivityStatus]);"

    Set qdf = db.CreateQueryDef("", strSQL)

    qdf.Parameters("[UserName]") = Environ("USERNAME")
    qdf.Parameters("[ComputerName]") = Environ("COMPUTERNAME")
    qdf.Parameters("[ComputerIP]") = GetComputerIP()
    qdf.Parameters("[ActivityLogin]") = IIf(activity = "Login", Now(), Null)
    qdf.Parameters("[ActivityLogOff]") = IIf(activity = "Logoff", logOffTime, Null)
    qdf.Parameters("[ActivityStatus]") = IIf(activity = "Logoff", "Completed", "Pending")

    qdf.Execute dbFailOnError

    Set qdf = Nothing
    Set db = Nothing

End Sub

Moreover, tried it change this line

qdf.Parameters("[ActivityLogOff]") = IIf(activity = "Logoff", logOffTime, Null)

Private Sub Form_Open(Cancel As Integer)
LogActivity "Login"
End Sub

Private Sub Form_Close()
LogActivity "Logoff"
End Sub

 

[ebs17]

The effort with the parameter query (it's good that you can use something like this) is very enormous in order to create exactly one record. An alternative with a recordset is technically just as fast, although performance certainly doesn't play a role here. The recordset solution is also simpler and clearer.

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("UsersActivity", dbAppendOnly)
With rs
   .AddNew
   !UserName = Environ("USERNAME")
   ' ...
   If activity = "Logoff" Then
      !ActivityLogOff = Now
      !ActivityStatus = "Completed"
   ElseIf activity = "Login" Then
      !ActivityLogin = Now
      !ActivityStatus = "Pending"
   Else
      ' unexpectedly
   End If
   .Update
   .Close
End With

What are you saying?

 

[June7]

Review https://stackoverflow.com/questions...in-the-different-contexts-in-microsoft-access

I've never used parameters. I have used 1) CurrentDb.Execute and concatenation and 2) opening recordset then edit or add methods on recordset.

 

[Edgar_]

Works fine for me.

Does it trigger an error or something?

 

[arnelgp]

maybe put the Sub in a Module and make it a Public Sub.

 

[Minty]

Add a debug.print to the start of your routine

Debug.Print "Logoff event fired: " & Now() , activity

And see if it ever fires, I doubt it is.

 

[smtazulislam]

The effort with the parameter query (it's good that you can use something like this) is very enormous in order to create exactly one record. An alternative with a recordset is technically just as fast, although performance certainly doesn't play a role here. The recordset solution is also simpler and clearer.

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("UsersActivity", dbAppendOnly)
With rs
   .AddNew
   !UserName = Environ("USERNAME")
   ' ...
   If activity = "Logoff" Then
      !ActivityLogOff = Now
      !ActivityStatus = "Completed"
   ElseIf activity = "Login" Then
      !ActivityLogin = Now
      !ActivityStatus = "Pending"
   Else
      ' unexpectedly
   End If
   .Update
   .Close
End With

What are you saying?

Thank you very much, I tried it, however its not function on ".AddNew".
Later, twice fields is not stamped "ActivityLogOff & ActivityStatus "

 

[smtazulislam]

Review https://stackoverflow.com/questions...in-the-different-contexts-in-microsoft-access

I've never used parameters. I have used 1) CurrentDb.Execute and concatenation and 2) opening recordset then edit or add methods on recordset.

I tried Recordset . moreover QueryDef

 

[smtazulislam]

Works fine for me.

Does it trigger an error or something?

Its had two ID used.
a)Login
b) LogOff

 

[smtazulislam]

maybe put the Sub in a Module and make it a Public Sub

I had change it
Private to Public.
Not work

 

[smtazulislam]

Add a debug.print to the start of your routine

Debug.Print "Logoff event fired: " & Now() , activity

And see if it ever fires, I doubt it is.

I dont get you well. Where is it
qdf.Execute dbFailOnError
Debug.Print
like that ...

 

[Minty]

I would add it at the start of the process:

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

Put a break point after that , then look at the immediate window or use a message box:

MsgBox "Logoff event fired: " & Now() & " Activity is : " & NZ(activity,"Null")

 

[Mike Krailo]

Private Sub Form_Close()
LogActivity "Logoff"
End Sub

What if you use the Form_Unload event instead?

 

[smtazulislam]

I uploaded the db. Its LogOff and LogStatus not stamped.

 

[MajP]

Login cannot be a REQUIRED field if you set it to null in the LOGOFF. Set required to NO
There error message is very clear and states exactly that.

 

[smtazulislam]

Login cannot be a REQUIRED field if you set it to null in the LOGOFF. Set required to NO
There error message is very clear and states exactly that.

Great, Thank you very much.

    qdf.Parameters("[ActivityLogOff]") = IIf(activity = "Logoff", logOffTime, Now())

Its now stamped. However it not change "ActivityStatus" = Completed

EDIT :
Now get a error :3314

 

[MajP]

Again, that error is because in the table the Login field is REQUIRED. You cannot set Login to Null if it is required. Works for me and status is logged.

 

[smtazulislam]

Thank you very much.
Its not stamped same UserAccessID. See

User Access ID	OS User Name	IP Address	Computer Name	Activity Login Date & Time	Activity LogOff Date & Time	Activity Status
-1199581822​	win	1.1.1	HRD-01	01/08/2023 16:10:21​	​	Pending
1690895412​	win	1.1.1	HRD-01	01/08/2023 16:10:12​	​	Pending
1047188098​	MajP	1.1.1	DESKTOP-EPBQ18N	01/08/2023 08:59:44​	​	Pending
-1630841893​	win	1.1.1	HRD-01	​	01/08/2023 16:10:23​	Completed
1511814437​	win	1.1.1	HRD-01	​	01/08/2023 16:10:17​	Completed
-2006463269​	MajP	1.1.1	DESKTOP-EPBQ18N	​	01/08/2023 08:59:51​	Completed

 

[MajP]

I do not understand or see the problem. If you logoff it is stamped as "Completed" if you log in it is "Pending". What are you expecting to see?

qdf.Parameters("[ActivityStatus]") = IIf(activity = "Logoff", "Completed", "Pending")

 

[smtazulislam]

I expect to see like that

User Access ID	OS User Name	IP Address	Computer Name	Activity Login Date & Time	Activity LogOff Date & Time	Activity Status
-1199581822	win	1.1.1	HRD-01	01/08/2023 16:10:21	01/08/2023 16:10:23	Completed
1690895412	win	1.1.1	HRD-01	01/08/2023 16:10:12	01/08/2023 16:10:17	Completed