Search code examples
ms-accessvbams-wordmailmerge

Is there a way for end users to use the current filter set in Access as the datasource for a Word mailmerge?

One of our products is an Access database which includes a table listing thousands of addresses. Our product includes a user friendly interface for searching and filtering out the addresses. These are displayed on a form, let's call it frmAddress.

We include on frmAddress an Export To Excel button which takes the current filter set and exports it to Excel.

To use their filtered addresses as a datasource for a Word mailmerge, end users export to Excel first then used the Excel file as the datasource.

But this is rather a long way round. Is there a way to connect directly to Access and pull out the addresses (the current filter set in frmAddress, not the full table)? If it can't be done directly is there anything that comes close?

The nearest thing I can think of is to save the current filter set every time it changes as the CommandText of a dynamic query, but it seems to me that in Word 2003 Access Queries (unlike Tables) aren't reliably visible for mailmerge purposes.

Solution

  • If you download my code library here: http://www.kallal.ca/msaccess/msaccess.html

    (super easy word merge)

    Then, the code you need for the merge is:

    Dim strSql      as string

strSql = "select * from tblCustomers where " & me.Filter

MergeAllWord strSql