Using Access query in MS Word merge

  • Thread starter Thread starter sml
  • Start date Start date
S

sml

I have a query which is parameterized for the id of record. The id is found
on an open form and I also have a button which opens a MS Word mailmerge
document which used the parameteriezed query as its datasource.

When I open the Word document the data connection is there and the document
merges, but when I use the button on the form, the data connection is lost
when the word document is open.

How can I have a button which opens a Mail Merged Word document that keeps
its data source?

The code (found on the web) that runs the button on the form is below.

Many thanks for whatever help you can provide.

Best.

Private Sub cmdWord_Click()
On Error GoTo Err_cmdWord_Click

Dim oApp As Object
Dim doc As Object
Dim strDocName As String

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

'change path
strDocName = "P:\All Agency\FY11RFND\APPLICATION - 2011 AGENCY Competitive
Bid\NOI\NOI Confirmation of Receipt Letter.docx"
Set doc = oApp.Documents.Open(strDocName)
'Set doc = oApp.Documents.Add(strDocName)

Exit_cmdWord_Click:
Exit Sub

Err_cmdWord_Click:
MsgBox Err.Description
Resume Exit_cmdWord_Click

End Sub
 
I use Access database with Word mail merge. Data is loaded in Access and
pulled out using a query to select what you want and the Word document.

Word Mail-Merge---
Open a blank Word document, click on menu TOOLS - Letters and Mailings -
Mail Merge.

It opens an additional window on the right and has step-by-step instructions
and choices. You can also backup in the process.

Works fine for me.
 
Solved. Found the missing bit below which I added and it merged from a button
on a form just fine.

With oDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="PATH\DOCUMENT.accdb", Connection:="QUERY
[qry_rptConfirmationLetter]", SQLStatement:="Select * from
[qry_rptConfirmationLetter]", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

If .State = wdMainAndDataSource Then
.Execute Pause:=False
End If
End With
 
Back
Top