Trying to mail merge Access 2003 data into Word 2003

[topgeek]

I am trying to create a simple button on a form which when pressed merges the current record into a Word 2003 document.

I am using the shown below - from the MS Knowledgebase.

Option Compare Database
Private Sub cmdPrintLetter_Click()
MergeIt
End Sub

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\HMDoors\test.doc", "Word.document")
' Make Word visible
objWord.Application.Visible = True
'Set mail merge data source as current database
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="TABLE tblEmployee", _
SQLStatement:="Select * from [tblEmployee]"
'Execute the Mail Merge
objWord.MailMerge.Execute
End Function

It works after fashion but I have two problems.

1.I get a pop-box warning me that "Opening this document will run the following SQL command, SELECT * from [tblEmployee]. Is there any way I can supress this?

2. Two copies of the Word document open the first is the original merge file, the second document is the megred document with the data in it. Is there anyway I can only open the second one with the merged data?

Many Thanks

 

[databasis]

I have had problems with Access 2003 and merging to Word with code that worked perfectly fine for Access 97 mail merges. But I've found the solution!!!

The code you have used (above) where you have...

objWord.MailMerge.OpenDataSource _

...is not needed anymore! When you directly mention the name of the mail merge word document in the VBA like you have (test.doc), this document itself knows what the data source is. So when it opens (either by Access telling it to open or you just opening it), it will automatically be linked to the data source. But by you telling it AGAIN in the Access VBA code (with objWord.MailMerge.OpenDataSource), it is confusing Word because it tries to make the connection again and gives all sorts of dialog boxes that you don't want to see, or in your case opens up two word files.

So that's it. In VBA just reference the file and open it like so:

 Dim objWord As Word.Document
    Set objWord = GetObject("C:\HMDoors\test.doc", "Word.document")
    ' Make Word visible
    objWord.Application.Visible = True

Don't use the OpenDataSource part of the code.