Controlling timing of printing of mulitple MSAccess Works order reports each with a PDF Drawing to print

I would like to print all the works orders for a job so that they come out on the printer in the correct order - so I have the first Works order print then the associated PDF then the next works order and so on.

I have code to loop through each works order print the works order, then print the PDF and then print the next works order. Using adobe reader to print.

The problem is that for the PDF to print it needs to open adobe reader which displays on the screen and takes some time to print - in the mean time all the access reports (works order) have printed and then the PDF's start.

I am using the below code to print the PDF - strfilelink is the directory location of the PDF e.g. C:\TestPDF.pdf.

Does anyone know of a better way of printing PDF's or a way to delay the printing the next Works order until the PDF is printed.

Be nice not to have adobe open up on the screen - happy to consider other PDF readers.

Public Declare Function ShellExecuteAPI Lib "Shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Call ShellExecuteAPI(Application.hWndAccessApp, "print", strfilelink, "", "", 0)
 

Open in new window

donhannamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You could print the works order to a PDF file, then print this and then the associated PDF to the physical printer.

/gustav
0
donhannamAuthor Commented:
Hi Gustav,

Thanks for that - good idea - However it would be nicer if I could control the printing of the PDF better - If anyone has had some success in controlling printing of PDF's either through adobe or another PDF reader would appreciate some feedback.

Thanks.
0
Gustav BrockCIOCommented:
That might be possible, but you won't be able to control the physical print, so it wouldn't add much of a difference.

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
You haven't shown us the context of the code.  We need to see the code loop that prints both the Access report and the PDF.
0
Jeffrey CoachmanMIS LiasonCommented:
Agree with Pat, ...would be useful to see the code.

But you may be able to insert a 'DoEvents' between each loop to force the system to wait and print both the Report and the PDF.
Something like this:

Do Until rst.eof
    Your code to Print the Report
    Your code to print the PDF
    DoEvents
    rst.MoveNext
Loop

Open in new window


Worth a try...
Note that using Doevents will drain your system resources if you have a lot of files to loop through...

JeffCoachman
0
donhannamAuthor Commented:
Thanks Jeff.

I tried the doevents but it did not work - may be where I have it - I use a subroutine in a module to print the PDF below. There is  a bit of code to get the works orders and file links - I have simplified this below to show just the code to print a works order twice with the PDF between them. I tested this simplified code and the access reports printed first then the PDF's with an Adobe reader screen opening. The report uses the openarg to pass the Works order ID to the report. I also tried the code without the subroutine and same happened - bottom TestPDF

.
Public Sub TestPDF()
    DoCmd.OpenReport "rpt_WorksOrder", acViewNormal, , , , 21659
    Call PrintPDF("C:\TestPDF.pdf")
    DoEvents
    DoCmd.OpenReport "rpt_WorksOrder", acViewNormal, , , , 21659
    Call PrintPDF("C:\TestPDF.pdf")
End Sub

'In Module
'Declarations
Public Declare Function ShellExecuteAPI Lib "Shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

'Sub
Public Sub PrintPDF(strFilelink As String)

       Call ShellExecuteAPI(Application.hWndAccessApp, "print", strFilelink, "", "", 0)
    
End Sub

Public Sub TestPDF()
    DoCmd.OpenReport "rpt_WorksOrder", acViewNormal, , , , 21659
    Call ShellExecuteAPI(Application.hWndAccessApp, "print", "C:\TestPDF.pdf", "", "", 0)
    DoEvents
    DoCmd.OpenReport "rpt_WorksOrder", acViewNormal, , , , 21659
    Call ShellExecuteAPI(Application.hWndAccessApp, "print", "C:\TestPDF.pdf", "", "", 0)
End Sub

Open in new window

0
Gustav BrockCIOCommented:
You are calling two asynchronous processes that don't know about each other. The only way to "sync" these is to add a substantial delay that may or may not guaranty that the one is finished before initiating the other, like:

1. Print first report

2. Wait one minute

3. Print first PDF

4. Wait one minute

5. Print second report

6. Wait one minute

7. Print second PDF

8. Wait one minute

etc.

It could work with some certainty for a few reports, but if you have many it would last "forever" to finish.

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
donhannam,

Still a bit lost in all your code...
...I cant see where/how the looping is moving though the records...?

In any event, ...
...to me, this might be accomplished by simply converting the Access Report directly to a PDF, ...Then all you have to do is call your PDF code for each Report/PDF loop...
Something roughly like this:

