Mail Merge query

[rookery]

I have a Form with a combo box on it. Once the user has made a selection you hit a button which launches \ generates a Mail Merge Word document. The document's source is the query "MailMerge".

The problem I'm having is that unless I refresh the Form by going from Design to Form view, before I want to launch the document, it asks me to enter a value for the parameter query. Why does it need refreshing each time? I tried putting in a "Form.Requery" command each time you clicked on the button but that didnt seem to work.

Also when it does launch successfully I'm getting the following message:

"The database has been placed in a state that prevents it from being opened or locked".

On these occassions the document is fine, but once I close it down again and try and go back to my database, it appears as though it has hung. This is the code behind the button:

Form.Requery
DocName = "C:\FolderName\Merge.doc"
retval = Shell("C:\Program Files\Microsoft Office\Office\WinWord.exe" & " " & DocName, vbNormalFocus)

How can I get round these problems? I tried searching thru' some of the older posts but there's so many on Mail Merges it's hard to find what you're looking for exactly.

 

[LittleSmudge]

I think I can see the root of your problem.

The Word doc is looking at the stored query ( which is closed ) and therefore won't be updated when Word opens )

I can't see a direct solution.

But I have to ask WHY are you working so hard ?

Why not just copy the Work document's format and layout into an Access Report and do the entire mail merge in Access ?


G LS

 

[rookery]

Smudge

Thanks for your reply. I have to admit that I asked myself the same question about 2 hours before your posting and went down the very road you suggest! Much easier!

However it does make you wonder how others have got this to work, as I have seen it done.

Cheers again.

 

[jebry]

Hi!

I usually use the query to create a table(which I first delete). Then I open the Word document where I had previous created a macro to run on opening to use the table I just created.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[LittleSmudge]

Yes Jeff I have travelled that road too.

If you make it a MakeTable query
SELECT fields INTO tablename FORM querySourceConstruct

Then it blows the old away automatically.


G LS

 

[jimmythegeek]

A third option is to drop the query and recreate it using QueryDef with dynamically created SQL. Then open your mail merge doc.

This method tends to "grow" your database slower than the Make Table method as every time you make a new table, Access expands to incorporate the space for the new data. The size of the db will continue to grow until you compact. However if the table is small, or you regularly compact the db, it is an easier option than the drop and create method. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[rookery]

Thanks guys for all your help on this. I'm off on holiday for 3 weeks as from tomorrow but looking forward to trying out all these other techniques on my return.