Access Query merge into Word is not working in Access 2016

**tallen** · 07-04-2019, 04:25 PM

I am not a programmer. With this said, our database was recently upgraded to 2016. I am getting errors on one of our forms which uses a query to merge to one of any 10 word merge documents. The two errors I am receiving are error 91 on the With oWdoc.MailMergee line and the "expect a name error on the mailmerge section. Can anyone help me. I have spent three days researching this and found a thread on this forum similar, but do not know how to apply it to my specific form. I am thinking I am missing a line or similar. I have included all code associated with this form in this post. The form is in Access 2016. I am disheartened by this not being able to get the merge to work. Within my database, I am using an Access Query which is used to fill out several word merge documents. The query is called "mqpinsp". Have I done something as simple as left out a bracket or quotations?

The errors occur once I call on the mail merge. An error 91 occurs and an error indicating a name is expected occurs. Both errors are in the following section of the code,

Code:

With oWdoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.FullName, _
            ReadOnly:=True, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="QUERY mqpinsp",
            SQLStatement:="SELECT * FROM [mqpinsp]        ' Change the table name or your query"
        .Destination = wdSendToNewDocument
        .Execute Pause:=False
    End With

The only code on this one form is this:

Code:

Option Compare DatabaseOption Explicit


Private Sub cmdQUIT_Click()
DoCmd.Close acForm, "fLettersPInsp"
End Sub


Private Sub lstLetters_Click()
Dim strWordDoc  As String


    'Path to the word document of the Mail Merge
    '###-1 CHANGE THE FOLLOWING LINE TO POINT TO YOUR DOCUMENT!!
    strWordDoc = "H:\USERS\Templates" & "\" & "lstLetters.Column(0)"


Dim MergeQuery As String
MergeQuery = lstLetters.Column(1)


    ' Call the code to merge the latest info
    startMerge strWordDoc


End Sub




'----------------------------------------------------
' Auto Mail Merge With VBA and Access (Early Binding)
'----------------------------------------------------
' NOTE: To use this code, you must reference
' The Microsoft Word 14.0 (or current version)
' Object Library by clicking menu Tools > References
' Check the box for:
' Microsoft Word 14.0 Object Library in Word 2010
' Microsoft Word 15.0 Object Library in Word 2013
' Click OK
'----------------------------------------------------
Function startMerge(strDocPath As String)
    Dim oWord           As Word.Application
    Dim oWdoc           As Word.Document
    Dim wdInputName     As String
    Dim wdOutputName    As String
    Dim outFileName     As String


    ' Set Template Path
    wdInputName = strDocPath            ' was CurrentProject.Path & "\mail_merge.docx"


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


    ' Output File Path w/outFileName
    wdOutputName = CurrentProject.Path & "\" & outFileName


    Set oWord = New Word.Application
    Set oWdoc = oWord.Documents.Open(wdInputName)


    ' Start mail merge


    With oWdoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.FullName, _
            ReadOnly:=True, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="QUERY mqpinsp",
            SQLStatement:="SELECT * FROM [mqpinsp]        ' Change the table name or your query"
        .Destination = 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
    ' ###-3 IF YOU DON'T WANT TO SAVE AS A NEW NAME, COMMENT OUT NEXT LINE
    oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16


    ' SHOW THE DOCUMENT
    oWord.Visible = True


    ' Close the template file
    If oWord.Documents(1).FullName = strDocPath Then
        oWord.Documents(1).Close savechanges:=False
    ElseIf oWord.Documents(2).FullName = strDocPath Then
        oWord.Documents(2).Close savechanges:=False
    Else
        MsgBox "Well, this should never happen! Only expected two documents to be open"
    End If


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

**moke123** · 07-05-2019, 05:20 AM

Not sure if its a copy and paste error but Option Explicit should be on its own line.

also - SQLStatement:="SELECT * FROM [mqpinsp] ' Change the table name or your query" is missing the closing quote after [mqpinsp].

**tallen** · 07-08-2019, 08:53 AM

