Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / May 2007

Tip: Looking for answers? Try searching our database.

How to create a report that prints at certain frequencies

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
kelly4133 - 14 May 2007 00:30 GMT
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 - 14 May 2007 12:35 GMT
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

> 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?
Dooxa - 14 May 2007 17:23 GMT
fadlan isku kay sheeg walaalo ?
pls who are you ?
were are you ?

>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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.