Microsoft Access: Applications and Utilities

Provided by Allen Browne, February 2009.  Last Updated: April 2010.

Log usage of forms and reports

This utility lets you track who opens each form and report in your database, and when. Logging gives feedback on how people use your application, and may help diagnose crashes or corruptions.

To use it, copy the logging table and code from the sample database, and set two properties for each form or report. You need not write or modify any code.

To use it in your database:

1. Download the sample database (21kb zipped, for Access 2000 and later.)
2. Open your database, and import the table (tblLogDoc) and the module (ajbLogDoc.)
   In Access 2007 or 2010, click the Access icon on the Import chunk of the External Data ribbon.
   In earlier versions, choose Get External on the File menu.
3. Set the On Open and On Close properties of each form or report in your database, like this:
   

   Property	For a form	For a report
   On Open	=LogDocOpen([Form])	=LogDocOpen([Report])
   On Close	=LogDocClose([Form])	=LogDocClose([Report])

   (Note: do not substitute your form/report name; use Form and Report exactly as shown.)

If these properties are already set to [Event Procedure], click the Build button (...) beside this. Access will open the code window. Insert a line into the event procedure code, like this:
    Call LogDocOpen(Me)
and for the Close event:
    Call LogDocClose(Me)

To temporarily turn off logging for all forms/reports in your database, change True to False on this line (towards the top of the module):
    Private Const mbLogDox As Boolean = False

You can view the code for this utility in a separate window.

What's in the log?

The fields in logging table are:

Does it handle all cases?

Each time you open a form/report it calls LogDocOpen() to record the event. When you close the document again, LogdocClose() finds the matching record, and writes the time it was closed.

The tough part is identifying the 'matching record', to ensure the right log entry is updated when you close the form. As Access is multi-user, it must handle cases like this:

Case 8 is important. If a form or report's Open event is canceled in code or in a macro, it does not open and no Close event will be fired. You must therefore call the logging routine only if the Open event is not canceled. This kind of thing:

Private Sub Form_Open(Cancel As Integer)
    'Run your tests and cancel the open if needed.
    If MsgBox("Really open?", vbOKCancel) = vbCancel Then
        Cancel = True
    End If
    'Then end the routine like this:
    If Not Cancel Then
        Call LogDocOpen(Me)
    End If
End Sub

You cannot avoid the situation above by using the form's Load event instead of its Open. There are cases where Access fires the Load event even when the Open event is canceled, e.g. if you include this line in Form_Open:
    RunCommand acCmdRecordsGoToNew

Case 9 is also an issue. The logging code assumes that you will close a form after you opened it. That assumption may get messed up if the system date is changed (by you or a synchronization script) after the form opens. If the date was set forward to after the time when the form opened, the routine won't find the correct entry to update. In this case it will create a new logging entry where the OpenDateTime is null. Conversely, if you had this form open yesterday and crashed out without closing the form, you will have an entry dated yesterday where the CloseDateTime is null. Now if the computer's date is set back prior to yesterday, and you just happened to get the same hWnd assigned when you opened the same form today, the logger will update the wrong entry.

Cases other than those listed above should be fine. Switching to or from design view logs correctly. Closing a form in Filter-By-Form still triggers Form_Close. Some events do not fire for reports in Access 2007 and later (section events in the new Report or Layout views), but the report's Open and Close events still do. If you are aware of a case we have not considered, email us.

Interpreting the log

The log can provide clues to diagnose issues:

Extending the logger

You will probably want to create reports to show the records where OpenDateTime is null, or CloseDateTime is null.

After checking it all works, you may want to suppress any logging error messages so it is completely transparent to the user. Remove the single quote mark from the If and End If lines in the LogError() function. Better still, replace it with a real error logger — otherwise you will have no idea if the logger is not working  correctly.

If you want to record each record each person visits, use Form_Current to log the form name and primary key value to another table.