Report with set number of rows per page

[Ken from Chicago]

Hello. I am using MS Access 2000 to print Purchase Orders to match
pre-existing printed forms with a set number of rows per page, 20 rows.

Basically the vendor info (name, phone, address, etc.) and purpose of the PO
is printed followed by the individual items purchased--up to 20 rows, each
in a box, on a page, and if more space is needed then you go to another
page. I've been using a report with a subreport for the individual items.

I have two tables:
--a Purchase Order table with vendor, name, address, who made the order,
purpose for the order, taxes, shipping, total, etc. with each record having
a unique Purchase Order Number (PON);
--a Purchase Items table with item, units, quantity, price per unit, item
amount, with each record having a unique Purchase Item Number (PIN) and a
non-unique Purchase Order Number (PON) to which it is linked.

The problem is if there are less than 20 items for a Purchase Order then it
would leave a gap and instead of printing an empty box.

To get around that I have been using multiple queries:
--A select query where the Purchase Orders are linked to the Purchase Items
by matching the PON in a one-to-many relationship (and if there are no
matching Purchase Items it still lists the Purchase Order with the purpose
but no detailed items) and counts the number of Purchase Items with matching
PONs (and if there are no matching Purchase Items then that number would be
zero), as well as figure out the number of pages a Purchare Order requires
(number of Purchase Items divided by 20, the number of rows allowed per
page)--RESULT: Purchase Order Header query with one record per Purchase
Order.
--A select query where the Purchase Order Header query and a Nums table
(simply a table with a field labled Num, which has list of a thousand
records, listing numbers from 0 to 999) that are NOT linked. Num criteria is
greater than 0 and less than or equal to the number of pages per Purchase
Order--RESULT: Purchase Order Page Header query with one record per page per
Purchase Order.
--A select query where the Purchase Order Header query and a Nums table are
NOT linked. Num is limited to greater than 0 and less than 21. Each row a
Row Number (from 1 to 20) and a Page Number--RESULT: Purchase Order Page
Rows query with one record per row per page per Purchase Order.
--A select query that counts the Purchase Items by how many records in the
Purchase Items table with a matching PON *and* a PIN that is less than or
equal to the current PIN of the record, the Purchase Row Number (PRN). It
also calculates Row Number dividing the number of items for a purchase by 20
and getting the remainder ( ( (PRN - 1) mod 20 ) + 1). It calculates the
Page Number for an iem by dividing the number of items by 20 ( Int ( (PRN -
1) / 20 ) + 1)--RESULT: Purchase Item Row query with one record per Purchase
Item.
--A select query where the Purchase Order Page Rows query is linked
one-to-many to the Purchase Item Row query by PON, Page Number and Row
Number--RESULT: Purchase Order Report query with 20 records per page per
Purchase Order.

I print the Purchase Orders using the Purchase Order Report query in the
main, grouped by PON and Page Number and Row Number, naturally a page break
is inserted before each new PON group and each new Page Number group.




Is there a simpler, more elegant way of forcing Access 2000 print 20 rows
with empty boxes per page and tell it if there are matching Purchase Items
fill up those 20 rows and if not then print the empty boxes anyway? And
keeping same Purchase Order header there are more than 20 items per page?

-- Ken from Chicago

 

[Ron Weiner]

First allow me to apologize for not reading your entire post (especially the
SQL code). More information than I wanted to process on second day of the
new year.

But, I think I have the gist of what you are attempting to accomplish. As I
understand it you want to *ALWAYS* have a grid of 20 rows on your page. If
this is the case why don't you turn off all of the lines on your subform and
simple draw a grid of 20 rows and N columns over top of the subreport. You
can use the line and/or box tool in design mode. The only caveat would be
to make sure that you move all of the lines to the top of the Z order (or
the subreport to the bottom). Obviously you will need to screw around with
the line placement until it matches up with the subreport underneath, and
any future changes you make in the subreport will screw up your lines on the
main report, requiring additional line screwing.

This all seemed to work in a quick test. Even though the lines I drew did
not appear on top of the Subform at design time they did appear on top of
the subreport in print preview, and on the actual printed page.

