Is there a way to tell when a form was opened or a report was printed in an
Access 2000 database. The db has security levels on it and there is a user
log that stamps the user name, date and time every time someone opens the
database but not what they access from that point on.
Hi Joy,
There is nothing built into Access that will do this, but it is quite easy to
create a recording method. I remember a post from MVP Albert Kallal
a couple of years ago where he detailed that he logs which forms, reports,
etc are opened as well as what toolbar/menu bar options are used. He uses
this information to see what options are used the most (or over used), and
which options are used the least. He can then make improvements to the
program. I can provide a link to that post if you would like.
Anyway, here is a little something I just put together. You can increase or
decrease the complexity as much as you desire. This will record who opens
what form or report. Follow these steps.
1. Create a new table called tblActivityLog with the following fields:
fldObjectLogID - AutoNumber
fldObjectName - Text
fldTimeOpened - Date/Time
fldUser - Text
2. Create a new standard module and copy/paste this code into it:
Public Sub subLogActivity(strObjectName As String)
On Error GoTo ErrorPoint
' Jeff Conrad - Access Junkie
' 1/28/05
' You are free to use this code in any projects
' so long as you admit you are an Access Junkie
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblActivityLog", , dbAppendOnly)
With rst
.AddNew
!fldObjectName = strObjectName
!fldTimeOpened = Now()
!fldUser = CurrentUser()
.Update
End With
ExitPoint:
On Error Resume Next
' Cleanup Code
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
3. Make sure you have a reference set to the DAO object library.
4. Compile the code, and save the module as basLogActivity.
5. Now put the following one line of code in EVERY form and report's
Open event that you want to track:
Call subLogActivity(Me.Name)
You will now see that whenever the form or report opens it will make a
record in the table and show what was opened, by whom, and when.
You can then make some nice queries and reports off this table information.
For example, you could do a query by a user and see everything they
have done. You could also query a specific database object and see
who opened it.
Hope that gets you going,
