Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
CHange query/datasource mail merge is bound to
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 :- Code:
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; 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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
CHange query/datasource mail merge is bound to
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 "Alex" wrote in message ... 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 :- Code:
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NO CODE - mail merge to Outlook with attachment with CC | Mailmerge | |||
Heavy Mail Merge Application | Mailmerge | |||
Change font for one field in mail merge | Mailmerge | |||
Why does mail merge change the numbers in excel | Mailmerge | |||
mail merge with word 2003 | New Users |