CHange query/datasource mail merge is bound to

[Alex]

Hi i have set up a mail merge document, and set the datasource to a query in
my access 97 database. This works fine. The problem is that i need to change
a parameter in the query, based on what record the user is currently looking
at. So i need to change the carNo = 1 part of my SQL :-

SELECT carNo, dateRaised, dateCARsent, e.fullName, b.supplierName,
c.materialCode, suppOrderNo, ProductionDate, DeliveryDate, originIsCustomer,
complaintNo, a.defectDesc, fsqOrderNo, holdNo, deliveryQty, f.defectDesc,
machineTimeLost, scrapIncurred, supplierResponse, suppResponse, briefDesc,
defectLocation, defectComments, setupOK, setupComments, sampleYN,
sampleComments, testMethod, results, graphAtc, spreadAtc, chkAgainstSpec,
specNo, specComments, conclusion
FROM tblCAR AS a, tblSupplier AS b, tblMaterial AS c, tblMaterialSupplier AS
d, tblUser AS e, tblDefect AS f, tblDefectLocation AS g
WHERE a.MaterialSupplierId=d.materialSupplierId
AND b.supplierId = d.supplierId
AND c.materialID = d.materialID
AND a.UserId = e.userId
AND a.defectId = f.defectId
AND a.defectLocId = g.defectLocId
AND CARNO = 1;

Can i change this is access? Or on the the other side of things is it better
to build the query in vb.net and pass that through to the document - but that
poses me the problem of how i set the query created in vb to be the
datasource of the document.

Any ideas, suggestions most welcome,

Cheers,
Alex

 

[Peter Jamieson]

In theory, you can issue SQL from vb.net by calling the
MailMerge.OpenDataSource method and providing the SQL in the SQLStatement
and SQLStatement1 parameters. IN some cases, you can also do it by setting
MailMerge.DataSource.QueryString to the SQL you need.

However, in practice you cannot do that in this case because the maximum
length of the combined parameters is 512 characters (for some connection
types and data sources it may be even shorter than that.

What I think you should be able to do in this case is set up a query (let's
call it "myquery" in Access containing everything except

"AND CARNO = 1"

then build the following SQL dynamically:

SELECT * FROM myquery WHERE carNo = 1

(or whatever)

and either issue an OpenDataSource call containing that SQL or, if you have
alwready set up the query to be the data source, set the value of
..QueryString to the SQL you need.

I can't check with an Access 97 database but that is what I would try. If
you need help on the other parameters of OpenDataSource, ask again and tell
us which version of Word you are using.

Peter Jamieson




e is an absolute maximum length of 512 characters for SQLStatement