You'll need more than a macro to do this. Firstly you need to record that
the report has been printed for the current month. If not then print it and
record that it has. For this you can simply store the date the report is
printed. The best way to do this would be to create a table with columns
ReportName and DatePrinted, the first of text data type, the other of
date/time data type. To get things started enter the report name in
ReportName and in DatePrinted enter a date one month before the current date.
Armed with this table, called ReportLog say, you can now create some code to
check if the report is due to be printed. Do this by creating a procedure in
any standard module. Name the procedure something like PrintMonthlyReport.
The name of the report will be the procedure's argument which will be passed
into it when its run. So the procedure would look like the following, which
I've commented rather more than would normally be done so that you can see
exactly what its doing. Just copy and paste the following into any standard
module, but be sure to save the module under a different name to the
procedure:
''''code begins''''
Public Sub PrintMonthlyReport(strReport As String)
' declare an ADO Command object
Dim cmd As ADODB.Command
' declare variables to hold the dates
Dim dtmLastPrinted As Date
Dim dtmNextprint As Date
' declare variable to hold SQL statement
' to insert new row into ReportLog table
Dim strSQL As String
' get the date when the report was last printed
dtmLastPrinted = DMax("DatePrinted", "ReportLog")
' get the date one month after report last printed
dtmNextprint = DateAdd("m", 1, dtmLastPrinted)
' is it one month since report was last printed?
' if so print report
If dtmNextprint <= VBA.Date Then
DoCmd.OpenReport strReport
' then insert new row into table with today's date.
' note that the date must be formatted mm/dd/yyyy
' regardless of the local system date format
strSQL = "INSERT INTO ReportLog(ReportName,DatePrinted) " & _
"VALUES(""" & strReport & """,#" & _
Format(VBA.Date, "mm/dd/yyyy") & "#)"
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute
End If
End Sub
''''code ends''''
You just need to call the code each time the database opens, passing the
name of the report into the procedure like so:
PrintMonthlyReport "YourReportNameGoesHere"
You can do this in the Open event procedure of the application's opening
form, a switchboard for instance.
Ken Sheridan
Stafford, England
