Mail Merge from access to word 1

[dougjack10]

I have a fairly simple request and I have been looking all over the internet for an answer.

All I want to do is;

Click on a button in access;

1. It opens a mail merge word document that has already been setup and linked to a query in the access database.
2. The document then merges to one letter, based on the current record in access form when I click the access button. The query in the mail merge document seems to be causing the problem.
3. The original mail merge document closes with no changes to the document.
4. The merged letter with the one record remains open.

I did have this working on a database years ago, using access 2003 and 2007. Although it does not seem to work in access 2010. See the code below. I ideally I am looking for some simple code that effectively has one variable, where I only need to put in the file path to the mail merge document.
If I remove the criteria in the access query, ([forms]![frm_name]![ID feild) that is linked to the merge document, it works and I get all 36 records merged letters into one document, but first of all I had to amend the registry,

https://support.microsoft.com/en-us...ain-document-that-is-linked-to-a-data-source.

This concerns me as I suspect this will need to be done to every machine that uses the database which seems impractical
My query includes criteria, [forms]![frm_name]![ID field] with the correct terms, and this seems to be causing the problem.

The error message is;

The method or property is not available because the document is not a mail merge.

Any ideas would be appreciated?
------------------------------------------------------------------------------------------------------------------------
Private Sub Command172_Click()
Dim stDocName As String

On Error GoTo Err_Command172_Click
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("F:\myfiles\letter.doc")
WordObj.Visible = True
With WordObj
.ActiveDocument.MailMerge.Execute
End With

With WordObj
Set WordDoc = .Documents.Open("F:\myfiles\letter.doc")
End With

With WordDoc
.Close SaveChanges:=False
End With

Exit_Command172_Click:
Exit Sub

Err_Command172_Click:
MsgBox Err.Description
End Sub

 

[dhookom]

I typically create a Word Merge file from Access so the Word document doesn't know or care about Access. It only consumes the file that was created by Access.

If the form/control reference is causing the issue then consider simply modifying the SQL property of a saved query so the value is in the SQL rather than [forms]![frm_name]![ID feild].

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Thanks for your reply, sounds like a good suggestion.

I tried setting up the mail merge from access, and I get this message pop up in word, ODBC driver login failed, it is then asking for authorisation. I played around with some of my security settings in word, but no luck.

This thread deals with it, although it is not totally conclusive,

https://access-programmers.co.uk/forums/showthread.php?t=293276.

The previous mail merge that worked did not have a front end and backend, and this post suspects this may be causing the problem.

I might try and explore some of the options in here,

http://www.helenfeddema.com/Code Samples.htm,

but the code looks a little more complicated and I am fairly new and inexperienced with VBA,.

 

[dhookom]

Again, have you considered simply exporting your query to a delimited or Word mail merge format? Then you don't have to worry about ODBC or anything else.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

I might just go back to a standard Microsoft access report although this is not ideal.

The select queries or variable queries do not appear to work from access. Actually I noticed they do not appear in Microsoft word if you want to merge from a query. I.e Forms]![frm_name]![Feild], or [Enter a surname]

I can filter the query in word but that does not help as it is not specific to the form I want to filter from in access, if I create the merge document from access by exporting the query, that still doesn't work.

I get the data link properties error message

 

[dhookom]

I'm not sure what you mean by "if I create the merge document from access by exporting the query, that still doesn't work" since you didn't provide any explanation.

The other option I suggested takes advantage of code like faq701-7433



Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Thanks Dhookom

I have got this to work, albeit I am now extracting from the current access form and using bookmarks in word, to insert fields from access into word. Ideally I would rather use the mailmerge, but this is a good interim solution.

The only problem I now have is that if I have a field that is blank in access it will not merge to the bookmark. There must be any easy way I can amend these lines , .Item("title").Range.Text = Me.title, so it will ignore blanks.

Private Sub Command238_Click()

'Declare variables for storing strings

Dim addressline2var As String
Dim Wrd As New Word.Application
Dim MergeDoc As String


'Declare an instance of Microsoft Word
Set Wrd = CreateObject("Word.Application")

'specify the path and name to the word document
MergeDoc = "C:\Users\Payment template invoice.docx"

'open the document template, make it visible
Wrd.Documents.Add MergeDoc
Wrd.Visible = True

'replace each bookmark with current data

With Wrd.ActiveDocument.Bookmarks

.Item("title").Range.Text = Me.title
.Item("abn").Range.Text = Me.abn
.Item("amount").Range.Text = Me.amount

End With

 