Thank you. I made the correction, but now I went back to receiving a Compile error for Syntax on the Open.Document command line area. Could this be because the Access 2016 database has VBA programming vs. SQL?

The subform has 5 Word Merge documents. The user picks one of these Word Docs and the Code should tell the database to use the query [mqpinsp] to merge the document clicked on for the current facility record selected.

Code:

Option Compare Database
Option Explicit


Private Sub cmdQUIT_Click()
DoCmd.Close acForm, "fLettersPInsp"
End Sub


Private Sub lstLetters_Click()
Dim strWordDoc  As String


    'Path to the word document of the Mail Merge
    '###-1 CHANGE THE FOLLOWING LINE TO POINT TO YOUR DOCUMENT!!
    strWordDoc = "H:\USERS\Templates" & "\" & "lstLetters.Column(0)"


Dim MergeQuery As String
MergeQuery = lstLetters.Column(1)


    ' Call the code to merge the latest info
    startMerge strWordDoc


End Sub




'----------------------------------------------------
' Auto Mail Merge With VBA and Access (Early Binding)
'----------------------------------------------------
' NOTE: To use this code, you must reference
' The Microsoft Word 14.0 (or current version)
' Object Library by clicking menu Tools > References
' Check the box for:
' Microsoft Word 14.0 Object Library in Word 2010
' Microsoft Word 15.0 Object Library in Word 2013
' Click OK
'----------------------------------------------------
Function startMerge(strDocPath As String)
    Dim oWord           As Word.Application
    Dim oWdoc           As Word.Document
    Dim wdInputName     As String
    Dim wdOutputName    As String
    Dim outFileName     As String


    ' Set Template Path
    wdInputName = strDocPath            ' was CurrentProject.Path & "\mail_merge.docx"


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


    ' Output File Path w/outFileName
    wdOutputName = CurrentProject.Path & "\" & outFileName


    Set oWord = New Word.Application
    Set oWdoc = oWord.Documents.Open(wdInputName)


    ' Start mail merge


    '###-2 CHANGE THE SQLSTATEMENT AS NEEDED
    With oWdoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
            Name:=CurrentProject.strWordDoc, _
            ReadOnly:=True, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="QUERY mqpinsp",
            SQLStatement:="SELECT * FROM [mqpinsp]"        ' Change the table name or your query"
        .Destination = 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
    ' ###-3 IF YOU DON'T WANT TO SAVE AS A NEW NAME, COMMENT OUT NEXT LINE
    oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16


    ' SHOW THE DOCUMENT
    oWord.Visible = True


    ' Close the template file
    If oWord.Documents(1).FullName = strDocPath Then
        oWord.Documents(1).Close savechanges:=False
    ElseIf oWord.Documents(2).FullName = strDocPath Then
        oWord.Documents(2).Close savechanges:=False
    Else
        MsgBox "Well, this should never happen! Only expected two documents to be open"
    End If


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

**moke123** · 07-08-2019, 04:23 PM

Code:

strWordDoc = "H:\USERS\Templates" & "\" & "lstLetters.Column(0)"

this line looks wrong and should probably be something like

Code:

strWordDoc = "H:\USERS\Templates\"  & Me.lstLetters.Column(0)

You should add a debug line right after the above line

Code:

Debug.Print strWordDoc

This way you can be sure of what it resolves to and is a valid path.

To be honest I'm not overly knowledgable with Word Vba. I always use Albert Kallal's Super Easy Word Merge, as it is easily integrated in any Db and is easily modified.

You can google Alberts code. Its everywhere.
I recently posted an example of what I use in this thread Post #7 ...https://www.accessforums.net/showthr...882#post431882

Access Query merge into Word is not working in Access 2016

Thread Tools

Access Query merge into Word is not working in Access 2016

Similar Threads

Error while connecting a Word Mail Merge to an Access query

Query With Join For VBA Mail Merge, Access 2016

Access 2010 DB, Word 2013 Mail Merge not working. Works with Word 2010.

Access Query to Word Merge

Access Query mail merge to Word

Posting Permissions