Hi everyone -- I am trying to do a mail merge to Word from access. I have made it work just fine using a table, with the following code:
Function MergeIT()
    Dim objWord As Word.Document
    Dim intAgency As Integer

    intAgency = Me!cmbAgency.Value

    Set objWord = GetObject("C:\Documents and Settings\Nora Brown\My Documents\mailMerge2.doc", "Word.Document")
    'Make Word Visible
    objWord.Application.Visible = True

    'Set the mail merge data source as the current database
    objWord.mailMerge.OpenDataSource _
    Name:=CurrentDb.Name, _
    LinkToSource:=True, _
    Connection:="TABLE AGENCIES", _
    SQLStatement:="SELECT * FROM [AGENCIES] WHERE agcyID=" & intAgency

    'Execute the mail merge
    objWord.mailMerge.Execute

End Function

However, I would actually like to draw info from 2 tables, and thus it seems I should base the mail merge on a query. How can I do this?
Thank you,
Nora Brown

You probably do need a query. Just go ahead and use query builder to create it. Then switch to SQL view to see the Select statement built by that query. Then replace the Select statement in your code.

As an alternative...

Set up the query such that the Criteria of agcyID has a unique value that will never appear elsewhere in the select statement, e.g. <Set Agency>.

Then in your code...

SQLStatement:=Replace(CurrentDB().QueryDefs("your_ query_name").SQL,"<Set Agency>",intAgency)

Then if you change the query, you don't have to change your code.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Dear RJWeers -
Thank you for the advice. However, my question is if I use multi-table select query as my SQL Statement, what do I put as the "Connection:" which currently says Connection:="TABLE AGENCIES", _?
Thank you,
Nora Brown

Have you tried without specifying the connection?

I get the impression from Access help that Connection is used if you are not specifying a SQLStatement.

If you specify "Table" it will just give you everything in the table that you name. If you specify "QUERY" it will give you everything from the query that you name. Apparently, assuming you're getting the correct data in your merge (as opposed to all agencies), the SQLStatement overrides the Connection property anyway.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org