Collating Report Output

[Bhujanga]

I'm trying to print out billings with cover letters to about 800 customers. I can't figure out how to include the cover letter in the billing report, because each customer can have several pages of line items in the bill and there have to headings on each page. I don't want these headings to appear on the cover letter so I can't seem to include the cover letter as a 'customer header' which is what I thought I might be able to do. If I print the cover letters separately then someone has collate the bills and the letters by hand. Is there some way to combine it so it's pre-collated.

Thanks

[puppydogbuddy]

I'm trying to print out billings with cover letters to about 800 customers. I can't figure out how to include the cover letter in the billing report, because each customer can have several pages of line items in the bill and there have to headings on each page. I don't want these headings to appear on the cover letter so I can't seem to include the cover letter as a 'customer header' which is what I thought I might be able to do. If I print the cover letters separately then someone has collate the bills and the letters by hand. Is there some way to combine it so it's pre-collated.

Thanks

See this link for a "how to" collate 2 reports in ms access:

http://support.microsoft.com/kb/292461

[Bhujanga]

See this link for a "how to" collate 2 reports in ms access:

http://support.microsoft.com/kb/292461

Thanks for replying to my question. I studied the code and it appeared to me that the routine collates by actually alternating one page from one report and then one page from the other report. This won't work for my situation, because sometimes one of the reports will have multiple pages for one customer, and need each customers cover to appear before their potentially multi-page bill report. So I need to make the collation switch at customer break headers rather than each page. Do you have any ideas for that case?

[puppydogbuddy]

Thanks for replying to my question. I studied the code and it appeared to me that the routine collates by actually alternating one page from one report and then one page from the other report. This won't work for my situation, because sometimes one of the reports will have multiple pages for one customer, and need each customers cover to appear before their potentially multi-page bill report. So I need to make the collation switch at customer break headers rather than each page. Do you have any ideas for that case?

Your interpretation is not correct. See this comment line from the code:

'NumPages is the number of pages in the largest report

Try the code first, then let me know if it is not working.

[puppydogbuddy]

PS: If you have page breaks set up by customer in the report, they will be printed with the breaks the same way.

[Bhujanga]

PS: If you have page breaks set up by customer in the report, they will be printed with the breaks the same way.

I must be doing something fundamentally wrong. I put the first part of the code ver batim at the top of what I believe is considered the "Module", i.e. the first lines of the code window for the form on which I'll put the command button.

Then I took the other line of code, replacing its report names with mine. VB doesn't want to accept this. It is expecting an = sign. So I haven't been succesful testing it. I fear my programming knowledge base is a bit lacking.

[puppydogbuddy]

I must be doing something fundamentally wrong. I put the first part of the code ver batim at the top of what I believe is considered the "Module", i.e. the first lines of the code window for the form on which I'll put the command button.

Then I took the other line of code, replacing its report names with mine. VB doesn't want to accept this. It is expecting an = sign. So I haven't been succesful testing it. I fear my programming knowledge base is a bit lacking.

No, the module referred to in the link does not mean form module. It refers to a standard module that you can create by going to the database window and clicking the module object, which is the object that follows macros in the db window. You will "call" the module from the button on your form.

[Bhujanga]

Ok, I created a new module and put the function code in there ver batim.
Then on my form, I created a button, and in the click event code I attempted to put the following code in:
CollateReports(1, "Annual_Billing_CoverLetter", "AnnualBilling")
It protests immediately, saying that it is expecting an '=' sign.
Am I missing a step somewhere?

[puppydogbuddy]

Ok, I created a new module and put the function code in there ver batim.
Then on my form, I created a button, and in the click event code I attempted to put the following code in:
CollateReports(1, "Annual_Billing_CoverLetter", "AnnualBilling")
It protests immediately, saying that it is expecting an '=' sign.
Am I missing a step somewhere?

From what I can tell from the instructions, 'NumPages is the number of pages in the largest report which is used to determine the number of loops through the code. So, try changing the 1 you entered below to the number of pages in your largest report....So if your annual billing report is for 80 customers, 1 page per customer, put 80. This should cycle the code for your two reports 80 times, one for each customer. Also, make sure that your function is designated as a Public Function in the standard module. If this doesn't fix it, then please post the function code you have in the standard module.

Change this:
CollateReports(1, "Annual_Billing_CoverLetter", "AnnualBilling")

to this:
CollateReports(80, "Annual_Billing_CoverLetter", "AnnualBilling")

[Bhujanga]

I appreciate all your help on this. I still can't get the VB editor to accept that statement. It keeps wanting an equal sign somewhere. In any case I've had to abandon this particular detail for the time being in the interest of not letting the project fall behind. If I have time later I might revisit it. For now they're going to have to collate by hand. Thanks again.

[puppydogbuddy]

I appreciate all your help on this. I still can't get the VB editor to accept that statement. It keeps wanting an equal sign somewhere. In any case I've had to abandon this particular detail for the time being in the interest of not letting the project fall behind. If I have time later I might revisit it. For now they're going to have to collate by hand. Thanks again.

This link should help if you decide to pursue. The link is to a free download of Microsoft sample reports for Access 2000, which includes a sample collated report. It should be relatively easy for you to compare the sample with what you coded and figure out what the problem is.

http://support.microsoft.com/kb/231851/EN-US/

[Bhujanga]

Thanks, I'll check it out.