Last updated 2008/01/21
You can track usage statistics for forms and reports by adding a record to a table in the form or reports OnOpen event. For modules you can do this as the third line in any function or subroutine. (The first line is a description of the function/subroutine. The second line is your On Error Goto line. (Hmm, you likely can within macros by using a function call with the name of the macro as a parameter.)
For forms this is the line inserted in the OnOpen event.
Call AddFormReportLogging("Forms",
Me.Name)
For reports
Call
AddFormReportLogging("Reports", Me.Name)
The module is as follows. Now I could've saved storage space by doing a lookup on the form/report name in a table and save the ID field. However I felt this would slow down the app a little. However I didn't actually do any timing tests on this.
Public Sub AddFormReportLogging(strObjectType As String, strObjectName As String) On Error GoTo tagError Dim intObjectType As Integer, strSQL As String, lngUserID As Long Select Case strObjectType Case "Forms" intObjectType = 2 Case "Reports" intObjectType = 3 Case Else intObjectType = 9 End Select strSQL = "INSERT INTO zsysObjectUsage ( zouObjectType, zouObjectName, zouUserID, zouDateTimeUsed ) " & _ "IN 'Z:\Objectusage.mdb' " & _ "VALUES (" & intObjectType & ", '" & strObjectName & "', " _ & fOSUserName & ", " & Format$(Now, JetDateTimeFmt) & ");" CurrentDb.Execute strSQL, dbFailOnError Exit Sub tagError: If Err.Number = 2450 Then ' Forms not found lngUserID = 0 Resume Next Else MsgBox Err.Description End If Exit Sub End Sub
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngx As Long Dim strUserName As String On Error GoTo tagError strUserName = String$(254, 0) lngLen = 255 lngx = apiGetUserName(strUserName, lngLen) If lngx <> 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = "" End If Exit Function tagError: MsgBox Err.Description Exit Function End Function
For info on JetDateTimeFmt see Date/Time: Return Dates in US #mm/dd/yyyy# format