Code to monitor report use 2

[password]

I want to keep a track of report usage in a database I maintain. My idea is that at the stage when a user sends a report to the printer I want to 'add 1' to the record for that report in a table containing the list of reports.

Currently users double click on the report they want on a form and my code runs the print/preview etc. It's at this point I want to add my monitor.

Anyone got an idea?

Nick

 

[substitute]

Create a table "Report Usage" with a text field (primary key) Report Name and number field Print Count. Put some code like this in the On Open event procedure for each report:

Dim oDB As DAO.Database
Dim oRst As DAO.Recordset
Dim intOld As Integer

' set reference to the database
Set oDB = CurrentDb
' open the table that records the usage
Set oRst = oDB.OpenRecordset("report usage&quot
' find the record for this report (assumes report name is
' the primary key in the report usage table)
oRst.Index = "PrimaryKey"
oRst.Seek "=", Me.name
If oRst.nomatch Then
' no existing record, create one
oRst.AddNew
intOld = 0
oRst![report name] = Me.name
Else
' edit the existing record and get the previous value
oRst.edit
intOld = oRst![print count]
End If
' increment the value and save the record
oRst![print count] = intOld + 1
oRst.Update
' tidy up
Set oRst = Nothing
Set oDB = Nothing

You might need to set a reference to DAO 3.6 in the VBA editor (tools->references)

Of course, this won't tell you if the user actually printed the report, only that they opened it. If you are only interested in printing from your form, put the code in the click event for the print button on the form and replace Me.Name with the report name string.

Will that do?

 

[password]

Thanks for your time. Sounds good. I'll let you know how I get on.

Nick

 

[password]

Amazingly, after a few amendments to fit in with my own coding, it worked first time!!

I co-maintain the database with a colleague who often creates new reports so I particularly like the bit about creating a new record where there is no current match. Without this my code would fall over in this scenario.

Worth a star in my opinion. Not only because it worked first time (!) but because I think this is a useful technique for monitoring report usage.

Thanks

Nick