Print Reports from VBA Code - Why is this not working?

[chrisdyson69]

Hi all,

I hope I can get an answer to this one because it has been driving me
crazy.

I have 3 reports in Access 2003 that when printed make up a booklet.
A table of contents is produced from the first report to be printed,
and is used in the second and third reports.
I also have a Microsoft Publisher 2003 document I would like to have
printed at the same time as these reports.

I have created a module and added the following code;

Public Sub print_slf_book()
Dim copies, message, title, style, pass, response, strDoc, retVal
message = "Enter the number of copies you require"
title = "Print Shared Learning Forum List Booklet"
copies = InputBox(message, title)
If copies = 0 Or IsNull(copies) Or copies = "" Then Exit Sub

'************Reports must be printed in this order
'************Otherwise the table of contents is not made
'************TOC is built when printing rptSLF_Booklet_NEW_LOs

For pass = 0 To copies
'Print cover
strDoc = "C:\Program Files\Microsoft Office\Office11\MSPUB.exe /p
""\\Bcserver\pdpbuilder\LOs Guide Book Cover.pub"""
Debug.Print strDoc
'retVal = Shell(strDoc, vbMinimizedNoFocus)
Debug.Print "Cover Page Printed"

'print booklet
strDoc = "rptSLF_Booklet_NEW_LOs"
DoCmd.OpenReport strDoc, acNormal
Debug.Print "SLF Booklet Printed"

'Print List
strDoc = "rptSLF_List"
DoCmd.OpenReport strDoc, acNormal
Debug.Print "SLF List Printed"

'print TOC
strDoc = "rptLOTreeView"
DoCmd.OpenReport strDoc, acNormal
Debug.Print "LO Tree Printed"

Next pass


End Sub


When I run this code from a button on the Switchboard, it only works
half way.
It prints the Publisher file and the first report, but then stops.
It appears to just drop out of the subroutine right after it prints the
first report.
It never prints the last 2 debug.print lines (which I put in to see
what was happening)

I've stepped through the code with (f8) and exactly the same thing
happens. It just stops.
I've tried putting in a five second delay between each report print
line, but still the same.

Is there another way I can achieve this? Why is it not working?

Most grateful for any help recieved.

Chris

 

[chrisdyson69]

Anyone? I've been trying to find ways round this over the holiday
period and still can't get it to work properly.
It still drops out half way through

 

[Rich]

Chris,

I suspect the Print List first report is crashing for some reason. To
debug this code you need to add error code.

At the top of the sub routine add this code or similar:

On error goto errSubPrint
then at the end of your subroutine before the end sub add this code

exitSubPrint:
exit sub
errSubPrint:
msgox err.number & "-" & err.description
resume exitSubPrint

Now try debugging and see if an error occurs.

 

[chrisdyson69]

Rich,

Thanks for your help, sorry for the long delay in replying, I've been
away from work (unforeseen) for a while.

I tried what you suggested, but it never even got to the start of the
routine before Access threw a fit and shut down altogether (with the
Send Error Report dialogue), and offered to repair, compact, and
restart the database.
This happened several times.

What I've done as a workaround is remove the line of code which prints
the MS Publisher document, and pop up a msgbox to say "Don't forget to
print the cover page!" not ideal I know, but hey - that's life!

Thanks again Rich,

Cheers
Chris