same report twice on A4 paper with fixed size of the detail section

[cyrus05]

Hi All,
I have been searching for solution of my problem for days and have always
found partial soultions in pieces and could not get in to the desired result,
please help.
Here is the situation.
My client wants to print the same invoice on A4 paper twice on a pre-printed
format, one for customer and one to file.
I used a main report with two sub reports(clone of each other). The rows in
detail section are variable, can be 1, 5, 20 or more. Due to which the footer
data below the detail section is not printed as desired due to the number of
rows in detail section. I need you guys to advice me for;
1. How to set a fixed size for the detail section of report.
2. if the number of rows exceed certain number, invoice footer should not be
printed but the invoice shall continue on the next page and so on and after
the last row (record) in the detail, footer should be printed.

Now i know there are lot of solutions provided as using a sub report in
detail with CANGROW to YES and in main report's detail CAN GROW to NO. but
when i do that, my records are printed as many times as the numbers of rows
repeatedly.

Please provide urgent suggestions, I have to submit this report within 3
hours.

 

[Allen Browne]

1. Create a little table with 2 fields like this:
CopyID Number
CopyName Text
Mark the CopyID as primary key, and save the table with a name such as
tblCopy.

2. Enter 2 records in the table, like this:
CopyID CopyName
===== =========
0 Original
1 Duplicate
or whatever names you want to appear on your report.

3. Create a query using your invoice tables (typically 1 table for the
invoice header, and a related table for the invoice items, linked on an
InvoiceID field.) Add tblCopy as well. There should be no line joining
tblJoin to the invoice tables. (This will give you 2 copies of every row, so
you can get your duplicated report.)

4. Create the report based on this query.
In report design view, open the Sorting and Grouping pane.

5. On the first row of the Sorting'n'Grouping, choose the CopyID field.

6. On the second row of the Sorting'n'Grouping, choose the InvoiceID field.
This time, in the lower pane, set Yes for Group Header and Group Footer.
This adds an InvoiceID group header (above the Detail section), and an
InvoiceID group footer (below the Detail section.)

7. Select the InvoiceID group header, and in the Properties box, set the
Force New Page property to: Before Section. (This ensures every new invoice
starts on a new page.)

8. Increase the height of the InvoiceID Group Header section, so you can
place all the header fields in there: the invoice, date, and address panel.
Place the CopyName there also (assuming you want to print that.)

9. Add the line-item fields to the Detail section -- typically Quantity,
Item, PriceEach etc.

10. In the InvoiceID group footer, add a text box to show the total, and
others you need. Typically the Control Source for the total will look like
this:
=Sum([Amount])

The report will now print 2 copies of each invoice, each starting on a new
page.

 

[cyrus05 via AccessMonster.com]

Hi Allen!
Thank you very much for your detail explaination. I really appretiate it.
Sorry I couldnt come to you yesterday as I had to transmit the required
report to the customer.
My problem was not an invoice to be printed on a seperate page, but same
invoice to be printed on A4 size paper twice vertically with detail section
with fixed size. Here I have solved the problem.
As known, there are two tables for the invoice.
1. Invoice main table (holds customer ID, invoice number, amount, etc)
2. Invoice detail table (holds invoice number, stock id, quantity, price etc)

I designed two queries based on each table. Made a Report1 based on 1st query
and made a second report(sub) to be a sub report of Report1. Detail section
of the Report1 CAN GROW property was set to NO, while report(sub) CAN GROW
property was set to YES.

Then I made a copy of the Report1 along with its subreport and lets call it
Report2.
Then I made the MAIN INVOICE REPORT, and its pager header I put Report1 as
sub report and its page footer I put Report2 and with little allignment
everything worked perfectly.

Anyway I thank you again for step by step explaination and for your time.
Regards,

1. Create a little table with 2 fields like this:
CopyID Number
CopyName Text
Mark the CopyID as primary key, and save the table with a name such as
tblCopy.

2. Enter 2 records in the table, like this:
CopyID CopyName
===== =========
0 Original
1 Duplicate
or whatever names you want to appear on your report.

3. Create a query using your invoice tables (typically 1 table for the
invoice header, and a related table for the invoice items, linked on an
InvoiceID field.) Add tblCopy as well. There should be no line joining
tblJoin to the invoice tables. (This will give you 2 copies of every row, so
you can get your duplicated report.)

4. Create the report based on this query.
In report design view, open the Sorting and Grouping pane.

5. On the first row of the Sorting'n'Grouping, choose the CopyID field.

6. On the second row of the Sorting'n'Grouping, choose the InvoiceID field.
This time, in the lower pane, set Yes for Group Header and Group Footer.
This adds an InvoiceID group header (above the Detail section), and an
InvoiceID group footer (below the Detail section.)

7. Select the InvoiceID group header, and in the Properties box, set the
Force New Page property to: Before Section. (This ensures every new invoice
starts on a new page.)

8. Increase the height of the InvoiceID Group Header section, so you can
place all the header fields in there: the invoice, date, and address panel.
Place the CopyName there also (assuming you want to print that.)

9. Add the line-item fields to the Detail section -- typically Quantity,
Item, PriceEach etc.

10. In the InvoiceID group footer, add a text box to show the total, and
others you need. Typically the Control Source for the total will look like
this:
=Sum([Amount])

The report will now print 2 copies of each invoice, each starting on a new
page.

Hi All,
I have been searching for solution of my problem for days and have always

[quoted text clipped - 27 lines]

Please provide urgent suggestions, I have to submit this report within 3
hours.