'Open the report (Filtered?)
DoCmd.OpenReport "YourReport", acViewNormal, , , , 21659
'Save this report to a PDF
DoCmd.OutputTo acOutputReport, "YourReport", acFormatPDF, "C:\YourFolder\YourReport.pdf"
'Close the report
DoCmd.Close acReport, "YourReport"
'Print this newly created Report PDF
Call PrintPDF("C:\YourFolder\YourReport.pdf")
'Print the existing PDF
Call PrintPDF("C:\TestPDF.pdf")

Open in new window

Worth a try...

JeffCoachman
0
PatHartmanCommented:
If the Access report IS what is being saved to the PDF, why not simply print the Access report, THEN save it to the PDF.  Why automate the PDF writer to print the report.  That just adds unnecessary overhead.
0
Gustav BrockCIOCommented:
It isn't - it is "the associated PDF" that needs to be printed right after the report.

/gustav
0
donhannamAuthor Commented:
Just an update on this one.

I have tried converting the report to a PDF and then printing this PDF then printing drawing PDF. This works better but still prints out of order in most cases as drawing PDF's much larger than WO PDF and taking a while to open - longer than 2 WO Pdf's.

I have spend some time on this looking at options and think I have found the solution in EE.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24148628.html#a23781136

This runs the PDF with a shell command and needs to know the correct version of adobe but I can deal with that.

Requires the module zovek put up with below - I am on 64 bit and version 11 so program file needs to be set up to suit:-

Private Sub print_pdf(xsomefile As String)
     DoEvents
     Debug.Print GetCommandResult("C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe /p /t " & Chr(34) & xsomefile & Chr(34))
 End Sub

Still prints pretty slow and opens adobe dialog - I had a bit of a look at Foxit which may be a better option - I'll have a look at this further when I get some time.

Will test above fully in next couple of days and update.
0
donhannamAuthor Commented:
Further update.

Ok I have spend many more hours and reams of paper on this and still not working.

The routine in previous post from zorvek did what it should and waited till the PDF was printed before returning. However when the PDF prints it opens a print dialog. This needs to be manually closed before the Print of the PDF is recognised as finished. So first PDF prints fine but then hangs till you manually close the adobe reader dialog. So routine works but I don’t want to have to close the adobe dialog after each print.

I have tried using Foxit but get the same results – cannot find a way to delay other than routine above and Foxit also opens a dialog box.

Get same results on Adobe Reader XI and DC

Attached is a test application I am using to try this.

This prints an access report then a PDF 3 times – Idea is for them to be sitting in the printer tray in same order but generally get the access reports printing first then the PDF’s.

Note prints a print PDF I have set up as C:\TestPF.pdf to run you will need to add this or change location on form.

First button prints in normal way I have used in the past – 2n’d button uses Zorveks code and does a shell command with adobe exe location. First prints out of order, second prints in order but requires closing popup adobe window manually.

I am looking at printing say 20 works orders and associated PDFs and this happens often so would be nice to solve this.

Appreciate any help.
TestPDFPrint.accdb
Adobe-Dialog.png
0
Jeffrey CoachmanMIS LiasonCommented:
Perhaps try DoEvents again, ...

Not sure buddy, ...perhaps there will never be an "easy" way to do this...


JeffCoachman
0
Gustav BrockCIOCommented:
One method that has to work is to print the PDF and then assemble this with the associated PDF to a new PDF and finally print the assembled PDF.

There are various (command line) utilities out there like

PDFPrint Command Line

that will handle assembling of several PDFs into one, and I believe some can print as well - which may be faster than using Acrobat which seems to be slow for this kind of job.

/gustav
0
donhannamAuthor Commented:
Update;-

I have found a solution with some help:-

Below uses SumatraPDF which is free - For code below SumatraPDF folder needs to be in access application folder - last parameter True in shell run command is what sets program to wait until the process ends before proceeding to the next.

    Dim stDocName As String
    Dim strFilelink As String
    Dim x As Integer
    Dim objWShell As Object
    Dim strSumatraPath As String
    
    DoCmd.Hourglass True
    
    stDocName = "Report1"
    strFilelink = Trim(Me.PDFLoc)
    
    strSumatraPath = CurrentProject.Path
    If (Right(strSumatraPath, 1) = "\") Then
        strSumatraPath = strSumatraPath & "SumatraPDF\SumatraPDF.exe"
    Else
        strSumatraPath = strSumatraPath & "\SumatraPDF\SumatraPDF.exe"
    End If
    
    Set objWShell = CreateObject("WScript.Shell")
    
    For x = 1 To 3
        DoCmd.OpenReport stDocName, acViewNormal
        objWShell.Run Chr(34) & strSumatraPath & Chr(34) & " -print-to-default " & Chr(34) & strFilelink & Chr(34), 1, True
    Next x

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Thanks. Looks you found a decent solution.

/gustav
0
donhannamAuthor Commented:
Accepted my comment as solution to provide help if others have this issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.