Apologies, the following code

oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]"

should say

oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [q1]"

(and there obviously should not be a digit "4" jut in front of the WHERE in
the SQL clause used to set up q1).
Testing q1 implies testing or myrow()

Peter Jamieson

"l_stocky" wrote in message
...
Peter - many thanks for your help thus far.

I have managed to successfully replicate your example. However, I don't
see
that it tests the myrow() function, which is where the potential problems
may
lie.
Can you extend/adjust the code to test the myrow() function, and q1 which
is
redundant from what I can see in the example as it stands.

I appreciate you may have avoided this to test the simple functionality of
the mailmerge process and to illiminate the fact that something in my
configuration is causing the problem.
Short of the fact that a second instance of the database is loaded the
whole
process is performed properly.

I would really appreciate your further help on this,
Lloyd

"Peter Jamieson" wrote:

I tried this using a simple example here and managed to get it all to
work,
which suggests that something specific either in your function (or
perhaps
the query) or your configuration is causing a problem. The main visible
configuration difference was that I was using Win2K not WinXP, but I
doubt
if that is a factor.

Does your example work when you do not try to automate Word from the
database, i.e. if you just use Word VBA to set or change the data source
to
be the query you want? In other words, is the fact that you are
automating
Word from your daatabase a factor (here, it does not appear to make any
difference).

In my simple example, an Access 2000 .mdb called c:\a\a.mdb with a
two-column table t1 with columns k (an integer) and t (text), and a small
number of rows.
I have a query q1 which does
SELECT t1.*
FROM t1
4WHERE ([t1].[k] = myrow())

(There may be some more [] in there than I have written above).

I have a Word document c:\a\atest.doc that just contains mergefields k
and
t.

I have a module ("Module1" in the database containing
a. a function myrow() which simpy sets myrow equal to one of the values
in
k
b. a function mymerge which does

dim oApp As Word.Application
dim oDoc As Word.Document
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
Set oDoc = oApp.Documents.Open("c:\a\atest.doc")
oApp.Visible = True
oDoc.MailMerge.MainDocumentType = wdNotAMergeDocument
oDoc.MailMerge.MainDocumentType = wdFormLetters
oDoc.MailMerge.OpenDataSource _
Name:= "C:\a\a.mdb", _
Connection:="TABLE t1", _
SQLStatement:= "SELECT * FROM [t1]"
Debug.Print oDoc.MailMerge.DataSource.Connectstring
Debug.Print oDoc.MailMerge.DataSource.Querystring
oDoc.MailMerge.DataSource.Querystring = "SELECT * FROM [t1]"
Debug.Print oDoc.MailMerge.DataSource.Connectstring
Debug.Print oDoc.MailMerge.DataSource.Querystring

' followed by any Set oDoc = Nothing type cleanup required, but I leave
the
document open to check the results.

I wonder if a similar simple example works on your system? If so, it
would
suggest that there is something different about your database, table or
query. I do not think it can be the multi-user settings or it would still
fail when you remove the function from the query.

BTW, this may work in Word 2000 but automating anything that relies on
the
DDE connection method may fail in Word 2002 or later, and using
user-defined
functions does require you to use DDE.

Peter Jamieson
"l_stocky" wrote in message
...
This question relates to the Automation of Word Mail Merge from Access.
I
wasn't sure whether to post it here or on the Access Newsgroup.
versions: Word 2000, Access 2000, Windows XP Pro.

I have a Word Document. It's Datasource is set to a predefined query in
an
Access DB. The query has criteria which is defined by functions set at
realtime. i.e. the 'CaseID' column has 'getCaseID()' in its criteria.
This
will return an integer which will have the query select a single row of
data.
This query works correctly within Access and correctly when attempting
to
'Edit' the source from Word.

What I am attempting to do is automate the mail merge process from
Access.
This is where the problem shows itself. I can open the Word document
but
if I
try to set the datasource to the Access query I get an error message:
"Word
cannot open the datasource" and sometimes "Automation Error".
If I replace the functions in the query's criteria with "[]" (so the
criteria is manually set at runtime), Word opens the datasource
successfully
and I can execute the mail merge. If I set the criteria with
appropriate
data, again the mail merge process is successful using automation.

So it seems that Word can't open the datasource when directed to do so
by
automation where the datasource involves function based criteria.

I have put a breakpoint on the function and it is clear they are being
consulted and are being assigned correctly.

Is this a known problem? I see lots of aritcles about parametrised
queries
but none take into account setting parameters by function. (see
kb209976).

Is there a workaround?