Access - Word Merge

[Leigh46137]

Hope someone can help me out here!

Versions:
I am using Access and Word 2000.

What I need to accomplish:
I need to Merge a series of 10-15 word documents with the data in the record
that is currently displayed. I also have a couple of other forms with one doc
that merge and print separately, not as part of the series.

How it is set up now:
Currently, I have a query set up for each merge that I need to perform. In
the RecordID field of the query, I have set criteria like this:
[Forms]![formname]![RecordID]. The Word docs point to the query in the
database and are set to preview the data -- there will only be one record, so
this works fine. The user clicks one button to merge and print. The code
walks through the list of docs and calls the print procedure for each file.

Private Sub Print(stFile As String)
Dim apWord As Word.Document
Set apWord = GetObject(stFile & ".doc", "Word.Document")
apWord.Application.Visible = True
apWord.Application.Options.PrintBackground = False
apWord.Application.ActiveDocument.PrintOut
apWord.Application.Quit SaveChanges:=False
Set apWord = Nothing
End Sub

There are a few problems with this:
1. It opens Word, then opens the document every single time. It should only
open Word once, then open each document, print and close the document.
2. It opens a new instance of Access every single time, too! It is the new
instance that is used to get the data, and since there is no form displayed,
the query can't find the RecordID, so it prompts for the parameter.
3. The query sometimes can't find the record until I have closed and
re-opened the form -- even when I click the save button to save the record
first.
4. The most aggravating problem is that yesterday this worked! It used the
instance of Access already open and therefore knew which record to use. I
have no clue what I changed to make it not work anymore. I only added a new
table, query, form and merge doc. (This one prints separate from the series.)

Any suggestion on how I can make this work?
Thanks!

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?TGVpZ2g0NjEzNw==?=,

I can explain what's happening, but I can't guarantee you'll get these
straightened out using the technique you've been using up until now.

1. Mail merge uses a DDE connection by default in version 2000. DDE means it
needs to find a running version of Access, based on a specific caption in the
title bar. It then sets up a running "conversation" with the application in
order to pick up the data.

2. The most common cause for additional Access instances opening is that you've
changed the caption in the title bar. Get rid of the Title change, and you MIGHT
take care of that problem. Not necessarily, but it's possible.

3. I'd suggest using ODBC to connect, instead, but then you can't use a
parameter query. This would be possible; you could pass commands to the Word
document to change the SQL it uses to bring across the data...

4. GetObject with a file name doesn't work so well with Word. Sometimes, it
won't work at all. Other times, it won't work as you expect. I'd change your
code so that you use a global variable for both the Word application and the
Word document. Use GetObject or CreateObject (or a combination) for
Word.Application. Then use the Documents.Open command to open the main merge
document. And do this only ONCE (when the user starts the form, for example; and
release them when the form closes; you'll need to check if the user closes
things on you and reconnect as required)

5. the query not being able to find the record may have something to do with the
second version of Access being opened. The change may not be "visible" in
another instance until the record is released, or something similar.

6. A way around all these problems would be to avoid mail merge entirely, and
automate Word to the extent of sending the data directly into the document. If
you're interested in that approach, there's a sample file (old) WdAcc97.zip on
my website that shows how this can be done using VBA automation and DAO. It's in
the Mail Merge FAQ, Special merges section, under the topic about one-to-many
datasets.

Versions:
I am using Access and Word 2000.

What I need to accomplish:
I need to Merge a series of 10-15 word documents with the data in the record
that is currently displayed. I also have a couple of other forms with one doc
that merge and print separately, not as part of the series.

How it is set up now:
Currently, I have a query set up for each merge that I need to perform. In
the RecordID field of the query, I have set criteria like this:
[Forms]![formname]![RecordID]. The Word docs point to the query in the
database and are set to preview the data -- there will only be one record, so
this works fine. The user clicks one button to merge and print. The code
walks through the list of docs and calls the print procedure for each file.

Private Sub Print(stFile As String)
Dim apWord As Word.Document
Set apWord = GetObject(stFile & ".doc", "Word.Document")
apWord.Application.Visible = True
apWord.Application.Options.PrintBackground = False
apWord.Application.ActiveDocument.PrintOut
apWord.Application.Quit SaveChanges:=False
Set apWord = Nothing
End Sub

There are a few problems with this:
1. It opens Word, then opens the document every single time. It should only
open Word once, then open each document, print and close the document.
2. It opens a new instance of Access every single time, too! It is the new
instance that is used to get the data, and since there is no form displayed,
the query can't find the RecordID, so it prompts for the parameter.
3. The query sometimes can't find the record until I have closed and
re-opened the form -- even when I click the save button to save the record
first.
4. The most aggravating problem is that yesterday this worked! It used the
instance of Access already open and therefore knew which record to use. I
have no clue what I changed to make it not work anymore. I only added a new
table, query, form and merge doc. (This one prints separate from the series.)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail