Merge Access Form (current record) to Word Template

Argh!  

I had this working the other day and now it just wont work right.

I have to merge a few fields from a form that has data entry in it.  Only a few of the fields get merged
i use a button to save the record and open the Merge Form and then use a query to give me just those fields with =Forms!QrySIDRecord!ValidationNumber in the criteria of the Unique ID of the query.  That returns the current record on the Merge Form.  And then word opens the template but wont merge and stops at the OpenDataSource if I change anything then the QrySIDRecord doesnt work and I cant return the current record to the form.

Does anyone have any ideas?

Dim objWord As Word.Application
Dim doc As Word.Document

Set objWord = CreateObject("word.application") 'Create an instance of Word
objWord.Visible = True 'Make sure you can see it!!
objWord.WindowState = wdWindowStateMaximize 'Maximize it, otherwise is always about 1/4 of screen

'Open a document based on a template for merging
objWord.Application.Documents.Add Template:="C:\Documents and Settings\XXXX.XXX-XX\Desktop\RecordVrs2_03122003.dot", NewTemplate:=False


'Store name of document for later use
varDocName = objWord.ActiveDocument.Name

objWord.ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\XXXXX.XXX-XXX\Desktop\QID.mdb", _
LinkToSource:=True, _
Connection:="QUERY QrySIDRecord", _
SQLStatement:="Select * from [QrySIDRecord]"

If Err.Number = 94 Then
                objWord.Selection.Text = ""
                Resume Next
            End If
           

End Sub

The error is this line: objWord.ActiveDocument.MailMerge.OpenDataSource

and the error message is this: Run-Time error '-2147417851 (80010105)'
Automation error
The server threw an exception

Thanks

Stokh




   
stokhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

boomstamCommented:
Stokh,

it seems to me you have 2 options in the OpenDataSource mixed up.
1) Use the Connection part to define a ODBC connection, and combine this with the SQLStatement part to define the records to select.
2) Use the Connection part to point to a Query from your MDB file. In this case you don't use the SQLStatement part.

Just define a QUERY "QrySIDRecord" in the MDB file using "Select * from [QrySIDRecord]"
and then call:
objWord.ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\XXXXX.XXX-XXX\Desktop\QID.mdb", _
LinkToSource:=True, _
Connection:="QUERY QrySIDRecord"



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nexusnationCommented:
Hi stokh,
This question has been abandoned and needs to be finalized (101 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.