[dougjack10]

I seem to be having some luck with the mail merge option.

I seem to have sorted out the ODBC issue by creating the word merge file from access.
The code now works and opens the document and merges the document.

However it does not work if I refine the query and put the following in the access criteria, either [forms]![frm_name]![ID field] or alternatively I tried [Enter a ID], as criteria hoping a box would pop up and I would enter the number associated with the record I want.

I see you have suggested that If the form/control reference is causing the issue then consider simply modifying the SQL property of a saved query so the value is in the SQL rather than [forms]![frm_name]![ID field]. You have kindly referred me to takes advantage of code like FAQ701-7433: How to Change SQL property of saved query (DAO).

I have had a go with this SQL code in red below and tried to insert it into my code below in green, with the applicable SQL wording. See my wording below. I am second guessing a little in trying to get this to work. First of all I seemed to have got the same ODBC connection issue. I then received a message to say the select statement includes a reserved word of an argument name that is misspelled of missing, or the punctuation is incorrect

I have also included the Function shown in the code as per the link above FAQ701. I wonder if you could help me to insert the sql code into the existing mail merge code.

My wording
Dim strSQL as String
Dim strOldSQL as String
strSQL =
"SELECT TBL_ownerpayments.IDpayments
FROM TBL_ownerpayments
WHERE (((TBL_ownerpayments.IDpayments)=[Enter a ID]))
ORDER BY TBL_ownerpayments.IDpayments;"
strOldSQL = fChangeSQL("qry_payments2",strSQL)

Your wording
Dim strSQL as String
Dim strOldSQL as String
strSQL = "SELECT field1, field2, field3 FROM tblMyTable " & _
"WHERE OrderDate BETWEEN #" & Me.txtStart & "# AND #" & _
Me.txtEnd & "# " & _
"ORDER BY field2, field1 DESC"
strOldSQL = fChangeSQL("qselMyQuery",strSQL)
---------------------------------------------------------------------------------------------------------------------
My code, without SQL above
Private Sub Command235_Click()

Dim stDocName As String
On Error GoTo Err_Command235_Click
Dim WordObj As Word.Application
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("C:\Users\Paymentsinvoice1.docx")
WordObj.Visible = True

With WordObj
.ActiveDocument.MailMerge.Execute
End With

With WordObj
Set WordDoc = .Documents.Open("C:\Users\Paymentsinvoice1.docx")
End With

With WordDoc
.Close savechanges:=False
End With

Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
MsgBox Err.Description
End Sub

 

[dhookom]

Do you actually have line breaks in your code (consider using the code TGML tag)?

Dim strSQL as String
Dim strOldSQL as String
strSQL =
"SELECT TBL_ownerpayments.IDpayments
FROM TBL_ownerpayments
WHERE (((TBL_ownerpayments.IDpayments)=[Enter a ID]))
ORDER BY TBL_ownerpayments.IDpayments;"
strOldSQL = fChangeSQL("qry_payments2",strSQL)

I would not use a parameter prompt like [Enter a ID]. Consider pulling the value from a control on a form.

Are you merging only the IDPayments field in your Word document?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Yes it is the IDoptionpayments field that I am merging. It was always this IDoptionpayments even though I referred to it as IDPayments above.

I have simply tried to amend this field so it is IDoptionpayments=44.

Hopefully when it is working I will try [Forms]![frm_ladnownerpayments]![IDoptionpayments]

The current message I get is;

Invalid SQL statement expected Delete, Insert, Procedure, Select or Update. Any ideas.
I suspect I need to include code with the words Update, but not sure where.

Also any ideas on how I can solve the bookmark issue when fields are null as per my previous post.

Private Sub Command235_Click()


Dim stDocName As String
   
On Error GoTo Err_Command235_Click
Dim WordObj As Word.Application
    Set WordObj = CreateObject("Word.Application")
    WordObj.Documents.Open ("C:\Users\Paymentsinvoice1.docx")
    WordObj.Visible = True
         

Dim strSQL As String
Dim strOldSQL As String
strSQL = "SELECT TBL_owneroptionpayments.IDoptionpayments FROM TBL_owneroptionpayments" & _
"WHERE (((TBL_owneroptionpayments.IDoptionpayments)=44))" & _
"ORDER BY TBL_owneroptionpayments.IDoptionpayments; " & _
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)


    
    With WordObj
    .ActiveDocument.MailMerge.Execute
    End With
    
     With WordObj
     Set WordDoc = .Documents.Open("C:\Users\Paymentsinvoice1.docx")
     End With
    
    With WordDoc
   .Close savechanges:=False
    End With
   
Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
    MsgBox Err.Description

 
