How to create a report that prints at certain frequencies

[kelly4133]

I have a database that generates work orders of tasks that are performed at
certain frequencies. For example, check oil in truck - frequency every 4
weeks. How do I create a report (in form of a work order) that will print
automatically when that date comes up?

 

[Ken Sheridan]

First create a WorkOrderPrintLog table with a text column Task and a
date/time column DatePrinted to log when the reports are printed. Then write
a VBA function in a standard module as follows:

Public Function PrintWorkOrder(strTask, intWeeks, strReport)

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String
Dim dtmLastPrinted As Date

strCriteria = "Task = """ & strTask & """"
dtmLastPrinted = Nz(DMax("DatePrinted", "WorkOrderPrintLog",
strCriteria), 0)

If DateDiff("ww", dtmLastPrinted, VBA.Date) >= intWeeks Then
' print report
DoCmd.OpenReport strReport

' insert current task and date into PrintLog table
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

strSQL = "INSERT INTO WorkOrderPrintLog(Task, DatePrinted) " & _
"VALUES(""" & strTask & """, #" & Format(VBA.Date, "mm/dd/yyyy")
& "#)"

cmd.CommandText = strSQL
cmd.Execute
End If

End Function

To print the report rptWorkOrderCheckOil every 4 weeks (or immediately if
its not logged as printed before) call the above function at start-up (e.g.
via an autoexec macro or in the open event procedure of the database's
opening form such as a switchboard) with:

PrintWorkOrder "Check Oil", 4, "rptWorkOrderCheckOil "

Ken Sheridan
Stafford, England

 

[Dooxa]

fadlan isku kay sheeg walaalo ?
pls who are you ?
were are you ?