SQL statement with Mailmerge

[rperre]

Hey I can succesfully open a mailmerge document from a access form with the right query in it, BUT.....

I want to make this procedure able to do all kinds of mailmerges and wish to include a SQL statement to select a certain record. How do I do this. I did look at the mailmerge FAQ somebody made, but really wish to continue with this solution!

I call this procedure like this (DOES NOT WORK, I get a 3011 error: The microsoft jet database engine could not find the object 'SELECT * FROM printquery WHERE [Tenant ID] = '3'', Make sure the object exists and that you spell its name and the path name correctly):

MailMerge "payments_information.doc", "SELECT * FROM printquery WHERE [Tenant ID] ='" & [Forms]![Print_reports]![Combo0] & "'"

If I call the procedure like this (WORKS):

MailMerge "payments_information.doc", "printquery"


The mailmerge procedure:

Public Sub MailMerge(conTemplate As String, conQuery As String)
Dim strPath As String
Dim strDataSource As String
Dim doc As Object

On Error GoTo HandleErrors
' Delete the rtf file, if it already exists.
strPath = FixPath(CurrentProject.Path)
strDataSource = strPath & conQuery & ".doc"
Kill strDataSource

' Export the data to rtf format.
DoCmd.OutputTo acOutputQuery, conQuery, _
acFormatRTF, strDataSource, False

' Start Word using the mail merge template.
Set wrdApp = CreateObject("Word.Application")
Set doc = wrdApp.Documents.Add(strPath & conTemplate)

' Do the mail merge to a new document.
With doc.MailMerge
.OpenDataSource Name:=strDataSource
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
If .State = wdMainAndDataSource Then
.Execute
End If
End With

' Display the mail merge document.
wrdApp.Visible = True

ExitHere:
Set doc = Nothing
Set wrdApp = Nothing
Exit Sub

HandleErrors:
Select Case Err.Number
Case 53 ' File not found.
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Select
End Sub

 

[traingamer]

If [Tenant ID] is numeric, it doesn't need the single quotes around the variable [Forms]![Print_reports]![Combo0].

traingamer

 

[rperre]

Hey, thanks for the reply,

I did try that and the same error comes up only without the single quotes:

3011 error: The microsoft jet database engine could not find the object 'SELECT * FROM printquery WHERE [Tenant ID] = 3'

Is there a workaround for this type of thing?
Anything else I can try before I just change the whole thing over to the FAQ mailmerge sample, which I do not completely understand. I do understand how this one works!

Thanks,

Richard

 

[PHV]

In your case, the OutputTo method expect a saved query name, not a SQL string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rperre]

ah, thanks , I'll try to save the query and do the merge!

Richard

 

[rperre]

OK I'm using this as a work around for now, I will be trying to put this in the mailmerge procedure later (that way I only have to call the procedure with the SQL statement), but it works for now!

Work around VB:

Set sqltext = CurrentDb.CreateQueryDef("tempprintquery", "SELECT * FROM printquery WHERE [Tenant ID] = [Forms]![Print_reports]![Combo0]")
MailMerge "payments_information.doc", "tempprintquery"
DoCmd.DeleteObject acQuery, "tempprintquery"

one thing is still bothering me, when the mailmerge is completed and the word document is showing, it shows the field code name and not the merged data. I have to click on the "view merged data" button first. Can I set this property with VB.

Thanks,

Richard

 

[PHV]

myWordObj.ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rperre]

awesome, I couldn't fine that property!

Thanks a lot,

Richard