MailMerge.OpenDataSource SQLStatement WHERE clause

[Wysy]

Hi,
Found a very nice code to allow Word mail merge using an Access query starting the process with a form command button. The following version works like charm
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String


' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"

' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")

' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)

' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService", _
SQLStatement:="SELECT * FROM [qrService]"
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With

' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False

' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17

' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16

' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False

' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub

However i would like to able to apply a filter - just like in normal Word use - and tried to change to code to include a WHERE clause but it simple does not work. Searched pretty much but still unable to get it working. Here is my code
Sub startMergeLB()
Dim oWord As Object
Dim oWdoc As Object
Dim wdInputName As String
Dim wdOutputName As String
Dim outFileName As String
Dim strX As String
strX = "SELECT [tbServices.ServiceID] FROM [tbServices] WHERE (((tbServices.ServiceID)=1371));"



' Set Template Path
'------------------------------------------------
'wdInputName = CurrentProject.path & "\AQHAPaymentOrderSigned.docx"
wdInputName = "D:\DOCS\OwnCloud\DBs\HQHA_Templates\AQHAPaymentOrderSigned.docx"

' Create unique save filename with minutes
' and seconds to prevent overwrite
'------------------------------------------------
outFileName = "AQHAPaymentOrder_" & Format(Now(), "yyyymmddmms")

' Output File Path w/outFileName
'------------------------------------------------
'wdOutputName = CurrentProject.path & "" & outFileName
wdOutputName = "P:\HQHA\DB_Directory\PaymentOrders" & outFileName
Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdInputName)

' Start mail merge
'------------------------------------------------
With oWdoc.MailMerge
.MainDocumentType = 0 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
Connection:="QUERY qrService1", _
SQLStatement:=strX
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With

' Hide Word During Merge
'------------------------------------------------
oWord.Visible = False

' Save file as PDF
' Uncomment the line below and comment out
' the line below "Save file as Word Document"
'------------------------------------------------
oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17

' Save file as Word Document
'------------------------------------------------
'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16

' Quit Word to Save Memory
'------------------------------------------------
oWord.Quit savechanges:=False

' Clean up memory
'------------------------------------------------
Set oWord = Nothing
Set oWdoc = Nothing
End Sub
Tested all around and i can not figure out if the WHERE clause is simple the source of the problem or a syntax.
thanks
Wysy

 

[essaytee]

Friendly advice, code should be wrapped between code tags, makes for better reading, easier reading.

Are you suggesting that this is the offending line:

strX = "SELECT [tbServices.ServiceID] FROM [tbServices] WHERE (((tbServices.ServiceID)=1371));"

If so, go to the query design window, select the SQL view and plonk it it there and run it, see what happens. If the query runs then you can cancel that as the problem.

This query only returns one field, ServiceID.

 

[Wysy]

Sorry for the not professional presentation...
This code is the one you mentioned: copied from the design window.

 

[Gasman]

Well for a start you are using different tables?

Are the field names the same between the tables.?

 

[essaytee]

Sorry for the not professional presentation...
This code is the one you mentioned: copied from the design window.

Now I'm lost. This is what I suggested:

Are you suggesting that this is the offending line:
Code:
strX = "SELECT [tbServices.ServiceID] FROM [tbServices] WHERE (((tbServices.ServiceID)=1371));"
If so, go to the query design window, select the SQL view and plonk it in there and run it, see what happens. If the query runs then you can cancel that as the problem.

This query only returns one field, ServiceID.

What is the result? Did the query run? More importantly, did the WHERE clause work?

Maybe to clarify my slang. Where I said, "plonk it in there", is the same as "paste in that SQL code" into the query SQL design window.

 

[isladogs]

Can you explain why you are using that SQL.
You would get the same result by writing ServiceID=1371

 

[June7]

"Does not work" means what - error message, wrong results, nothing happens?

So the SQL statement was copied from the query builder. Why are you pulling only 1 field?

strX = "SELECT * FROM tbServices WHERE ServiceID=1371;"

If you want dynamic criteria, reference a control on form with concatenation:

strX = "SELECT * FROM tbServices WHERE ServiceID=" & Me.cboID

 

[Wysy]

Thank you! Yes the SQL where statement was indeed the problem. It works great now.Thanks again!

 

[Wysy]

The data for merge was needed to be filtered based on a form' control value.
it really works as expected.