IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: Access Database Repair Service
An in-depth repair service for corrupt Microsoft Access files
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
IN THE SPOTLIGHT: Code Protector
Standard compilation to MDE/ACCDE format is flawed and reversible.
Provided by Allen Browne, February 2009
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.
(Note: do not substitute your form/report name; use Form and Report exactly as shown.)
Property For a form For a report On Open =LogDocOpen([Form]) =LogDocOpen([Report]) On Close =LogDocClose([Form]) =LogDocClose([Report])
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.
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 only) | Particularly useful for reports in Access 2007 |
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 (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.
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. |
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 |
Rate this article:
This is a cached tutorial, reproduced with permission.
iTech Masters | VAT: GB202994606 | Terms | Sitemap | Newsletter