All of this frees you from having to design queries that bring back extra
empty rows, but you will still need code that does appropriate paging, and
record selection (You might want to take a look at the Sql TOP N predicate
and a WHERE clause the limits the first record to the next logical row) for
the sub report.

Frankly, I am not sure that this is a more elegant solution to what you are
already doing, and in the end, I am not even sure that it will work! Just
thought I'd inject my $.02. Good luck with your project.

 

[Vincent Johns]

Ron Weiner wrote:

[...]

All of this frees you from having to design queries that bring back extra
empty rows, but you will still need code that does appropriate paging, and
record selection (You might want to take a look at the Sql TOP N predicate
and a WHERE clause the limits the first record to the next logical row) for
the sub report.

One problem with using TOP 20 (or whatever) for this purpose is that TOP
20 might return more or fewer than 20 records. Fewer, if the dataset
contains only 7 records after filtering. More, if the 33rd record from
the top contains the same value as the 19th. (This latter problem can
be avoided by including the primary key as one of the sorting fields.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Van T. Dinh]

See if the following Microsoft Knowledge Base article helps:

http://support.microsoft.com/?id=209006

 

[Ken from Chicago]

First allow me to apologize for not reading your entire post (especially
the
SQL code). More information than I wanted to process on second day of the
new year.

But, I think I have the gist of what you are attempting to accomplish. As
I
understand it you want to *ALWAYS* have a grid of 20 rows on your page.
If
this is the case why don't you turn off all of the lines on your subform
and
simple draw a grid of 20 rows and N columns over top of the subreport.
You
can use the line and/or box tool in design mode. The only caveat would be
to make sure that you move all of the lines to the top of the Z order (or
the subreport to the bottom). Obviously you will need to screw around
with
the line placement until it matches up with the subreport underneath, and
any future changes you make in the subreport will screw up your lines on
the
main report, requiring additional line screwing.

This all seemed to work in a quick test. Even though the lines I drew did
not appear on top of the Subform at design time they did appear on top of
the subreport in print preview, and on the actual printed page.

All of this frees you from having to design queries that bring back extra
empty rows, but you will still need code that does appropriate paging, and
record selection (You might want to take a look at the Sql TOP N predicate
and a WHERE clause the limits the first record to the next logical row)
for
the sub report.

Frankly, I am not sure that this is a more elegant solution to what you
are
already doing, and in the end, I am not even sure that it will work! Just
thought I'd inject my $.02. Good luck with your project.

You can draw boxes ON TOP of subreports?

-- Ken from Chicago

 

[Ron Weiner]

Comment Inline

You can draw boxes ON TOP of subreports?

I created a small test report using A2K and was able to draw both lines and
boxes ON TOP of a sub report. All I did was to make sure that all of the
lines and boxes were Format | Bring to Front and the subform was Format |
Send to Back. Even though it did not appear that way in design mode, it did
Print Preview, and Print with the lines and boxes ON TOP of the subform.

 

[Vincent Johns]

