Hi Brian,
Thanks for the reply.
I did think of using a view (This is an Access .MDB, by the way), but as the query contains a program variable, I couldn't see how to save it correctly. The actual SQL I ended up trying to use pulled from only two tables, but still the same issue arose, as follows:
"SELECT * FROM partybookings INNER JOIN customers ON partybookings.cust_ref = customers.cust_ref WHERE partybookings.party_ref = '" & pRef & "'"
(pRef is a class-level variable that holds a string like "071206RP")
Maybe I am just being dumb as regards how to store/use views in VB.
Also, I wanted to keep as much "intelligence" away from the reports as possible - The reason for this is that if I come back to this code in six months' time, I don't want to have to dig around all over the place to remember how the reports got generated.
Looking back at this I think I ended up going around in circles trying to achieve something that could have been done with a view and a dataset, but I didn't (and still don't) know how to implement the view.
Oh well....:-)
As for the mail-merge stuff, here's a really simple example....
1. Create a blank report
2. Insert a text object
3. Rename the text object, ie "toAddr1"
4. Format and position the text object as required
5. Note which section the text object appears in (I tend to rename them away from 'Section1', 'Section2' etc to more memorable strings like 'Header' and 'Body')
6. In code, populate a datareader with the record you want to merge into the template
7. Reference the objects in the section that the textobject is in via the ReportObjects collection (I use the renamed section 'Header' for this example):
dim txtobjs as ReportObjects = cr.ReportDefinition.Sections("Header").ReportObjects
8. Set the text you want to display. NB: As the ReportObjects collection returns objects, you need to cast to the correct type:
Ctype(txtObjs("toAddr1"), TextObject).Text = drdr.item("addr1").ToString
In this example, I use the addr1 database field as populated in the datareader
9. Print the report: cr.PrintToPrinter(1, true, 1, 1)
For a full mail-merge, all I did was to enclose the whole lot in a While .Read loop. That will print a page for each record in the reader.
For multi-page documents, I found that creating several new sections in the report and making each one approx 16380 in height gave a A4 page per section that can be formatted and populated with text objects in the same way as above. All that is needed then is to reference the sections in turn (as in step 7) and then refer to each textobject as required (as in step 8). Finally, remember to set the number of pages in the PrintToPrinter call to the same as the number of pages that the report generates.
Cheers
Graham
IP Logged
