Mail Merge Module fails to open Data Source

[Robert Blair via AccessMonster.com]

My Access 2K to Word 2K mail merge module is:

***********************************************************
Function MergeDoc(varDocName, varQryName)

Dim objWord As Word.Document

Set objWord = GetObject(varDocName, "Word.Document")
' Make Word visible
objWord.Application.Visible = True
' Set the mail merge data source as the Client database.
objWord.MailMerge.OpenDataSource _
Name:="C:\My Files\Databases\Client.mdb", _
LinkToSource:=True, _
Connection:="QUERY varQryName"
' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
' Now Print it
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
' Exit the Document without saving it and Exit Word
objWord.Application.ActiveDocument.Close SaveChanges = False
objWord.Application.Quit

End Function
***********************************************************

The code for the command button calling the function is:

***********************************************************
Private Sub btnPrintContract_Click()
On Error GoTo Err_PrintContract_Click

Dim varDocName As String
Dim varQryName As String

varDocName = "C:\My Files\Databases\Contract.doc"
varQryName = "qryContractData"

Call MergeDoc(varDocName, varQryName)

Exit_PrintContract_Click:
MsgBox "DONE"
Exit Sub

Err_PrintContract_Click:
MsgBox "Error #" & Err.Number & vbCr & Err.Description
Resume Exit_PrintContract_Click

End Sub
***********************************************************

The query qryContractData is:

***********************************************************
SELECT DISTINCTROW tblClientData.FileNumber, tblClientData.ClientFullName,
tblClientData.SpouseFullName, tblClientData.ClientRole
FROM tblClientData
WHERE (((tblClientData.FileNumber)=[Me]![FileNumber]));
***********************************************************

FileNumber is the key field of the active record on the Form with the
command button; the WHERE is my attempt to limit the query results to only
that record. It is my intent to print one document tied to one record.
The contract is too lengthy to code into an Access Report and is used for
other purposes than as an Access Merge Document.

Contract.doc was created using Word Mail Merge, but after creation, was
copied and pasted to a new document so that it would not be bound to a
specific database.

When I click on the command button on the form, I'm getting an error
message "Error #5922, Word was unable to Open the Data Source". When the
execution halts, I have an instance of Word 2K with the file "Contract.Doc"
active on screen, but since the data source won't open, it's still in the
un-merged format. At least it's executing to that point. I can't seem to
find my coding error.

Is the problem in the way I'm passing the query to the function or do I
need additional code to execute the query before reaching the

objWord.MailMerge.OpenDataSource

code?

Any help is appreciated.

 

[Van T. Dinh]

There are 2 problems, AFAIK:

1. The argument

Connection:="QUERY varQryName"

will look for a Query name "varQryName", not the name in the Variable
varQryName. I think you need to change it to:

Connection:="QUERY " & varQryName

2. You are using a Parameter Query using the reference to the Control on
the Form as the Parameter which won't work. Note that the Query will be
processed by Word and Word won't know the Control on the Form. You will
need to use either the SQL String of the OpenDataSource of MailMerge Object
or used a non-parametrised Query as the Connection.

Check Word VB Help on OpenDataSource for different options you can use.

--
HTH
Van T. Dinh
MVP (Access)

My Access 2K to Word 2K mail merge module is:

***********************************************************
Function MergeDoc(varDocName, varQryName)

Dim objWord As Word.Document

Set objWord = GetObject(varDocName, "Word.Document")
' Make Word visible
objWord.Application.Visible = True
' Set the mail merge data source as the Client database.
objWord.MailMerge.OpenDataSource _
Name:="C:\My Files\Databases\Client.mdb", _
LinkToSource:=True, _
Connection:="QUERY varQryName"
' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
' Now Print it
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
' Exit the Document without saving it and Exit Word
objWord.Application.ActiveDocument.Close SaveChanges = False
objWord.Application.Quit

End Function
***********************************************************

The code for the command button calling the function is:

***********************************************************
Private Sub btnPrintContract_Click()
On Error GoTo Err_PrintContract_Click

Dim varDocName As String
Dim varQryName As String

varDocName = "C:\My Files\Databases\Contract.doc"
varQryName = "qryContractData"

Call MergeDoc(varDocName, varQryName)

Exit_PrintContract_Click:
MsgBox "DONE"
Exit Sub

Err_PrintContract_Click:
MsgBox "Error #" & Err.Number & vbCr & Err.Description
Resume Exit_PrintContract_Click

End Sub
***********************************************************

The query qryContractData is:

***********************************************************
SELECT DISTINCTROW tblClientData.FileNumber, tblClientData.ClientFullName,
tblClientData.SpouseFullName, tblClientData.ClientRole
FROM tblClientData
WHERE (((tblClientData.FileNumber)=[Me]![FileNumber]));
***********************************************************

FileNumber is the key field of the active record on the Form with the
command button; the WHERE is my attempt to limit the query results to only
that record. It is my intent to print one document tied to one record.
The contract is too lengthy to code into an Access Report and is used for
other purposes than as an Access Merge Document.

Contract.doc was created using Word Mail Merge, but after creation, was
copied and pasted to a new document so that it would not be bound to a
specific database.

When I click on the command button on the form, I'm getting an error
message "Error #5922, Word was unable to Open the Data Source". When the
execution halts, I have an instance of Word 2K with the file "Contract.Doc"
active on screen, but since the data source won't open, it's still in the
un-merged format. At least it's executing to that point. I can't seem to
find my coding error.

Is the problem in the way I'm passing the query to the function or do I
need additional code to execute the query before reaching the

objWord.MailMerge.OpenDataSource

code?

Any help is appreciated.

 

[Robert Blair via AccessMonster.com]

Right on target.

Changing the Connection:= as you suggested got me through the merge, but I
was prompted, as you correctly pointed out, for the Me!FileNumber criteria.

It occurs to me that a simpler fix may be to change the query to a Make
Table Query, outputting the single record to a table named
'mrgtblClientData' and then changing the Connection argument to
Connection:= "TABLE mrgtblClientData"
especially since Word mail merges execute faster using tables than using
queries, or so I've read on other forums.

But, I'm not that bright. So,
1. Once I create the Make Table Query, each time it executes, I will be
prompted for whether or not I want to delete the old table. I always will
since the table is only temporary and only to hold the data long enough for
the mail merge to complete. How do I code my way around the prompt?

2. Should the code to execute the Make Table Query, 'qryClientData', be
blaced early on in the Private Sub btnPrintContract_Click() procedure, and
where?

Any help is appreciated.

 

[Van T. Dinh]

If you want to use temp. Table, you should always delete the temp Table (by
code if you want) right after the MailMerge.

Personally, I prefer to use DAO to create / modify the SQL String of the
Query so that all Parameters are resolved before MailMerge actually uses the
Query. This way, I don't use temp Tables which can bloat the database file.

Check the QueryDef Object in DAO.

 

[Robert Blair via AccessMonster.com]

I'm going to take your advice and explore the DAO method. For the time
being, this is how I've resolved the issue, for the benefit of anyone who
checks this thread with a similar issue. I'm still a novice and learning,
and my work here is based upon the advice and coding by people with greater
knowledge than me, and I'm sure there's someone who can streamline the
following code and make it more compact and efficient, but here's what is
working for me and by posting my solution, I hope to give back some of what
has been given to me.

1. The MergeDoc function is now:

************************************************************************

Function MergeDoc(varDocName As String, vartblName As String)

Dim objWord As Word.Document

Set objWord = GetObject(varDocName, "Word.Document")

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

' Set the mail merge data source as the Client database.
objWord.MailMerge.OpenDataSource _
Name:="C:\My Files\Databases\Client.mdb", _
LinkToSource:=True, _
Connection:="TABLE " & vartblName

' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

' Now Print it
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut

' Exit Merged Doc and the Form Doc without saving and Exit Word
objWord.Application.ActiveDocument.Close (wdDoNotSaveChanges)
objWord.Application.ActiveDocument.Close (wdDoNotSaveChanges)
objWord.Application.Quit

Set objWord = Nothing

End Function

************************************************************************

2. The code for the Command Button on the Form to print the contract is:

************************************************************************

Private Sub btnPrintContract_Click()
On Error GoTo Err_PrintContract_Click

Dim varDocName As String
Dim vartblName As String

varDocName = "C:\My Files\Databases\Contract.doc"
vartblName = "mrgtblClientData"

' Run the Make Table Query and save the resulting data to a temporary
table named mrgtblClientData
DoCmd.OpenQuery "qryContractData2"

' Call the merge function to create and print the contract

Call MergeDoc(varDocName, vartblName)

Exit_PrintContract_Click:
MsgBox "DONE"
' Let me know when you're finished, then delete the temporary table and
end
DoCmd.DeleteObject acTable, "mrgtblClientData"
Exit Sub
' Sign the contract, then exit the office for a well-deserved beer
' (haven't learned how to code that one yet ... more beer may help)

Err_PrintContract_Click:
MsgBox "Error #" & Err.Number & vbCr & Err.Description
Resume Exit_PrintContract_Click

End Sub

************************************************************************

3. Create the Make Table Query, saving the result(s) to a temporary table
named mrgtblClientData. Be sure the field names match those of the Word
mail merge template.

4. In the VBA window, click Debug / Compile to be sure there are no
errors. Save your hard work and don't forget the button on the Form
which calls Word, merges and prints the document.

NOTE: The other thing which has helped me avoid errors and speed the
process up is, in Word,
a. Create the form document using the Tools / Mail Merge / Create /
Form Letter option.
b. Once the form document is ready for use (test it using Word while
still linked to the data source within Word), use the Edit / Select All and
copy to a new document. This will break the data source connection with
the form in Word but leave you with the mergefields intact.
c. Save the document using the same name and folder location as used by
the Function which is connected in the Private Sub btnPrintDoc procedure
(passed as the varDocName argument).

As soon as I have worked out the DAO solution, I'll post it below.

Hope this helps someone else. Thanks to all for the help I was given in
getting this far.

 

[Van T. Dinh]

Just the start for you in DAO:

1. Create 2 Queries called:

qryContractData_Base
qryContractData_Custom

qryContractData_Base is the same with your original SQL without the WHERE
condition, i.e. is:

****
SELECT DISTINCTROW tblClientData.FileNumber, tblClientData.ClientFullName,
tblClientData.SpouseFullName, tblClientData.ClientRole
FROM tblClientData
****

The SQL String of the qryContractData_Custom is not important as we modify
it in code. You can simply copy the qryContractData_Base and rename the new
copy qryContractData_Custom

2. Before you start the MailMerge execution code, code something like

Dim db As DAO.Database
Dim qdf as DAO.QueryDef

Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs("qryContractData_Custom")
qdf.SQL = db.QueryDefs("qryContractData_Base").SQL & _
" WHERE tblClientData.FileNumber = " & Me.FileNumber
qdf.Close

Set qdf = Nothing
Set db = Nothing
DoEvents

and then continue your MailMerge code but use the qryContractData_Custom as
the DataSource. Since this query is not parametrised (the Parameter has
been resolved to explicit value), Word MailMerge will be able to use it.

This way, you don't have to use temporary Table.

It is probably true that MailMerge can use the Table faster than the Query
but without method, you need to use a Make-Table Query to select Record(s)
to create the Table (which need hard-disk writes) then the code needs to
read from the hard-disk again. You then have to delete the Table (hard-disk
access again).

With the customised Query like above, the whole process only involve the
selection similar to the selection done by your Make-Table Query. Thus, the
customised Query should be much faster for the whole process.

 

[Robert Blair via AccessMonster.com]

Talk about a jump-start..... Thanks. I'll be working on it this evening.