Timing problem between Word and Access

[Joop]

Hi all,

In an application that I wrote the following code causes a problem:

Private Sub CmdEnqFormAfdr_Click()
On Error Resume Next

Dim oApp As Object
Dim DocEen As Word.Document
Dim StrWinkelNaam As String, SQLString As String
Dim rcdE As New ADODB.Recordset
Dim cn As New ADODB.Connection

Set cn = CurrentProject.Connection
Set oApp = StartWord
oApp.Visible = False
' oApp.WindowState = wdWindowStateMaximize
SQLString = "SELECT TblEnquette.* from TblEnquette WHERE FldPrEFB = " &
0 & " ORDER BY FldCode, FldNaamCons"
rcdE.Open SQLString, cn, adOpenKeyset, adLockOptimistic
If rcdE.RecordCount > 0 Then
Do
rcdE!FldVerzendDatum = Date
StrWinkelNaam = DLookup("FldLedenNaam", "TblLeden",
"TblLeden!FldLedenCode='" & rcdE!FldCode & "'")
If Left(StrWinkelNaam, 7) <> "Baderie" Then StrWinkelNaam =
"Baderie " & StrWinkelNaam
oApp.Documents.Add StrImpPath & "Enquete.dot"
With oApp.ActiveDocument
.Bookmarks("Winkelnaam").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam1").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam2").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam3").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam4").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam5").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam6").Range.Text = StrWinkelNaam
.Bookmarks("Winkelnaam7").Range.Text = StrWinkelNaam
.Bookmarks("ConsAdres").Range.Text = rcdE!FldStraat & " " &
rcdE!FldHuisnr
.Bookmarks("ConsNaam").Range.Text = rcdE!FldAanhef & " " &
rcdE!FldVoorl & " " & rcdE!FldTussenv & " " & rcdE!FldNaamCons
.Bookmarks("ConsPlaats").Range.Text = Left(rcdE!FldPostcode,
4) & " " & Right(rcdE!FldPostcode, 2) & " " & rcdE!FldWoonpl
.Bookmarks("VerzDatum").Range.Text =
FormatDateTime(rcdE!FldVerzendDatum, vbLongDate)
.Bookmarks("Werk1").Range.Text = rcdE!FldWerk
.Bookmarks("Werk2").Range.Text = rcdE!FldWerk
.Bookmarks("Werk3").Range.Text = rcdE!FldWerk
.Bookmarks("Werk4").Range.Text = rcdE!FldWerk
.Bookmarks("Werk5").Range.Text = rcdE!FldWerk
.Bookmarks("Werk6").Range.Text = rcdE!FldWerk
.Bookmarks("Werk7").Range.Text = rcdE!FldWerk
.PrintOut
End With
Documents.Close SaveChanges:=wdDoNotSaveChanges
rcdE!FldPrEFB = -1
rcdE.Update
rcdE.MoveNext
Loop Until rcdE.EOF
Else
MsgBox ("Er zijn geen enquêteformulieren af te drukken")
End If
rcdE.Close
oApp.Application.Quit wdDoNotSaveChanges
End Sub

The problem:
For all records in the recordset rcdE the document is printed except the
last. This is caused by a timing problem: Word is already being closed
before the last document is flushed to the print que. If I set a breakpoint
before: oApp.Application.Quit wdDoNotSaveChanges
All documents are printed. The application ran well for many month up to
last week, last week the desk-top pc's of my customer where changed into
fast 3Ghz systems (the old ones where 800Mhz). So I guess the Access thread
is executed faster than the Word-printing thread.

My question:
Is there a way to test if Word is ready printing? or to force the priority
of the print tread or something like that?

Thanx all
regards
Joop

 

[Newbie]

would adding a
DoEvents
before you close the form help?

 

[Joop]

That was the first thing I tried..........
thanx

 

[Dave Jones]

Try the following:

While oApp.BackgroundPrintingStatus <> 0
DoEvents
Wend
Insert this after the line
rcdE.Close
This should stop Word closing before printing hs finished.

Dave

 

[Graham R Seach]

Joop,

<<That was the first thing I tried..........>>
I assume you were indicating WithEvents didn't work, in which case, you
undoubtedly didn't use Word's Close event!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html