End Sub

Function fChangeSQL(qry_landownerpayments2 As String, strSQL As String) As String
'=============================================================
' basQueryDefs.ChangeSQL
'-------------------------------------------------------------
' Purpose : update the SQL property of a saved query
' Copyright: Duane Hookom
' Author : Duane Hookom
' Notes :
'-------------------------------------------------------------
' Parameters
'-----------
' pstrQueryName (String) Name of saved query
' strSQL (String)        SQL Statement
'-------------------------------------------------------------
' Returns: the previous SQL statement
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 07-09-2001 DKH:
'=============================================================
' End Code Header block
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(qry_landownerpayments2)
    fChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

 

[dhookom]

You are missing spaces between words in your SQL statement and have added and extra line continuation.

Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments[highlight #FCE94F] [/highlight]" & _
    "WHERE IDoptionpayments=44[highlight #FCE94F] [/highlight]" & _
    "ORDER BY IDoptionpayments; "[s][highlight #FCE94F] & _[/highlight][/s]
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

This looks weird when you select only the field you are filtering by. This is like select 44.

Edit:
I would also store "C:\Users\Paymentsinvoice1.docx" and any other data in a table rather than in your code.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

This seems to change the query, qry_landownerpayments2. So it is now just a query showing one record being 44, as opposed to the original with 115 records

I want to simply update the query or filter the query so it just selects record 44. I wanted to see if it would work with 44

Ideally it needs to be [forms]![frm_name]![ID field], so it will be dependant on which form I am in.

I have now got the bookmarks working even when the fields are blank.

 

[dhookom]

Did you try change the code to reference the control on the form?

Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & [forms]![frm_name]![ID field] & " & _
    "ORDER BY IDoptionpayments; " & _
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

If the code is running in [frm_Name] you can use:

Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & Me.[ID field] & " & _
    "ORDER BY IDoptionpayments; " & _
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

If the query returns only a single record I'm not sure why you need an ORDER BY.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

I am getting the following error message

Syntax error missing operator in query expression IDoptionpayments = 39 &'

It then opens up the merge documents showing all records. The query has been amended, and now shows only the IDoptionpayments merge field in the word documents and all the other merge fields, as part of the query come up as errors.

If I then try and merge from access again I am still getting the issue with data link properties box, unless I close the database and then open up again.

Dim strSQL As String
Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & [Forms]![frm_ladnownerpayments]![IDoptionpayments] & " &" _
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

 

[dhookom]

Try this:

Dim strSQL As String
Dim strOldSQL As String
strSQL = "SELECT IDoptionpayments FROM TBL_owneroptionpayments " & _
    "WHERE IDoptionpayments=" & [Forms]![frm_ladnownerpayments]![IDoptionpayments] 
strOldSQL = fChangeSQL("qry_landownerpayments2", strSQL)

Of course there is only one field which is why I said it looks weird when you select only the field you are filtering by.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Thanks for your patience Dhookham.

I seem to be making progress. This is now merging, and the letter appears although as we know the only merge field that appears is IDoptionpayments, and for the letter to work I will need to include about 20 fields.

The code below changes the query ("qry_landownerpayments2) so it only includes one field IDoptionpayments.

Either I;
1) Amend the SQL query code above to include the 20 fields, but I don’t have the programming ability to do that, I gave it a go, by converting the Access query to SQL, but I keep on getting errors, and the query involves several tables.

2) Is it possible to amend the query or update the query? As opposed to fChangeSQL, which changes the query. This way it would simply amend the query "WHERE IDoptionpayments=" & [Forms]![frm_ladnownerpayments]![IDoptionpayments], and keep the 20 fields as part of the query.

Thanks

 

[dhookom]

Can you provide the SQL view of a query containing the 20 fields and required tables?

The other option is to add qry_landownerpayments2 to your query with 20 fields and join the IDoptionpayments fields. When your code changes qry_landownerpayments2 it will apply a filter to the query with 20 fields.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

See below, I have attached the code.

I do have a few other letters to produce and I suspect the second option may be easier to manage, but not sure how to code this.

I have attached the SQL regarding the first option.

Thanks

SELECT TBL_Owner.OwnerID, TBL_owneroptionpayments.IDoptionpayments, TBL_owneroptionpayments.optionpaymenttype, TBL_owneroptionpayments.optiondatepayment, TBL_owneroptionpayments.indexdatestartpayment, TBL_owneroptionpayments.indexnowpayment, [indexnowpayment]/[indexdatestartpayment] AS indexlevelpayment, TBL_owneroptionpayments.optionpaymentamount, [optionpaymentamount]*[indexlevelpayment] AS optionpaymentplusindex, ([optionpaymentamount]*[gst])*[indexlevelpayment] AS gstamount, TBL_owneroptionpayments.gst, [optionpaymentplusindex]+[gstamount] AS optionpaymenttotal, TBL_owneroptionpayments.optionpaymenttotal AS totalmerge, TBL_owneroptionpayments.optionpaymentstatus, TBL_owneroptionpayments.optiondatepaid, TBL_owneroptionpayments.optionpaymentcomment, TBL_owneroptionpayments.OwnerID, TBL_owneroptionpayments.gstpayable, Contacts.[First Name], Contacts.[Last Name], Contacts.[Business Phone], Contacts.[Mobile Phone], TBL_Owner.ownernameontitle, TBL_Owner.abn, TBL_Owner.bsb, TBL_Owner.accountnum, TBL_Owner.bank, TBLRecordcontrol.Sitename, TBLRecordcontrol.companynum, TBLRecordcontrol.ID, TBLRecordcontrol.developerid, TBL_Owner.banknameonaccount
FROM TBLRecordcontrol INNER JOIN ((TBL_Owner INNER JOIN Contacts ON TBL_Owner.contactsID = Contacts.contactsID) INNER JOIN TBL_owneroptionpayments ON TBL_Owner.OwnerID = TBL_owneroptionpayments.OwnerID) ON TBLRecordcontrol.ID = TBL_Owner.ID;

 

[dhookom]

Create a new query for testing and paste this SQL into the SQL view to see if the correct record(s) are shown. This query should be filtered by the [Forms]![frm_ladnownerpayments]![IDoptionpayments]. The code must be run to change the SQL of qry_landownerpayments2.

SELECT TBL_Owner.OwnerID, TBL_owneroptionpayments.IDoptionpayments,
TBL_owneroptionpayments.optionpaymenttype, TBL_owneroptionpayments.optiondatepayment,
TBL_owneroptionpayments.indexdatestartpayment, TBL_owneroptionpayments.indexnowpayment, 
[indexnowpayment]/[indexdatestartpayment] AS indexlevelpayment, 
TBL_owneroptionpayments.optionpaymentamount, 
[optionpaymentamount]*[indexlevelpayment] AS optionpaymentplusindex, 
([optionpaymentamount]*[gst])*[indexlevelpayment] AS gstamount, 
TBL_owneroptionpayments.gst, [optionpaymentplusindex]+[gstamount] AS optionpaymenttotal, 
TBL_owneroptionpayments.optionpaymenttotal AS totalmerge, 
TBL_owneroptionpayments.optionpaymentstatus, TBL_owneroptionpayments.optiondatepaid, 
TBL_owneroptionpayments.optionpaymentcomment, TBL_owneroptionpayments.OwnerID, 
TBL_owneroptionpayments.gstpayable, Contacts.[First Name], Contacts.[Last Name], 
Contacts.[Business Phone], Contacts.[Mobile Phone], TBL_Owner.ownernameontitle, 
TBL_Owner.abn, TBL_Owner.bsb, TBL_Owner.accountnum, TBL_Owner.bank, TBLRecordcontrol.Sitename, 
TBLRecordcontrol.companynum, TBLRecordcontrol.ID, TBLRecordcontrol.developerid, 
TBL_Owner.banknameonaccount
FROM TBLRecordcontrol 
INNER JOIN ((TBL_Owner 
INNER JOIN Contacts ON TBL_Owner.contactsID = Contacts.contactsID) 
INNER JOIN TBL_owneroptionpayments ON TBL_Owner.OwnerID = TBL_owneroptionpayments.OwnerID) 
ON TBLRecordcontrol.ID = TBL_Owner.ID
WHERE TBL_owneroptionpayments.IDoptionpayments IN 
(SELECT IDoptionpayments FROM qry_landownerpayments2);

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dougjack10]

Excellent, that does the job thank you very much.

One last thing and final request. I am trying to automatically save the merge file,

I was able to do this with excel. Something along the lines of.

ActiveWorkbook.SaveAs fileName:= _
"\\Agfgsdnewfs01\letter.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

However I want the file name to be a mixture of text and fields from the database.

So it would be Me.Sitename "_" Me.ID "_" Initial letter.docx