Office 2003.Word Mail Merge from Access Parameter Query. Possible?

Posted on 2006-05-25
Medium Priority
Last Modified: 2011-10-03

I use Office 2003 Pro. I wish to create a mail-merge letter in MS Word that gets its data from a parameter query in Access 2003. The query I wish to use, prompts users for a start membership numbe, End membership number and betwwen first and last date and brings up the data within the constraints of their input.

If I use the mail-merge wizard in MS Word to link to the database, it shows all the queries EXCEPT those that require user input for the parameters.

Is it not possible to create such a mail merge with parammeter input. So far I have had to create a normal query and change the criteria section in the query design. This is long a laborious and I often forget to do it before running the query

What am I doing wrong? Is it possible to do a mail-merge with a query that requires user input for its parameters and if so how as I have tried every option in the Word Mail Merge Wizard to no effect

Question by:arwood
LVL 65

Expert Comment

ID: 16759317
this is how u do it with Word 2000
perhaps this may help u
LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 16759432
Hi arwood,
The native connection from Word to Access in Office 2003 is different to previous Office versions and the technology which allowed the use of Access parameter queries in previous versions is quite well hidden in Office 2003 and pretty awful even when you get to it.

If you check the setting in Word
Tools|Options|General|"Confirm conversion at open"
you can get the mailmerge doc to ask the right Qs and you can choose the DDE route which is essential if you want to use a parameter query.
When you get to choose the Data source, ignore all the given options and just browse to your Access file and select it.  You will then be able to choose the dde option.
The real problem is that the Access parameter dialog does not come to the top and you have to switch to Access using the taskbar to see the parameter prompt.  This is wretched for users.

But the whole of mail-merge in Office 2002/3 is a mess IMHO.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 16759460
I agree - and was saying that very thing in a recent question Pete.
It could have been good, nice idea - but they dropped the ball on that one (or at least spooned it way over the net).

You can set it to do as much (i.e. use a DDE connection) in code
(my comment is riiight at the end).

I have to say though - my preference is to merge with a table.
It's just safer and generally faster.
All you need to do is execute a delete and then an append query first - and merge to that table.

(Of course - if you're not initiating the merge from Acces - then that ain't so straight forward)
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

ID: 16759640
Hi Rockiroads

Tried it and found what peter57r is saying  in his first para which I have just seen. Sorry it did not work  but many thanks for the exceptionally quick reply


Will now look at the replies from Peter57r and LPurvis

Author Comment

ID: 16760304
Hi Peter

Tried your solution and it works no problem with the proviso you give, ie it is very well hidden in the options and the kludge of having to select the parammeters in Access which are underneath so you are not aware they are there. After saving the final document however that does not occur so it is just a quirk that I can live with. I actually had several existing merge documents linked to Queries. I converted the queries to Parammeter queries and then found that the documents would not link any more. That was when I tried to change the source data and found that it would not list the Parammeter queries. I then tried to creat a new document with similar results. That was the point at which I consulted the experts. I would never have found that Option setting in a hundred years so thanks very much. All my previous documents are now up and running again but with the parammeter entry facility.

Thanks again and as the first one I allocate the points to you with thanks toLPurvis for his comments which I am not really able to comment on as VB is something I have never really gotten into.

Regards and thanks to all.

LVL 77

Expert Comment

ID: 16760403


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question