Track Usage of Reports

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

I want to create a table and track the usage of reports and forms. Since
Access can't tell you when an object was last used, I will do it myself.
This will let me delete old forms and reports without worrying that someone
somewhere might still need it.

I want to create a public function that I can insert into the OnOpen event
of every report and form. I don't want to copy and paste the same code into
every single object if the code turns out to be 5 or 10 lines.

I will build the function as below. this is just air code that I threw
together and know that it needs at least a little work.

I have a few questions. How do I call this function from the OnOpen event
of my report and send the report name over to the function? Also, can
someone look over my code and see if they see any problems?

Thanks for your help. If there is code out there somewhere that already
does this, let me know. I would hate to reinvent the wheel here!

Rick B


----------------------------------------------------------------------

Public Function LogUsage()

DimVarObjectName as String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblObjectLoging")
With rs
rs.Edit
rs.AddNew
rs![Object] = VarObjectName
rs![Date] = Date()
rs![UserID] = fOSUserName()
rs.Update
End With
rs.Close
Set rs = Nothing

End Function
 
Hi Rick,

Your air code is really close, just a few modifications.
Try this:

' Code Start
Public Function LogUsage(VarObjectName As String)
On Error GoTo ErrorPoint

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblObjectLoging")
With rs
rs.AddNew
rs![Object] = VarObjectName
rs![UsedDate] = Date
rs![UserID] = fOSUserName()
rs.Update
End With

ExitPoint:
rs.Close
Set rs = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " _
& Err.Description, vbExclamation, _
"Unexpected Error"
Resume ExitPoint

End Function
' Code End

I would avoid using Date as a field name so I have UsedDate.

Now in each form/report's Open event just add this one line of code:

LogUsage (Me.Name)

That's it.
 
Jeff:

That works great!! Thanks.

Of course, I corrected my typo and made the table name "tblObjectLogging".

Rick B


Hi Rick,

Your air code is really close, just a few modifications.
Try this:

' Code Start
Public Function LogUsage(VarObjectName As String)
On Error GoTo ErrorPoint

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblObjectLoging")
With rs
rs.AddNew
rs![Object] = VarObjectName
rs![UsedDate] = Date
rs![UserID] = fOSUserName()
rs.Update
End With

ExitPoint:
rs.Close
Set rs = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " _
& Err.Description, vbExclamation, _
"Unexpected Error"
Resume ExitPoint

End Function
' Code End

I would avoid using Date as a field name so I have UsedDate.

Now in each form/report's Open event just add this one line of code:

LogUsage (Me.Name)

That's it.
 
You're welcome, glad to help.
I assumed since it was air code it was a simple typo on the table name.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Rick B said:
Jeff:

That works great!! Thanks.

Of course, I corrected my typo and made the table name "tblObjectLogging".

Rick B


Hi Rick,

Your air code is really close, just a few modifications.
Try this:

' Code Start
Public Function LogUsage(VarObjectName As String)
On Error GoTo ErrorPoint

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblObjectLoging")
With rs
rs.AddNew
rs![Object] = VarObjectName
rs![UsedDate] = Date
rs![UserID] = fOSUserName()
rs.Update
End With

ExitPoint:
rs.Close
Set rs = Nothing
Exit Function

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " _
& Err.Description, vbExclamation, _
"Unexpected Error"
Resume ExitPoint

End Function
' Code End

I would avoid using Date as a field name so I have UsedDate.

Now in each form/report's Open event just add this one line of code:

LogUsage (Me.Name)

That's it.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Rick B said:
I want to create a table and track the usage of reports and forms. Since
Access can't tell you when an object was last used, I will do it myself.
This will let me delete old forms and reports without worrying that someone
somewhere might still need it.

I want to create a public function that I can insert into the OnOpen event
of every report and form. I don't want to copy and paste the same code into
every single object if the code turns out to be 5 or 10 lines.

I will build the function as below. this is just air code that I threw
together and know that it needs at least a little work.

I have a few questions. How do I call this function from the OnOpen event
of my report and send the report name over to the function? Also, can
someone look over my code and see if they see any problems?

Thanks for your help. If there is code out there somewhere that already
does this, let me know. I would hate to reinvent the wheel here!

Rick B


----------------------------------------------------------------------

Public Function LogUsage()

DimVarObjectName as String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblObjectLoging")
With rs
rs.Edit
rs.AddNew
rs![Object] = VarObjectName
rs![Date] = Date()
rs![UserID] = fOSUserName()
rs.Update
End With
rs.Close
Set rs = Nothing

End Function
Click to expand...
Click to expand...
 
Back
Top