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:
    PropertyFor a formFor 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:

Field Data Comment
LogDocID Autonumber Primary key
OpenDateTime When the document was opened Blank if a close event cannot find the matching open
CloseDateTime When the document was closed Blank if not marked as closed (e.g. a crash)
DocTypeID 2 = form, 3 = report (the constants acForm and acReport) Forms/reports can have the same name
DocName Name of the form/report  
DocHWnd Operating system number for the window containing the form/report. Distinguish multiple instances of the same form
ComputerName Workstation that opened/closed the document Helps identify a crashing computer that causes corruption
WinUser User as currently logged into the Windows Helps identify a user who is not closing down properly
JetUser User of the JET database engine Will be 'Admin', unless you use JET security
CurView Current view (Reports have a current view in Access 2007 and later.) Particularly useful for reports in recent versions.

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 Comment
1. A form and a report may have the same name. The DocTypeID field tells the difference.
2. You may open multiple instances of the same form (using the New keyword.) The hWnd uniquely identifies each instance.
3. You could open multiple instances of the database on the same computer. The hWnd is unique at any moment, even across different instances of Access.
4. You could be logged into the database from multiple computers. ComputerName tells the difference.
5. Users on different computers could (possibly) be given the same hWnd ComputerName + WinUser + hWnd will be unique at any moment.
6. You use OpenForm when a form is already open. No issue: Access does not fire the Open event again.
7. A report's NoData event can be canceled. No issue: Access fires the close event.
8. The Open event can be canceled programmatically. Log as the last thing in the Open event, only if Cancel has not been set.
9. Your computer's date changes between when you open the form and close it. May not update the correct log record on close.

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:

Problem What to check
No log entry is created for a form/report Are the On Open and On Close properties set?
Make sure the error handler is reporting messages, or comment out the error handler at the top of each routine by adding the single quote to the line:
    'On Error GoTo Err_Handler
OpenDateTime is blank in several log entries. See comment about Case 9 above.
CloseDateTime is blank in several log entries. The computer may be crashing out without closing Access properly. Examine the ComputerName and WinUser fields to determine if this is a hardware problem (network failure, computer overheating, etc), or a user issue (closing Access with Ctrl+Alt+Del, or switching off without closing down properly.)
CurView is blank in several log entries. Versions older than Access 2007 do not provide a current view for reports.
JetUser always shows 'Admin' This is normal for unsecured databases, where no user name/password is required to open the database.

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.


Home Index of tips Top