Mailmerge and Opendatasource

[Jas0r]

I have a button on a form that I want to run a mail merge. Currently
it runs fine for a select all in a table producing a document with all
the records in it but I want it to produce a document from a filter.

Example
Private Sub PrintMerge_Click()

Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Set oMainDoc = oApp.Documents.Open("C:\TemplateDoc.doc")
oApp.Visible = True

With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=CurrentDb.Name, SQLStatement:="SELECT *
FROM [tblJobDetails]"
End With

With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With

oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1

End Sub

Works fine for all records in the table but if I put a WHERE clause in
the sql as below

..OpenDataSource Name:=CurrentDb.Name, SQLStatement:="SELECT * FROM
[tblJobDetails] WHERE [Job_No] = '944'"

Opens the template but then asks for a table as if it cant find it and
when the table is selected no filter (where clause) is used

Any ideas?

 

[Arvin Meyer MVP]

Replace your SQL statement:

"SELECT * FROM [tblJobDetails]"

with the SQL statement from your filter.