Query won't function as MailMerge DataSource

[silverback]

I made a posting about this problem here and got it going by adopting the solution of converting the query to a make table query. The link up was made successfully but I've since realised that the table solution isn't secure in a multi user environment so I'm trying to get the mailmerge to hook up to the query.

Hans suggested another method - Tools | Options | GeneralTab | check "Confirm conversion at Open"
I've looked - I promise I've looked - but I don't have a "Confirm conversion at Open" check box in my Tools | Options | GeneralTab
so I can't try this solution.

But what other suggestions are there for why the mailmerge document won't use the query as output but happily uses a table created by the query? The error is Too Few fields in Record 1 - essentially it thinks there are no fields in the query as following this error it goes through all the merge fields in the document asking whether I want to replace or delete them.
Alternatively, is there a way to use a table that works in a multi user environment - temporary table, create a unique table name every time query runs and delete when process finished, ........?
Thanks
Silverback

[HansV]

Are you sure that you looked at Tools |Options... in Word (not Access)?

x16.png

Alternatively, you could set up the mailmerge from Access using Automation; you can then construct an SQL string in code and specify that as data source instead of the name of a table or query. See WendellB's Managing Merges with Access.

[silverback]

Duh! OK, so I've set that in Word (not Access!) and have progressed to where I've got mailmerge working with automation as long as the details are picked from a table. I need to make this work with a query, though, if I'm to solve the multi user problem.
When I try to use the relevant query, the merge document is opened but a 'Select Table' dialogue box is displayed. I select Views as an option, and then select the query, but when I look back into Access, there is an Access error "Word is unable to open the data source".
I've attached a zipped up stripped down DB. The merge VBA code is not rigorous, so the items need to be unpacked into C:\Automation. It will open on the multiselect contract form; the query that delivers the rows matching the selected IDs is qryMultiContractData. (if form MultiContract is loaded and some selections made, running the query directly shows the returned records)
Can someone find out why linking to the query in automation is not working, please?
Thanks
Silverback

[HansV]

You were almost there. You only need to tell the mail merge to use the Word 2000 method of connecting to the data source:

Code: Select all

   objWord.MailMerge.OpenDataSource _
      Name:="C:\Automation\AutomationTest.mdb", _
      LinkToSource:=True, _
      Connection:="QUERY qryMultiContractData", _
      SQLstatement:="SELECT * FROM [qryMultiContractData]", _
      SubType:=wdMergeSubTypeWord2000

The SubType argument has been added.

[silverback]

As always, my grateful thanks for getting things going. It's working and automation is very quick indeed compared to my old method (running WordMailMerge in a macro).
One quirk : everything works fine and the merge is actually done, but when I close the main merge document, Access errors in the inSelect VBA function saying that it can't find the form MultiContract - which it can't, of course, since the form has been closed. But why is it looking for the form when the entire merge action has completed successfully?
I've stopped this happening by not closing the form (smart, eh?) but I'd like to know why the error is happening.
Thanks again
Silverback

[HansV]

My guess would be that Word evaluates the query one more time when the main merge document is closed. The query calls the function, and this fails if the form is no longer available. So the workaround is indeed to keep the form open.