log of printed reports

[Guest]

I have a set of reports that I print on a regular basis. I need to be able to
keep a track of the date and time at which each report was printed in a table
of query so if it goes missing, i have a record of whether and when it was
printed. I have been unable to come up with a way in which i could generate
this automatically. I would be thankful if i could be helped with this.
Thankyou,
Radhika

 

[Douglas J. Steele]

Create a table with columns ReportName and PrintedDate. Let's assume you
name the table ReportLog

In the Close event of each report, put code like:

Dim strSQL As String

If MsgBox("Did you print this report?", vbYesNo) = vbYes Then
strSQL = "INSERT INTO ReportLog (ReportName, PrintedDate) " & _
"VALUES('" & Me.Name & "', " & Format(Now(), "\#yyyy\-mm\-dd
hh\:nn\:ss\#") & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If

 

[Arvin Meyer [MVP]]

Create a table (tblReportPrinted) with 3 fields: a Primary Key (an
autonumber), DTPrinted, (a date/time field), and ReportName (a text field).
The key is only necessary to aid lookups.

In the code for the button that prints the report, write a record to the
table, something like this (aircode):

In a standard module:

Public Sub RecordPrinting (strReport As String, dtPrinted As Date)
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblReportPrinted", dbOpenDynaset)

With rst
.AddNew
!ReportName = strReport
!DTPrinted = Now
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox "Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

Now in the command button that prints the report:

Sub cmdPrintReport_Click()
Dim strReportName As String

strReportName = "rptYourReport"
DoCmd.OpenReport strReportName

RecordPrinting ("rptYourReport", Now) ' <<This is the line to add

End Sub

 

[Guest]

I tried putting in the code, but it does not give me any data in the table.
Is there any way in which i can put it in as a macros?

 

[Guest]

Thankyou! I managed to get it to work. I do have another question though.
Each report has around 8 clients on it. I need the date and time the report
was printed for each client. The names of all the clients are in a query i
created the report from. Is there maybe a way I can link the query to the
table so by each clients name in the query i can get a date and time printed?
If not, how else could i go about doing this?

 

[Douglas J. Steele]

How are you printing the reports for the different clients? If you're using
a command button, then modified Arvin's suggestion to also write the
customer information to the table.

Realistically though, having such an audit trail proves nothing. Just
because you recorded that you printed it at a particular time doesn't mean
that what you produced was of any use. You could have had printer problems,
so that the report was ruined.

 

[Arvin Meyer [MVP]]

How are you printing the reports for the different clients? If you're
using a command button, then modified Arvin's suggestion to also write the
customer information to the table.

Realistically though, having such an audit trail proves nothing. Just
because you recorded that you printed it at a particular time doesn't mean
that what you produced was of any use. You could have had printer
problems, so that the report was ruined.

Even if the report is successfully printed, you may spoil it later by
spilling coffee on it. Or, you could email it and it is corrupted in the
email. All that the audit trail can attest to is that the report was sent to
the printer.

BTW, Doug's method is both faster to implement, and probably faster running
(although unless you are printing a thousand reports, the speed difference
is negligible). The only advantage my method offers is reusability. If you
implement a standard module function, you can call it for any report, just
feeding it the report name.