Query as data source for mail merge

I have a query named QryDept where one of the fields is DeptID. The query is
used for the data source of a mail merge letter. I would like to control
which department is to get the mail merge letters. I have a department
selection pop-up form where the departments are listed in a listbox. The
value of the listbox is DepartmentID. On the form is a Create Mail Merge
button which is coded to open the mail merge and make the pop-up form not
visible. In QryDept I set the criteria for DeptID as
Forms!MyPopupForm!DepartmentID. When I go to Word to create the main
document for the mail merge, it won't let me set the data source as QryDept.
If I remove the criteria in the DeptID field, it will then let me set the
data source as QryDept but if I run the mail merge that way I get letters to
all departments. If I go back to QryDept and enter the criteria after making
it the data source, I get an error message when trying to run the mail
merge.

Has any one else experienced this problem? How can I set the criteria in the
data source query for a mail merge?

Thanks!

Lisa

re: Query as data source for mail merge

Well, you could download my mail merge program. Remove all criteria from the
query.

Then, place a button in your popup form that lets the user select the
department.

Then, the button code on that popup form to merge would go:

dim strSql as string

strSql = "select * from qryDept where DeptID = " & me!DepartmentID
MergeAllWord strSql

That is all you need!

You can try downloading my sample mail merge code and system here:
http://www.attcanada.net/~kallal.msn.../msaccess.html

Give the download a try..and see if you like it. If the working sample is to
your lining, then you can read the notes on how to use this merge in your
application. Once my code system is in place, you can see that you only need
about 3 lines of code to do the whole mail merge...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn