Union query for mail merge

[tabitha]

Does anyone know if it's possible to use a union query as a mail merge? I haven't found anything that says I can't do it, but I'm not getting my merge to complete, and when I switch to a plain query (and not changing anything else) my merge is successful, so I'm thinking there might be a limitation.

Any thoughts?

 

[vbaInet]

Try creating a SELECT query based on your UNION query and use that in your mail merge.

 

[tabitha]

This is my code:

Function Turning64MailMerge()

    Dim oMainDoc As Word.Document
    Dim oEnvDoc As Word.Document
    Dim oChartDoc As Word.Document
    Dim oSel As Word.Selection
    Dim sDBPath As String
    Dim oApp As Object
    
    Set oApp = CreateObject("Word.Application")
    Set oMainDoc = oApp.Documents.Open("V:\Database\Mail Merge Letters\Turning 64.docx")
    Set oEnvDoc = oApp.Documents.Open("V:\Database\Mail Merge Letters\Turning 64 Envelopes.docx")
    Set oChartDoc = oApp.Documents.Open("V:\Database\Mail Merge Letters\Turning 64 Chart.docx")
        oApp.Visible = True

    With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "C:\Test.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [Emps and Deps >64]"

    End With

    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    
    With oEnvDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "C:\Test.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [Emps and Deps >64]"

    End With

    With oEnvDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    
    With oChartDoc.MailMerge

        .MainDocumentType = wdFormLetters

        sDBPath = "C:\Test.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [Emps and Deps >64]"

    End With

    With oChartDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1


End Function

The FROM is my union query (I tried making a query that referenced the union query with no change) and if I change it to another standard query it runs perfectly.

 

[tabitha]

Oh, here's my symptom: I run the function from a macro and the three word docs appear, but a box pops up asking for the data source. I choose the union query, and a box from access says "Run-time error 5922: Word was unable to open the data source." When I debug the code, it breaks at .OpenDataSource and just crashes the program.

But if I open the document from Word itself, there are no errors, I can complete the merge successfully. So I'm at a loss at what I'm doing wrong...

 

[vbaInet]

How huge is your union query? And at what point does it crash in the code, the first merge?

 

[tabitha]

Yes, the first merge. It's not big at all: only 13 fields, which pulls only 2 records. I'm going to start today by trying the same query but only with the first Select statement (not a union query) and if that's successful, then maybe I'll just create two functions that are both run with one macro. If it's not successful, then I'll remove the two calculated fields I have and see if that works.

 

[vbaInet]

That probably won't be necessary just yet, try changing the way you reference the query:

        .OpenDataSource Name:=sDBPath, _
           LinkToSource:=True, _
           Connection:="[Emps and Deps >64]"

 

[tabitha]

I tried that, and it still pops up with a "Select a Table" box. It has queries on the list, but no union queries, or queries that reference a union.

 

[vbaInet]

Can you upload a cut-down version of your db?

* Delete any confidential data and replace that with some test data
* Delete any unnecessary forms, queries, reports, tables etc
* Debug > Compile your code
* Compact & Repair the db
* Zip and upload
... and I'll take a look.

NB: Please ensure that the scrubbed down version also fails on the same line and it includes a union query.

 

[tabitha]

Here is the database, free of everything but what's relevant. The people are all made up. Currently the function is bound to the union query.

 

[vbaInet]

In the db you attached, are you able to open the Union query without an errors? Please test.

 

[tabitha]

Yes, it opens when I click on it and has two records.

 

[vbaInet]

It errors on mine and that's causing the issue. I'll re-write it later. Can you explain what records it fetches.

 

[tabitha]

Maybe I wasn't quite clear enough: when I just run the query itself, it opens, to show "Lisa Glow" and "Mike Glow". When I run the "Turning 64 Mail Merge" macro, it errors and I get nothing.

 

[vbaInet]

I'm asking what the logic/rational is behind your union query?

 

[tabitha]

The query is very simple, it pulls any employee or dependent that is 64 and older, along with the employee's employer and address.

 

[vbaInet]

I need to get as much information as possible before I go looking at your db, that's why I ask. Couldn't you have derived the same results from a normal Select query? That's what I'm trying to understand.

 

[tabitha]

Oh you mean, why use a union query? I did it that way so that I would have a unique record for each employee and each dependent. Since the employees and dependents are on different tables, joined by the [Employee ID], when I do a query that just joins them, it puts the dependent NEXT to the employee, not under it. I don't think a mail merge would be able to give me a letter addressed to an employee with Address A and to a dependent with Address A, and to a dependent with Address B, but not to the corresponding employee. Does that makes sense?

 

[vbaInet]

Only just had time to look into this and I've re-written the process. Please note the comments within. See attached.

As for the dependants and customers conundrum, I haven't given that much thought but I think that there should be a way of re-structuring your tables so that you have the dependants and customers in one table. But from the looks of things you're already set-up so don't worry about that until next time you're considering re-building your db.