Module to Merge causes DB placed in Admin and fails 1

[Dragunsfyre]

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.

 

[PHV]

At least rreplace this:
Connection:="QUERY varQryName"
By this:
Connection:="QUERY " & varQryName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dragunsfyre]

That worked as far as OpenDataSource line of code is concerned, I was able to get to the fully merged document, but, in the process, the query did not pick up the matching criteria
WHERE tblClients.FileNumber = Me!FileNumber
I was prompted for Me!FileNumber before the query executed.

It occurs to me that a simpler solution may be to change the query to a Make Table Query to a table named mrgtblClientData and change the connection to
Connection:= "TABLE mrgtbleClientData"
especially since mail merge executes faster with data from a table that with data from a query.

But, I'm not that bright. I can create the query to create the table containing a single row, but, how do I make the query execute since I'm going to be prompted at execution to delete the old table. Since the table's only purpose is to temporarily hold data for a Word Mail Merge, I will always want to delete it and replace it with the data from the make table query. I think that the code to execute the make table query should be inserted early in the Private Sub btnPrintContract_Click(), but where?

And what is the code which will automatically get me past the Delete Old Table prompt?

 

[RoyVidar]

I'm more in favour of deleting the records in the table, in stead of drop and recreate, but that's another matter.

Executing the queries on the current database, should take care of the messages:

[tt]on error resume next
currentdb.execute "drop mytemptable"
' or
' currendtb.execute "delete from mytemptable"
if err.number<>0 then
err.clear
end if
on error goto <your error handler>[/tt]

The resume next to take care of situations where the table doesn't exist. I don't know much about the mailmerge stuff, but I think there may be some differences between versions, and that I've started to believe later versions have, shall we say, some reservations against parameterized queries.

There's a faq by pseale that pretty much circumvents most of the hassle with mailmerge, through creating a text file for the document to llink to. But by what you say, I'd first start with getting this to work, then perhaps when time allows play a little with this on a copy of your db, faq181-5088.

Roy-Vidar

 

[Dragunsfyre]

Thanks for the help, Roy. I really appreciate it. I also received a suggestion to try using a DAO procedure and I'm going to experiment some with that and see what I can do.