Printing Hyperlinked documents

[Tom]

Hi – small problem that has me stumped, hope somebody can offer some
advice.

I have a small database that collects field data. Included is a table
that stores a hyperlink to a Word document with pictures. Once a week
I need to print out all the records for the previous week including
the attachments, in order.

The code I use is below. The problem I am having is that the reports
and attachments get out of order. For example, I’ll get Report1,
Attachment1, Report2, Report3, Attachment2, etc… Then I have to go
back by hand and reorder everything – very tedious.

I don’t see any way the code below would cause this, so I’m assuming
that the issue in the time it takes the word document to open and
print, the code has moved on to the next record in rst and has printed
it already.

How do I force Access to wait till Word is done printing before moving
on? Or is my assumption wrong and there is a different problem.

Thanks

Tom



**************************
sqlstr = "Select InspectionID from tblInspection WHERE
InspectionDate" _
& " between #" & dtmStart & "# and #" & dtmEnd & "# Order
by InspectionDate"
Set rst = CurrentDb.OpenRecordset(sqlstr, dbOpenSnapshot)
Do Until rst.EOF
strWhere = "InspectionID =" & rst!InspectionID
DoCmd.OpenReport "rptInspection", , , strWhere
DoCmd.Close acReport, "rptinspection"

sqlstr = "Select AttachmentPath from tblAttachment where
inspectionid = " & rst!InspectionID
Set rst2 = CurrentDb.OpenRecordset(sqlstr, dbOpenSnapshot)
Do Until rst2.EOF
Select Case Right(rst2!AttachmentPath, 4)
Case Is = ".doc"
Set objDoc = objWord.Documents.Open("""" & rst2!
AttachmentPath & """", , , , , , , , , , , True)
objDoc.PrintOut
objDoc.Close
' case is = place holder for future support of other
document types
Case Else
MsgBox "Sorry - can't print the attachment associated
with Record " & rst!InspectionID & ": " & vbCrLf
& rst2!AttachmentPath
End Select
rst2.MoveNext
Loop

 

[aaron.kempf]

I normally do stuff like this in SQL Server Reporting Services

Access isn't designed for automation, sorry!

-Aaron

 

[Gina Whipp]

Tom,

Look up DoEvents in Help. This will cause Access to wait until it finishes
processing one 'item' before moving on to the next.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I normally do stuff like this in SQL Server Reporting Services

Access isn't designed for automation, sorry!

-Aaron

 

[Gina Whipp]

Oops forgot... please ignore Aaron as his answer for everything is SQL
Server whether it is relevant or not!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I normally do stuff like this in SQL Server Reporting Services

Access isn't designed for automation, sorry!

-Aaron

 

[Gina Whipp]

Good point... it just gets my last nerve. Will do my best to refrain!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tom]

Thanks for the suggestion - but either I didn't implement it right, or
something else is going on... I'm still getting reports out of order
after changing the code to:

<snip>
Case Is = ".doc"
Set objDoc = objWord.Documents.Open("""" & rst2!AttachmentPath &
"""", , , , , , , , , , , True)
objDoc.PrintOut
objDoc.Close
DoEvents
<snip>

Did I put the DoEvents in the right place? Or is something else going
on?

Thanks again.

Tom

 

[Tom]

Problem solved - in addition to adding the DoEvents, I explicitly
instantiated Word at the beginning of each Case Statement, then set =
nothing prior to the DoEvents instead of relying on one instance of
Word to carry me thru the entire routine.

So the code now looks like:

<snip>
Case Is = ".doc"
Set objWord = CreateObject("word.application")
Set objDoc = objWord.Documents.Open("""" & rst2!AttachmentPath &
"""", , , , , , , , , , , True)
objDoc.PrintOut
objDoc.Close
Set objDoc = Nothing
Set objWord = Nothing
DoEvents
<snip>

Thanks again for your help!

 

[Gina Whipp]

Great point! Tipping my hat, if I were wearing one!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm