how to keep track of the times a user logs on

[Guest]

I want to have a record of when a user logged onto a database and the length
of time spent on the database.

 

[Keith]

I want to have a record of when a user logged onto a database and the
length of time spent on the database.

Have some code update a table with CurrentUser() and Now() when your main
form opens and closes. The ENVIRON method can be used to harvest the
network logon ID if necessary.

Regards,
Keith.
www.keithwilby.com

 

[Jeff Conrad]

I want to have a record of when a user logged onto a database and the length
of time spent on the database.

Here is a past post of mine on this issue which may be of help.

1. Create a new table called tblUserLog with the following fields:
LogID - Autonumber (Primary Key)
ProgramUser - Text
TimeIn - Date/Time
TimeOut - Date/Time

Save the new table.

2. I have a switchboard-type form that is the first form open via
Tools | Startup and is always open. You could do something similar
by creating a small hidden form that is always open behind the scenes.

3. In the Declarations area of the Switchboard form code module I have this:
Public LogInT As Date

(That could just as easily be put in a standard module as well)

4. In the Form's Load event I have this code:
(A reference to the DAO object library must be set)

'*************Code Start***************
Private Sub Form_Load()
On Error GoTo ErrorPoint

' Record this Login to tblUserLog
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblUserlog")
With rst
.AddNew
.Fields("ProgramUser") = CurrentUser()
.Fields("TimeIn") = Now()
LogInT = !TimeIn
.Update
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'*************Code End***************

5. In the Form's Unload event I have this code:

'*************Code Start***************
Private Sub Form_Unload(Cancel As Integer)
On Error GoTo ErrorPoint

' Record the time out in tblUserLog
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQLUser As String

Set dbs = CurrentDb()

strSQLUser = "Select * From tblUserlog " _
& "Where ProgramUser = '" _
& CurrentUser() & "' And TimeIn = #" & LogInT & "#"

Set rst = dbs.OpenRecordset(strSQLUser)
With rst
If Not (.EOF And .BOF) Then
'Record was found
.Edit
.Fields("TimeOut").Value = Now()
.Update
End If
End With

ExitPoint:
' Cleanup Code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
'*************Code End***************

This has worked fine for me for many years.
I can then create various reports off this table information.

Side note - Make sure you grant your custom groups appropriate
permissions to this table and/or change the code to use a saved
RWOP query.

Hope that helps,