MailMerge.OpenDataSource SQLStatement WHERE clause (1 Viewer)

Wysy

Registered User.
Local time
Yesterday, 22:35
Joined
Jul 5, 2015
Messages
333
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

Need a good one-liner.
Local time
Today, 16:35
Joined
Oct 20, 2008
Messages
512
Friendly advice, code should be wrapped between code tags, makes for better reading, easier reading.

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 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

Registered User.
Local time
Yesterday, 22:35
Joined
Jul 5, 2015
Messages
333
Sorry for the not professional presentation...
This code is the one you mentioned: copied from the design window.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:35
Joined
Sep 21, 2011
Messages
14,048
Well for a start you are using different tables?

Are the field names the same between the tables.?
 

essaytee

Need a good one-liner.
Local time
Today, 16:35
Joined
Oct 20, 2008
Messages
512
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

MVP / VIP
Local time
Today, 05:35
Joined
Jan 14, 2017
Messages
18,186
Can you explain why you are using that SQL.
You would get the same result by writing ServiceID=1371
 

June7

AWF VIP
Local time
Yesterday, 21:35
Joined
Mar 9, 2014
Messages
5,424
"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

Registered User.
Local time
Yesterday, 22:35
Joined
Jul 5, 2015
Messages
333
Thank you! Yes the SQL where statement was indeed the problem. It works great now.Thanks again!
 

Wysy

Registered User.
Local time
Yesterday, 22:35
Joined
Jul 5, 2015
Messages
333
The data for merge was needed to be filtered based on a form' control value.
it really works as expected.
 

Users who are viewing this thread

Top Bottom