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