Solved Form Close Time not stamped

smtazulislam

Member
Local time
Today, 21:37
Joined
Mar 27, 2020
Messages
808
Hi, appreciate any help.
I tried is twice ways, however its not worked.

Code:
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
Code:
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
 
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.
Code:
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?
 
Last edited:
maybe put the Sub in a Module and make it a Public Sub.
 
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.
 
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.
Code:
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 "
 
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 ...
 
I would add it at the start of the process:

Code:
  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")
 
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.
 
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.
Code:
    qdf.Parameters("[ActivityLogOff]") = IIf(activity = "Logoff", logOffTime, Now())
Its now stamped. However it not change "ActivityStatus" = Completed

EDIT :
Now get a error :3314
 

Attachments

  • Screenshot 2023-08-01 155249.png
    Screenshot 2023-08-01 155249.png
    19.3 KB · Views: 113
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.
 

Attachments

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


User Access IDOS User NameIP AddressComputer NameActivity Login Date & TimeActivity LogOff Date & TimeActivity Status
-1199581822​
win1.1.1HRD-01
01/08/2023 16:10:21​
Pending
1690895412​
win1.1.1HRD-01
01/08/2023 16:10:12​
Pending
1047188098​
MajP1.1.1DESKTOP-EPBQ18N
01/08/2023 08:59:44​
Pending
-1630841893​
win1.1.1HRD-01
01/08/2023 16:10:23​
Completed
1511814437​
win1.1.1HRD-01
01/08/2023 16:10:17​
Completed
-2006463269​
MajP1.1.1DESKTOP-EPBQ18N
01/08/2023 08:59:51​
Completed
 
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?
Code:
qdf.Parameters("[ActivityStatus]") = IIf(activity = "Logoff", "Completed", "Pending")
 
I expect to see like that

User Access IDOS User NameIP AddressComputer NameActivity Login Date & TimeActivity LogOff Date & TimeActivity Status
-1199581822win1.1.1HRD-0101/08/2023 16:10:21
01/08/2023 16:10:23
Completed
1690895412win1.1.1HRD-0101/08/2023 16:10:12
01/08/2023 16:10:17
Completed
 

Users who are viewing this thread

Back
Top Bottom