Results 1 to 11 of 11
  1. #1
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8

    General Merge query to word template Module stopped working

    I have inherited a very involved access database which requires the merging of queries to existing Word documents. There are several forms which have the VBA calling the dmerge command. Each form defines the specific query and specific word document to merge. The General Module to do this is called "dmerge". This seems to have stopped working. Simply stated, dmerge calls up a string query, then mail merges it to an existing string word document template, then closes the query. I keep receiving a '91 error on the line "If rst.EOF = True Then" . The general module code which gives the error is below. Creating reports is not an option because this database has over 25 Word Templates it used to merge to in Access 2013 depending on which form calls up the dmerge. Thank you if anyone is able to suggest a VBA repair to get the run query, send to word document general module below working. I am an intermediate user.



    Code:
    Public Function dmerge(StrTemplate As String, StrQuery As String, strFolder As String)DoCmd.Hourglass True
    'pubCurrDBPath is a variable located in the GenMods module
    'it is normally primed when db is opened via macro
    'AutoExec(Runcode OpenProcs) by a call to genCurrDBPath
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    
    
    'pubTemplateFolder is a constant located in the GenMods module
    StrTemplate = pubTemplateFolder & StrTemplate
    If StrQuery = "none" Then GoTo SkipQuery
    'Run query
    'exit function if recordset is empty
    If rst.EOF = True Then
        MsgBox "No data to merge.", vbInformation, "Mail Merge"
        rst.Close
        GoTo Exit_Here
    End If
    rst.Close
    qdf.Close
    SkipQuery:
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Set wApp = New Word.Application
    Set rst = CurrentDb.OpenRecordset("StrQuery")
    On Error GoTo dMergeError
    ' open word merge template document
    Set wDoc = wApp.Documents.Open(StrTemplate)
    'Dim strSampleFile
    'strSampleFile = pubCurrDBPath & "Sample.doc"
    'if there is no query associated with the passed strTemplate
    'then goto procedure exit
    If StrQuery <> "none" Then GoTo DodMerge
    'StrQuery "none" means that the document is a blank
    'form with no merge required
    'this copies the template document and pastes it to a
    'new open document
    wDoc.Select
    wApp.Selection.Copy
    'close the templates document
    wDoc.Close (wdDoNotSaveChanges)
    'Documents.Add DocumentType:=wdNewBlankDocument
    wApp.Documents.Add DocumentType:=wdNewBlankDocument
    wApp.Selection.Paste
    GoTo FinishUpDoc
    'merge processing
    DodMerge:
    On Error GoTo dMergeError
    With wDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .SuppressBlankLines = True
    .Destination = wdSendToNewDocument
    .OpenDataSource Name:=StrQuery, Format:=wdOpenFormatDocument, LinkToSource:=False, Connection:="strQuery", subtype:=wdMergeSubTypeWord
    .Execute
    End With
    'close the merge 'template document' without saving
    wDoc.Close (wdDoNotSaveChanges)
    
    
    GoTo FinishdMerge
        
    End With
    On Error GoTo dMergeError
    'paste the contents of the clipboard over the current selected text
    wApp.Selection.Paste
    '2/25/05 ME:  add the following statement-see if it will give another case
    On Error GoTo dMergeError
    
    
    dMergeError:
    DoCmd.Hourglass False
    Select Case Err.Number
        Case 3265
            MsgBox ("Query named " & StrQuery & " cannot be located.")
        Case 5174
            MsgBox ("Word doc named " & StrTemplate & " cannot be located.")
            ActiveDocument.Close
        Case 5922
            MsgBox ("Data transferred to Word cannot contain quotes!")
                ActiveDocument.Close SaveChanges:=wdPromptToSaveChanges, OriginalFormat:=wdPromptUser
        Case Else
            MsgBox ("error # " & Err.Number & Err.Description)
    End Select
    Resume Exit_Here
    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You have declared the recordset but never opened it. If it worked before, perhaps a few lines got deleted by mistake.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8
    Thank you Paul. I thought the following code opened the recordset:

    Code:
    Set rst = CurrentDb.OpenRecordset("StrQuery")
    I now receive a compile error on the "End With" code line. Am I not closing the sub possibly? This general module is supposed to send the query results to merge to the specific word document called up by each form's vba code. For example, there is a form in the Access Database called fcomplain01 and has the following VBA code to merge the complaint letter.

    Code:
      Case "ComplaintLtr"        Dim MergeQuery As String
            Dim WordDoc As String
            Dim SaveFolder As String
            'worddoc = "ComplaintLtr"
            'ME:  7/11/03 - change to the following after testing
            WordDoc = "ComplaintReferral"
            MergeQuery = "mqcmplaint"
            SaveFolder = "AQC"
            Call dmerge(WordDoc, MergeQuery, SaveFolder)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That does open the recordset, but that line is after the line that throws the error, so it hasn't been executed yet at that point.

    You have 1 "With" but 2 "End With".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8
    Thank you again Paul for the guidance. I have cleaned up the code quite a bit, but I am still receiving an error 91 code "Object Variable or With Block variable not set at
    Code:
    Set qdf = dbs.QueryDefs(StrQuery)
    in the code below. This is a public function since one of queries could be pulled from a form. I am so close to solving this puzzle, Word even tries to open now to push the query to Word. See code below. I am getting lost in the weeds I think, but I am sure the missing code is close, maybe even just one line away.

    Code:
    Public Function dmerge(StrTemplate As String, StrQuery As String, strFolder As String)DoCmd.Hourglass True
    'pubCurrDBPath is a variable located in the GenMods module
    'it is normally primed when db is opened via macro
    'AutoExec(Runcode OpenProcs) by a call to genCurrDBPath
    If pubCurrDBPath = "" Then Call genCurrDBPath
    'pubTemplateFolder is a constant located in the GenMods module
    StrTemplate = pubTemplateFolder & StrTemplate
    Dim dbs As DAO.Database, rs As DAO.Recordset, qdf As DAO.QueryDef
    Dim wApp As Word.Application
    Dim AppWord As Object
    Dim wDoc As Word.Document
    Set wApp = New Word.Application
    ' open word merge template document
    Set wDoc = wApp.Documents.Open(StrTemplate)
    ' Make Word visible.
    wApp.Visible = True
    Set qdf = dbs.QueryDefs(StrQuery)
    Set rs = CurrentDb.OpenRecordset("StrQuery")
    ' Set the mail merge data source as the StrQuery.
    wDoc.MailMerge.OpenDataSource _
    Name:=CurrentProject.FullName, _
    LinkToSource:=True, _
    Connection:="QUERY StrQuery", _
    SQLStatement:="SELECT * FROM [StrQuery]"
    ' Execute the mail merge.
    wDoc.MailMerge.Execute
    wDoc.Close False
    wApp.Quit
         
    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing
    End Function

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Same issue, but this time you've declared dbs but not set it. I suspect a chunk of code got accidentally deleted. You might go to a working backup and grab the code from there to make sure you get it all.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8
    Thank you. I will go to a previous backup and check.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, good luck. Post back if you run into more issues.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8
    Good morning,

    I found quite a few changes in the General Module in an older version of the document which calls up query to be sent to Excel then transferred to one o 27 existing Word documents...quite cumbersome. I searched the VBA of the Access 2016 database to see how many times the Public Function for merging a query is used. Only five form use this cumbersome vba for populating various company complaint forms. I think I would prefer to modify the forms instead to contain the merge code. With that said, I am hoping someone can offer a simple way to replace the call dMerge with a merge code which sends the query directly to Word. Below is the form code I would like to modify. Any sample run query and merge to Word VBA available? six word templates are available for the database user to select to merge...

    Code:
    Private Sub lstLetters_Click()
    'added 6/20
    Dim DocSource As String
        DocSource = "cmp"
    Dim WordDoc1 As String
    Dim strWordComplaint
    Dim strLetterType
    Dim GotIt
    If IsNull(Forms![fComplain01]![fComplainSetup01].Form![Proj_ID]) Then
        MsgBox ("Project # is needed to open Word document!")
        Exit Sub
    End If
    'develops a Word document name from the Project # and Document Choice Selected
    WordDoc1 = lstLetters.Column(0)
    Select Case WordDoc1
        'Case "ComplaintLtr"
         '   strLetterType = "R"
        Case "ComplaintReferral"
            strLetterType = "R"
        'Case "ComplaintOriginate"
            'strLetterType = "C"
        Case "PhotoComplaint"
            strLetterType = "P"
        Case "ComplaintGeneral"
            strLetterType = "C"
        Case "ComplaintInit"
            strLetterType = "I"
       'Added for Testing ME 7/21/03
        Case "ComplaintGeneral_One"
            strLetterType = "C"
        Case Else
            strLetterType = "O"
       
    End Select
    Dim finddoc
    strWordComplaint = (Forms![fComplain01]![fComplainSetup01].Form![Proj_ID]) & strLetterType & ".doc"
    finddoc = strWordComplaint
    'MsgBox (strWordComplaint & " is your document name")
    '==> we need the following
    'procedure to locate and open Word Complaint document
    Dim Response
    'MsgBox ("This looks for an Original Complaint Document")
    Call GetWordComplaint(strWordComplaint, Response, GotIt, DocSource)
    If Response = vbNo Then Exit Sub
    SetUpMerge:
    Dim MergeQuery As String
    Dim WordDoc As String
    Dim SaveFolder As String
    WordDoc = lstLetters.Column(0)
    MergeQuery = lstLetters.Column(1)
    SaveFolder = lstLetters.Column(2)
    DoTheMerge:
    Call dmerge(WordDoc, MergeQuery, SaveFolder)
    Exit Sub
    End Sub

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    have you considered Albert K.'s super easy word merge?

    http://www.kallal.ca/msaccess/msaccess.html

  11. #11
    tallen is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    8
    Thank you! I used this many years ago in 2003. I checked your link and see it is updated for today's dbase versions. I will research and if I have issues, I will repost.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-14-2020, 11:18 AM
  2. Replies: 3
    Last Post: 07-08-2019, 04:23 PM
  3. Replies: 1
    Last Post: 10-25-2015, 12:02 PM
  4. Replies: 6
    Last Post: 08-13-2014, 10:32 PM
  5. Replies: 1
    Last Post: 05-20-2011, 07:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums