By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 748 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Please help! Strange behavior: Access > Word Mail Merge code

P: n/a
Hello all you fine folks,

Here is some code I am using to merge data from a single record Access
2K3 table into a NEW word document based on a pre-defined Word merge
template doc. All I want to happen is as follows:

User defines a storage path and name for the new Word document then
clicks a "Create" button on a form. From that point on I use the
following code:

====================== BEGIN CODE ============================
Public Sub MergeIt(docPath As String)

Dim fID As Long
Dim strSQL As String
Dim objWord As Word.Document

On Error GoTo MergeIt_Error

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

fID = Me.txtFaxID

strSQL = "SELECT tblSavedFaxes.FaxID, tblSavedFaxes.From,
tblSavedFaxes.FullName, " & _
"tblSavedFaxes.Facsimile, tblSavedFaxes.Telephone, " & _
"tblSavedFaxes.CID, tblSavedFaxes.[DACSFileNum],
tblSavedFaxes.Importance, " & _
"tblSavedFaxes.RE, tblSavedFaxes.MsgBody INTO
TEMP_FaxPrint " & _
"FROM tblSavedFaxes WHERE (((tblSavedFaxes.FaxID)=" & fID
& "));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Set objWord = GetObject(ProgDrive &
"\DACS\DocTemplates\FaxMerge.dot", "Word.Document")

' ' Make Word visible.
' objWord.Application.Visible = True

' Set the mail merge data source as a query on the DACS Database.

objWord.MailMerge.OpenDataSource Name:=CurrentDb.Name,
LinkToSource:=True, Connection:="TABLE TEMP_FaxPrint",
SQLStatement:="SELECT * FROM [TEMP_FaxPrint]"

objWord.MailMerge.Destination = wdSendToNewDocument

' Execute the mail merge.
objWord.MailMerge.Execute

'Save the new document per pathname provided by user (docPath)
ActiveDocument.SaveAs docPath, , , , True

' Make Word visible.
objWord.Application.Visible = True

' Close master document and release object variable
objWord.Close wdDoNotSaveChanges
Set objWord = Nothing
====================== END CODE ============================

The problem is that it works fine the FIRST time I do it (most of the
time). As soon as I try to do it again, the template gets opened in a
separate instance of Word in the background and it does not close.
Also, sometimes, a new instance of the Access app is spawned as well.

All that needs to happen is for the new word document to be opened
with the data from the one record table merged into the pre-defined
merge fields with the document already saved once. Then the user can
complete the document and click save again before they close Word --
at which point, Word should be closed and they should be right back at
the form they started from and there should be only one instance of
Access open.

Anyone have a clue what I'm missing?

Thanks for your help.

--LC

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.