An alternate approach that may work for you (but, like others posting
here, I'm not sure that mine is any more elegant than yours) I have
outlined below. My example is kind of lengthy and is based on a hybrid
of your design and Northwind Traders.

What I did was to define some Queries to display what I wanted to see,
but without using any Subreports. I'll leave it to you to decide if you
like the results...

Comment Inline




I created a small test report using A2K and was able to draw both lines and
boxes ON TOP of a sub report. All I did was to make sure that all of the
lines and boxes were Format | Bring to Front and the subform was Format |
Send to Back. Even though it did not appear that way in design mode, it did
Print Preview, and Print with the lines and boxes ON TOP of the subform.

I'm also using A2K, and I was even able to avoid using the graphic stuff
that I had at first thought necessary. (I'd tried covering up some of
the text with a white Text Box, and that looked OK on the screen but
didn't print well to a file).

I'll start with a bit of background. I used Tables from the Northwind
Traders sample database as a basis for my Report, and I modified a
couple of the Queries from Northwind Traders as well.

One of the Northwind Traders Tables that I used was [Purchase Order]. I
didn't change this Table except to rename a couple of fields and disable
Lookup properties.

[Purchase Order] Table Datasheet View (last 3 records):

PON_ID CustomerID ShipAddress EmployeeID
------ ---------- -------------------- ----------
...
11075 RICSU Starenweg 5 8
11076 BONAP 12, rue des Bouchers 4
11077 RATTC 2817 Milton Dr. 1

Purpose Taxes Shipping OrderDate RequiredDate
-------- ------ -------- ----------- ------------
...
$6.19 06-May-1998 03-Jun-1998
$38.28 06-May-1998 03-Jun-1998
$8.53 06-May-1998 03-Jun-1998

ShippedDate ShipVia ShipName
----------- ------- ------------------
...
2 Richter Supermarkt
2 Bon app'
2 Rattlesnake Canyon Grocery

ShipCity ShipRegion ShipPostalCode ShipCountry
----------- ---------- -------------- -----------
...
Genève 1204 Switzerland
Marseille 13008 France
Albuquerque NM 87110 USA

A purchase order includes several product records stored in the
[Purchase Items] Table.

[Purchase Items] Table Datasheet View (typical record):

PON_ID Product_ID Quantity Price per unit Discount
------ ---------- -------- -------------- --------
...
11075 Chang 10 $19.00 15.00%
...


Although I know you wish to display 20 items per page, for brevity in
this message I chose to display only 7 items per page. So, if you wish
to follow my suggestion, you'll need to change each 7 to 20. For
example, you'd need to add another 13 records to the following Table.


[Purchase Items Blanks] Table Datasheet View (entire Table):

LineBlank
---------
1
2
3
4
5
6
7

Since [Order Details] includes a foreign key linking it to [Products], I
defined the following Lookup Query to display the product names.

[QL_Products] SQL:

SELECT DISTINCTROW Products.Product_ID,
Products.Item
FROM Products
ORDER BY Products.Item;

I added a blank record (record #78) to [Products], to be used later in
padding out partial pages.

[QL_Products] Query Datasheet View (first few records):

Product_ID Item
---------- -------------
78
17 Alice Mutton
3 Aniseed Syrup
40 Boston Crab Meat
...

To number the lines and pages (assuming, for now, 7 items per page), I
modified the Northwind Traders [Order Details] Query to include new
fields [Line #] and [Page #]. For this example, I limited the list to
the last 3 (purchase order numbers 11075 and up).

[Order Details Numbered] SQL:

SELECT DISTINCTROW [Purchase Items].PON_ID,
([Line #]-1)\7+1 AS [Page #],
Count([Purchase Items_1].Product_ID) AS [Line #],
[Purchase Items].Product_ID,
[Purchase Items].[Price per unit],
[Purchase Items].Quantity,
[Purchase Items].Discount,
CCur([Purchase Items].[Price per unit]
*[Purchase Items]![Quantity]
*(1-[Purchase Items]![Discount])/100)*100
AS ExtendedPrice
FROM [Purchase Items]
INNER JOIN [Purchase Items] AS [Purchase Items_1]
ON [Purchase Items].PON_ID
= [Purchase Items_1].PON_ID
WHERE ((([Purchase Items_1].Product_ID)
<=[Purchase Items]![Product_ID]))
GROUP BY [Purchase Items].PON_ID,
[Purchase Items].Product_ID,
[Purchase Items].[Price per unit],
[Purchase Items].Quantity, [Purchase Items].Discount,
CCur([Purchase Items].[Price per unit]
*[Purchase Items]![Quantity]
*(1-[Purchase Items]![Discount])/100)*100
HAVING ((([Purchase Items].PON_ID)>=11075))
ORDER BY [Purchase Items].PON_ID;

Note that the last purchase order, #11077, includes 25 items, so at 7
items/page, they occupy 4 pages.

[Order Details Numbered] Query Datasheet View (last of 31 records):

PON_ID Page # Line # Product_ID
------ ------ ------ -------------------------------
11077 4 25 Original Frankfurter grüne Soße

Price per unit Quantity Discount ExtendedPrice
-------------- -------- -------- -------------
$13.00 2 0.00% $26.00


The next Query lists the number of pages and number of lines for each
purchase order.

[Order Details MaxPage] SQL:
SELECT DISTINCTROW
[Order Details Numbered].PON_ID,
Max([Order Details Numbered].[Page #]) AS MaxPage,
Max([Order Details Numbered].[Line #]) AS MaxLine
FROM [Order Details Numbered]
GROUP BY [Order Details Numbered].PON_ID
ORDER BY [Order Details Numbered].PON_ID;

[Order Details MaxPage] Query Datasheet View:

PON_ID MaxPage MaxLine
------ ------- -------
11075 1 3
11076 1 3
11077 4 25

What we want to do is to determine how many blank records to append to
each purchase order to fill out the last page, and [Order Details
MaxPage] gives us all we need to know to do that.

The next Query produces the blank records, and this is where we use the
new [Products] record 78 (which has a blank name).

[Order Details Blanks] SQL:

SELECT DISTINCTROW
[Order Details MaxPage].PON_ID,
[Order Details MaxPage].MaxPage AS [Page #],
[Order Details MaxPage]![MaxLine]+[LineBlank]
AS LineGen,
78 AS Product_ID, 0 AS Quantity,
0 AS [Price per unit], 0 AS Discount,
0 AS ExtendedPrice
FROM [Order Details MaxPage],
[Purchase Items Blanks]
WHERE ((([Purchase Items Blanks].LineBlank)
<=7-1-(([Order Details MaxPage]![MaxLine]-1) Mod 7)))
GROUP BY [Order Details MaxPage].PON_ID,
[Order Details MaxPage].MaxPage,
78, 0, 0, 0, 0,
[Purchase Items Blanks].LineBlank,
7-1-(([Order Details MaxPage]![MaxLine]-1) Mod 7),
[Order Details MaxPage].MaxLine
ORDER BY [Order Details MaxPage].PON_ID;

These records will fill out the last page of each purchase order. Note
that 78 identifies the new record in [Products] that has a blank name.

[Order Details Blanks] Query Datasheet View:

PON_ID Page Line Product Quan Price Dis Extended
# Gen _ID tity per unit count Price
------ ---- ---- ------- ---- -------- ----- --------
11075 1 4 78 0 0 0 0
11075 1 5 78 0 0 0 0
11075 1 6 78 0 0 0 0
11075 1 7 78 0 0 0 0
11076 1 4 78 0 0 0 0
11076 1 5 78 0 0 0 0
11076 1 6 78 0 0 0 0
11076 1 7 78 0 0 0 0
11077 4 26 78 0 0 0 0
11077 4 27 78 0 0 0 0
11077 4 28 78 0 0 0 0

We can now combine them into a list, for each purchase order, of all
ordered products and all the blank items.

[Order Details FullPage] SQL:

SELECT [Order Details Numbered].PON_ID,
[Order Details Numbered].[Line #],
[Order Details Numbered].[Page #],
[Order Details Numbered].Product_ID,
[Order Details Numbered].Quantity,
[Order Details Numbered].[Price per unit],
[Order Details Numbered].Discount,
[Order Details Numbered].ExtendedPrice
FROM [Order Details Numbered]
UNION ALL
SELECT [Order Details Blanks].PON_ID,
[Order Details Blanks].LineGen,
[Order Details Blanks].[Page #],
[Order Details Blanks].Product_ID,
[Order Details Blanks].Quantity,
[Order Details Blanks].[Price per unit],
[Order Details Blanks].Discount,
[Order Details Blanks].ExtendedPrice
FROM [Order Details Blanks]
ORDER BY [Order Details Numbered].PON_ID,
[Order Details Numbered].[Line #],
[Order Details Numbered].[Page #];

[Order Details FullPage] Query Datasheet View (last 5 of 42 records):

PON_ID Line Page Product Quan Price Dis Extended
# # _ID tity per unit count Price
------ ---- ---- ------- ---- -------- ----- --------
11077 25 4 77 2 $13.00 0 $26.00
11077 26 4 78 0 $0.00 0 $0.00
11077 27 4 78 0 $0.00 0 $0.00
11077 28 4 78 0 $0.00 0 $0.00

[Invoices] SQL:

SELECT DISTINCTROW
"Acme Explosives" AS ShipName,
[Purchase Order].CustomerID,
[Purchase Order].PON_ID,
[Purchase Order].OrderDate,
[Purchase Order].RequiredDate,
[Purchase Order].ShippedDate,
[Order Details FullPage].[Page #],
[Order Details FullPage].Product_ID,
Products.Item,
[Order Details FullPage].[Price per unit],
[Order Details FullPage].Quantity,
[Order Details FullPage].Discount,
[Order Details FullPage].ExtendedPrice,
[Purchase Order].Shipping
FROM ([Purchase Order]
INNER JOIN [Order Details FullPage]
ON [Purchase Order].PON_ID
= [Order Details FullPage].PON_ID)
INNER JOIN Products
ON [Order Details FullPage].Product_ID
= Products.Product_ID
ORDER BY [Purchase Order].CustomerID,
[Order Details FullPage].[Line #];

[Invoices] Query Datasheet View (2 of the 42 records are shown here):

CustomerID PON_ID Page # Product_ID Item
---------- ------ ------ ---------- ------------
...
RICSU 11075 1 76 Lakkalikööri
RICSU 11075 1 78
...

Price per unit Quantity Discount ExtendedPrice Shipping
-------------- -------- -------- ------------- --------
...
$18.00 2 15.00% $30.60 $6.19
$0.00 0 0.00% $0.00 $6.19
...

For the Report, I deleted some Fields and inserted a [Page #] field at
the top. I also added code for the "Format" Event of the [Detail]
section of the Report to look for blank [ProductName] fields and hide
the entire record when they occur. This involves a line of code for
each field appearing there. Since [ProductName] is already blank in
these records, the line of code hiding it need not have been included.

Since the numeric fields are all zero, they don't affect the sums, but
in a Report displaying averages or other statistics, they might have had
an effect, so in a case like that you'd have to make special provisions
for them.


Private Sub Detail_Format( _
Cancel As Integer, _
FormatCount As Integer)

Dim blnShowIt As Boolean 'Record is blank, so we
'should hide the Detail controls

With Me

blnShowIt = (.ProductName.OldValue <> "")

'Hide all controls in the Detail section of the
' Report, if this is an empty record.
'Otherwise, print all of them.

.ProductID.Visible = blnShowIt
.ProductName.Visible = blnShowIt
.Quantity.Visible = blnShowIt
.UnitPrice.Visible = blnShowIt
.Discount.Visible = blnShowIt
.ExtendedPrice.Visible = blnShowIt

End With 'Me

End Sub 'Detail_Format

With this Event Handler active to blank out the records added to pad out
the last page of each invoice, the [Invoice Paged] Report, based on the
[Invoice] Report in Northwind Traders, produces the following output,
consisting of these six pages:

+---------------------------------------------------------
|
| Northwind Traders Page 1 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11075 RICSU 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 2 Chang 10 $19.00 15% $161.50
| --------------------------------------------------------
| 46 Spegesild 30 $12.00 15% $306.00
| --------------------------------------------------------
| 76 Lakkalikööri 2 $18.00 15% $30.60
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
| Subtotal: $498.10
| ------------------
| Freight: $6.19
| -------------------
| Total: $504.29
| -------------------
|
+---------------------------------------------------------

+---------------------------------------------------------
|
| Northwind Traders Page 1 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11076 BONAP 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 6 Grandma's Boysenbe 20 $25.00 25% $375.00
| --------------------------------------------------------
| 14 Tofu 20 $23.25 25% $348.75
| --------------------------------------------------------
| 19 Teatime Chocolate 10 $9.20 25% $69.00
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
| Subtotal: $792.75
| ------------------
| Freight: $38.28
| -------------------
| Total: $831.03
| -------------------
|
+---------------------------------------------------------

+---------------------------------------------------------
|
| Northwind Traders Page 1 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11077 RATTC 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 2 Chang 24 $19.00 20% $364.80
| --------------------------------------------------------
| 3 Aniseed Syrup 4 $10.00 0% $40.00
| --------------------------------------------------------
| 4 Chef Anton's Cajun 1 $22.00 0% $22.00
| --------------------------------------------------------
| 6 Grandma's Boysenbe 1 $25.00 2% $24.50
| --------------------------------------------------------
| 7 Uncle Bob's Organi 1 $30.00 5% $28.50
| --------------------------------------------------------
| 8 Northwoods Cranber 2 $40.00 10% $72.00
| --------------------------------------------------------
| 10 Ikura 1 $31.00 0% $31.00
| --------------------------------------------------------
|
|
|
|
|
|
|
+---------------------------------------------------------

+---------------------------------------------------------
|
| Northwind Traders Page 2 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11077 RATTC 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 12 Queso Manchego La 2 $38.00 5% $72.20
| --------------------------------------------------------
| 13 Konbu 4 $6.00 0% $24.00
| --------------------------------------------------------
| 14 Tofu 1 $23.25 3% $22.55
| --------------------------------------------------------
| 16 Pavlova 2 $17.45 3% $33.85
| --------------------------------------------------------
| 20 Sir Rodney's Marma 1 $81.00 4% $77.76
| --------------------------------------------------------
| 23 Tunnbröd 2 $9.00 0% $18.00
| --------------------------------------------------------
| 32 Mascarpone Fabioli 1 $32.00 0% $32.00
| --------------------------------------------------------
|
|
|
|
|
|
|
+---------------------------------------------------------

+---------------------------------------------------------
|
| Northwind Traders Page 3 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11077 RATTC 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 39 Chartreuse verte 2 $18.00 5% $34.20
| --------------------------------------------------------
| 41 Jack's New England 3 $9.65 0% $28.95
| --------------------------------------------------------
| 46 Spegesild 3 $12.00 2% $35.28
| --------------------------------------------------------
| 52 Filo Mix 2 $7.00 0% $14.00
| --------------------------------------------------------
| 55 Pâté chinois 2 $24.00 0% $48.00
| --------------------------------------------------------
| 60 Camembert Pierrot 2 $34.00 6% $63.92
| --------------------------------------------------------
| 64 Wimmers gute Semme 2 $33.25 3% $64.51
| --------------------------------------------------------
|
|
|
|
|
|
|
+---------------------------------------------------------

+---------------------------------------------------------
|
| Northwind Traders Page 4 INVOICE
| =========================================================
| One Portals Way
| Twin Points WA 98156 Date:
| Phone: 1-206-555-1417 03-Jan-2006
| Bill To:
| Order ID: Customer ID: Order Date:
| 11077 RATTC 06-May-1998
|
| ID: Product Name: Quant: Unit Disc.: Ext.
| Price: Price:
| --------------------------------------------------------
| 66 Louisiana Hot Spic 1 $17.00 0% $17.00
| --------------------------------------------------------
| 73 Röd Kaviar 2 $15.00 1% $29.70
| --------------------------------------------------------
| 75 Rhönbräu Klosterbi 4 $7.75 0% $31.00
| --------------------------------------------------------
| 77 Original Frankfurt 2 $13.00 0% $26.00
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
|
| --------------------------------------------------------
| Subtotal: 1,255.72
| ------------------
| Freight: $8.53
| -------------------
| Total: 1,264.25
| -------------------
|
+---------------------------------------------------------

Note that no Subreport was needed for this (though I did have to define
some new Queries), and that no special graphics (possibly difficult to
print) were required.

As I mentioned, the references to "7" in the SQL and VBA code would have
to be changed to the number of records you want printed on each page.



-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Danny J. Lesandrini]

Ken:

I'm sorry I didn't see this sooner. I have code that will do this for you.
You'll have to modify it to meet your needs, and the file is in Access 97
format, but it can be upsized.

http://amazecreations.com/datafast/downloads/AdmnCodes.zip

Basically, the solution is to do advance the print location after printing
the last record and reprint the last record as many times as needed to
create the empty boxes. However, you toggle the font color to WHITE.
Cool trick, huh? Printing the last record with white ink, so to speak,
leaves an empty box.