Code to fill Word form for multiple records from Access

[rollininclover]

This is the code I've been working with, but it freezes Access:

Private Sub Command66_Click()
'Print Physician Profile.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set rs = Db.OpenRecordset
rst.Open Me.RecordSource, CurrentProject.Connection
'Cycle through records to fill Word form fields
Do While Not rst.EOF
Set doc = appWord.Documents.Open("Z:\My Documents\ACCESS\Access to
Word.doc", , True)
With doc
..FormFields("fldMnemonic").Result = rst!Mnemonic
..FormFields("fldCMAMIPct").Result = rst!CMAMIPct
..FormFields("fldCMCHFPct").Result = rst!CMCHFPct
..FormFields("fldCMPNEPct").Result = rst!CMPNEPct
..FormFields("fldCMSCIPPct").Result = rst!CMSCIPPct
..FormFields("fldCMAMIOPPct").Result = rst!CMAMIOPPct
..FormFields("fldCMSURGOPPct").Result = rst!CMSURGOPPct
..FormFields("fldCMAMINum").Result = rst!CMAMINum
..FormFields("fldCMCHFNum").Result = rst!CMCHFNum
..FormFields("fldCMPNENum").Result = rst!CMPNENum
..FormFields("fldCMSCIPNum").Result = rst!CMSCIPNum
..FormFields("fldCMAMIOPNum").Result = rst!CMAMIOPNum
..FormFields("fldCMSURGOPNum").Result = rst!CMSURGOPNum
..FormFields("fldCMAMIDen").Result = rst!CMAMIDen
..FormFields("fldCMCHFDen").Result = rst!CMCHFDen
..FormFields("fldCMPNEDen").Result = rst!CMPNEDen
..FormFields("fldCMSCIPDen").Result = rst!CMSCIPDen
..FormFields("fldCMAMIOPDen").Result = rst!CMAMIOPDen
..FormFields("fldCMSURGOPDen").Result = rst!CMSURGOPDen
..Visible = True
..Activate
..SaveAs "4Q 09 " & Me!Mnemonic
rst.MoveNext
End With
Loop
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

 

[J_Goddard via AccessMonster.com]

Hi -

You are using Set rs = Db.OpenRecordset, but then you use rst to open and
loop. The Set statement I showed should be Set rst = Db.OpenRecordset.

I recommend you use Option Explicit in all your code modules, to force all
variables to be explitly declared. You code would not compile if you had
used it - you would be amazed at how many errors it catches.

John

 

[rollininclover]

Hi, John, thanks for the help. I changed rs to rst, but Access only produces
one Word form and then locks up. Any other ideas?

Thanks, again, for your help.

 

[J_Goddard via AccessMonster.com]

Hi -

Why are you re-opening the Word document every time go through the loop?
Shouldn't you be opening it before the Do While ... loop, filling the fields
in the loop, then doing the Save As ...
after the loop has completed?

This seems to me the logical approach, though I am not that familiar with it.

HTH

John

Hi, John, thanks for the help. I changed rs to rst, but Access only produces
one Word form and then locks up. Any other ideas?

Thanks, again, for your help.

Hi -

[quoted text clipped - 60 lines]

 

[Mark Andrews]

You could use Albert Kallal's word merge code.
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

or look at it for help
Mark