Report with set number of rows per page

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

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.

--
Ron W
www.WorksRite.com
"Ken from Chicago" wrote in message
. ..
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 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
Please feel free to quote anything I say here.

See if the following Microsoft Knowledge Base article helps:

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

--
HTH
Van T. Dinh
MVP (Access)



"Ken from Chicago" wrote in message
. ..
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" wrote in message
...
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.

--
Ron W
www.WorksRite.com
"Ken from Chicago" wrote in message
. ..
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





You can draw boxes ON TOP of subreports?

-- Ken from Chicago

Comment Inline

"Ken from Chicago" wrote in message
...

"Ron Weiner" wrote in message
...
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.

--
Ron W
www.WorksRite.com
"Ken from Chicago" wrote in message
. ..
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





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.

--
Ron W
www.WorksRite.com


-- Ken from Chicago

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...

Ron Weiner wrote:
Comment Inline

"Ken from Chicago" wrote in message
...

"Ron Weiner" wrote in message
.. .

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.

--
Ron W
www.WorksRite.com
"Ken from Chicago" wrote in message
om...

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




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.


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
Please feel free to quote anything I say here.

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/d.../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.
--

Danny J. Lesandrini

http://amazecreations.com/datafast


"Ken from Chicago" wrote in message . ..
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