Question on filling out a report

The database I am working on, the gentlemen I am making it for has a bizarre
request. Maybe it is not bizarre but I have not ever done it or seen it done
before.

He would like the report to have a celled look and take up the whole page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the rest of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform based
off table POItems. I am using a query to filter a report to print only the
current record of the form PO. The textboxes on the table are laid out so
Report Header contains the items needed at the top of the PO, Page header has
the line items names, descriptions, etc.. so if there are enough items order
it will display this section on the second and third page, Detail has the
actual item information from the POItems subform. No Page Footer, Report
footer is the items needed at the bottom of the page. Everything is lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the report only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a full
page look? I hope this is clear enough to understand, any help is greatly
appreciated and thanks in advanced.

Default105,

A general concept here is to "cook up" the report's record source so it
has the number of records to fill a whole page. Which you know how many
records that is, right?

In practice, there are a number of ways this could be done. One
(admittedly slightly kludgy) possibility is to:
1. make a table that mimics the fields in the report's existing query,
with all fields blank except a field that is not visible on the report.
2. then make a Union Query, based on the existing query, and this table.
3. then make a SELECT TOP x query (where x is the number of records that
fit on a page), based on the Union Query, and use this as the report's
Record Source.

--
Steve Schapel, Microsoft Access MVP

default105 wrote:
The database I am working on, the gentlemen I am making it for has a bizarre
request. Maybe it is not bizarre but I have not ever done it or seen it done
before.

He would like the report to have a celled look and take up the whole page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the rest of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform based
off table POItems. I am using a query to filter a report to print only the
current record of the form PO. The textboxes on the table are laid out so
Report Header contains the items needed at the top of the PO, Page header has
the line items names, descriptions, etc.. so if there are enough items order
it will display this section on the second and third page, Detail has the
actual item information from the POItems subform. No Page Footer, Report
footer is the items needed at the bottom of the page. Everything is lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the report only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a full
page look? I hope this is clear enough to understand, any help is greatly
appreciated and thanks in advanced.

You can use the Line method in the On Page event to draw rectangles anywhere
on your page. The following code will draw a box around each control in your
detail section of the report and repeat the boxes for a total of 15 rows.

Private Sub Report_Page()
Dim intNumLines As Integer
Dim intLineNumber As Integer
Dim intTopMargin As Integer
Dim ctl As Control
Dim intLineHeight As Integer

intNumLines = 15
'height of page header
intTopMargin = Me.Section(3).Height
'height of detail section
intLineHeight = Me.Section(0).Height
For Each ctl In Me.Section(0).Controls
For intLineNumber = 0 To intNumLines - 1
Me.Line (ctl.Left, intTopMargin + _
(intLineNumber * intLineHeight)) _
-Step(ctl.Width, intLineHeight), , B
Next
Next
End Sub

--
Duane Hookom
MS Access MVP

"default105" wrote in message
...
The database I am working on, the gentlemen I am making it for has a
bizarre
request. Maybe it is not bizarre but I have not ever done it or seen it
done
before.

He would like the report to have a celled look and take up the whole page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the rest
of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform
based
off table POItems. I am using a query to filter a report to print only
the
current record of the form PO. The textboxes on the table are laid out so
Report Header contains the items needed at the top of the PO, Page header
has
the line items names, descriptions, etc.. so if there are enough items
order
it will display this section on the second and third page, Detail has the
actual item information from the POItems subform. No Page Footer, Report
footer is the items needed at the bottom of the page. Everything is lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the report
only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a
full
page look? I hope this is clear enough to understand, any help is greatly
appreciated and thanks in advanced.

Steve,
I have got the union query and select query made but when I use it in my
record source I still only get the number of records as before. I probably
did something wrong but I would appreciate more of your input if you would.
Thanks


"Steve Schapel" wrote:

Default105,

A general concept here is to "cook up" the report's record source so it
has the number of records to fill a whole page. Which you know how many
records that is, right?

In practice, there are a number of ways this could be done. One
(admittedly slightly kludgy) possibility is to:
1. make a table that mimics the fields in the report's existing query,
with all fields blank except a field that is not visible on the report.
2. then make a Union Query, based on the existing query, and this table.
3. then make a SELECT TOP x query (where x is the number of records that
fit on a page), based on the Union Query, and use this as the report's
Record Source.

--
Steve Schapel, Microsoft Access MVP

default105 wrote:
The database I am working on, the gentlemen I am making it for has a bizarre
request. Maybe it is not bizarre but I have not ever done it or seen it done
before.

He would like the report to have a celled look and take up the whole page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the rest of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform based
off table POItems. I am using a query to filter a report to print only the
current record of the form PO. The textboxes on the table are laid out so
Report Header contains the items needed at the top of the PO, Page header has
the line items names, descriptions, etc.. so if there are enough items order
it will display this section on the second and third page, Detail has the
actual item information from the POItems subform. No Page Footer, Report
footer is the items needed at the bottom of the page. Everything is lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the report only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a full
page look? I hope this is clear enough to understand, any help is greatly
appreciated and thanks in advanced.

Duane,
I copied the code you have to the report but it starts the boxes at the very
top of the report not started in the detail section as you said it would.
Am I missing something?

"Duane Hookom" wrote:

You can use the Line method in the On Page event to draw rectangles anywhere
on your page. The following code will draw a box around each control in your
detail section of the report and repeat the boxes for a total of 15 rows.

Private Sub Report_Page()
Dim intNumLines As Integer
Dim intLineNumber As Integer
Dim intTopMargin As Integer
Dim ctl As Control
Dim intLineHeight As Integer

intNumLines = 15
'height of page header
intTopMargin = Me.Section(3).Height
'height of detail section
intLineHeight = Me.Section(0).Height
For Each ctl In Me.Section(0).Controls
For intLineNumber = 0 To intNumLines - 1
Me.Line (ctl.Left, intTopMargin + _
(intLineNumber * intLineHeight)) _
-Step(ctl.Width, intLineHeight), , B
Next
Next
End Sub

--
Duane Hookom
MS Access MVP

"default105" wrote in message
...
The database I am working on, the gentlemen I am making it for has a
bizarre
request. Maybe it is not bizarre but I have not ever done it or seen it
done
before.

He would like the report to have a celled look and take up the whole page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the rest
of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform
based
off table POItems. I am using a query to filter a report to print only
the
current record of the form PO. The textboxes on the table are laid out so
Report Header contains the items needed at the top of the PO, Page header
has
the line items names, descriptions, etc.. so if there are enough items
order
it will display this section on the second and third page, Detail has the
actual item information from the POItems subform. No Page Footer, Report
footer is the items needed at the bottom of the page. Everything is lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the report
only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a
full
page look? I hope this is clear enough to understand, any help is greatly
appreciated and thanks in advanced.

Default105,

Can you post back with the SQL view of the queries?

--
Steve Schapel, Microsoft Access MVP

default105 wrote:
Steve,
I have got the union query and select query made but when I use it in my
record source I still only get the number of records as before. I probably
did something wrong but I would appreciate more of your input if you would.
Thanks

The first query I use is to create the filter to print only the current
record of the form. The Sql view

SELECT POReqs.POID, POReqs.Vendor, POReqs.EmployeeName,
POReqs.ChargeAccount, POReqs.CostCenter, POReqs.DateCreated,
POReqs.DateNeeded, POReqs.ApprovedBy, POReqs.AFENo, POReqs.PONo,
POReqs.RequisitionNumber, POReqs.DepartmentNo, POReqs.DeliverToDept,
POReqs.PurchasingGroup, POReqs.ShipVia, POReqs.Terms, POReqs.VendorNo,
POReqs.PromisedDDate, POReqs.TaxMsg, POReqs.Attachments, POReqs.MachineNo,
POReqs.MachineType, POItems.LineNo, POItems.UnitNo, POItems.UnitType,
POItems.SAPMatlNo, POItems.PartNo, POItems.PartDescription,
POItems.UnitPrice, POItems.Amount, POItems.PASalesTax, POItems.Total,
POItems.UnionRequired
FROM POReqs INNER JOIN POItems ON POReqs.POID = POItems.POID
WHERE (((POReqs.POID)=[Forms]![PO]![POID]));

the union query where test is table created with the same number of columns
with no data entered

select PurchaseOrderFilter.*
from PurchaseOrderFilter
UNION select Test.*
from Test;


the select top query sql view

SELECT TOP 20 POUnion.*
FROM POUnion;

Hope that is what you wanted



"Steve Schapel" wrote:

Default105,

Can you post back with the SQL view of the queries?

--
Steve Schapel, Microsoft Access MVP

default105 wrote:
Steve,
I have got the union query and select query made but when I use it in my
record source I still only get the number of records as before. I probably
did something wrong but I would appreciate more of your input if you would.
Thanks

You will need to enter some records into the Test table. I think you
will need 19. Did you do that already? You will need to enter data in
at least one of the fields, in order to create the records, so I suppose
that will be in a field that is not shown on the report.

Then, test out the queries by running them directly, to see how many
records are returned. The Union Query should return x+19 records, where
x is the number of records returned by your original query.

Let us know how you get on with that.

--
Steve Schapel, Microsoft Access MVP


default105 wrote:
The first query I use is to create the filter to print only the current
record of the form. The Sql view

SELECT POReqs.POID, POReqs.Vendor, POReqs.EmployeeName,
POReqs.ChargeAccount, POReqs.CostCenter, POReqs.DateCreated,
POReqs.DateNeeded, POReqs.ApprovedBy, POReqs.AFENo, POReqs.PONo,
POReqs.RequisitionNumber, POReqs.DepartmentNo, POReqs.DeliverToDept,
POReqs.PurchasingGroup, POReqs.ShipVia, POReqs.Terms, POReqs.VendorNo,
POReqs.PromisedDDate, POReqs.TaxMsg, POReqs.Attachments, POReqs.MachineNo,
POReqs.MachineType, POItems.LineNo, POItems.UnitNo, POItems.UnitType,
POItems.SAPMatlNo, POItems.PartNo, POItems.PartDescription,
POItems.UnitPrice, POItems.Amount, POItems.PASalesTax, POItems.Total,
POItems.UnionRequired
FROM POReqs INNER JOIN POItems ON POReqs.POID = POItems.POID
WHERE (((POReqs.POID)=[Forms]![PO]![POID]));

the union query where test is table created with the same number of columns
with no data entered

select PurchaseOrderFilter.*
from PurchaseOrderFilter
UNION select Test.*
from Test;


the select top query sql view

SELECT TOP 20 POUnion.*
FROM POUnion;

You should be able to adjust the intTopMargin value. You might have to
include the heights of the Report Header and possibly group headers or other
sections.
--
Duane Hookom
MS Access MVP

"default105" wrote in message
...
Duane,
I copied the code you have to the report but it starts the boxes at the
very
top of the report not started in the detail section as you said it would.
Am I missing something?

"Duane Hookom" wrote:

You can use the Line method in the On Page event to draw rectangles
anywhere
on your page. The following code will draw a box around each control in
your
detail section of the report and repeat the boxes for a total of 15 rows.

Private Sub Report_Page()
Dim intNumLines As Integer
Dim intLineNumber As Integer
Dim intTopMargin As Integer
Dim ctl As Control
Dim intLineHeight As Integer

intNumLines = 15
'height of page header
intTopMargin = Me.Section(3).Height
'height of detail section
intLineHeight = Me.Section(0).Height
For Each ctl In Me.Section(0).Controls
For intLineNumber = 0 To intNumLines - 1
Me.Line (ctl.Left, intTopMargin + _
(intLineNumber * intLineHeight)) _
-Step(ctl.Width, intLineHeight), , B
Next
Next
End Sub

--
Duane Hookom
MS Access MVP

"default105" wrote in message
...
The database I am working on, the gentlemen I am making it for has a
bizarre
request. Maybe it is not bizarre but I have not ever done it or seen
it
done
before.

He would like the report to have a celled look and take up the whole
page.
Like you would in Excel if you put borders around the cells. Well, the
border look on the report is not the problem but the filling up the
rest
of
the page with a bordered effect is giving me a fit.

First off, I have two tables, a form based off table PO and a subform
based
off table POItems. I am using a query to filter a report to print only
the
current record of the form PO. The textboxes on the table are laid out
so
Report Header contains the items needed at the top of the PO, Page
header
has
the line items names, descriptions, etc.. so if there are enough items
order
it will display this section on the second and third page, Detail has
the
actual item information from the POItems subform. No Page Footer,
Report
footer is the items needed at the bottom of the page. Everything is
lined
tight to create the celled(bordered) appearance that he is looking for.
However, if there are not enough items in the POItems subform the
report
only
fills one half the page or less.
Is there a way to have boxes fill in the rest of the report to create a
full
page look? I hope this is clear enough to understand, any help is
greatly
appreciated and thanks in advanced.

Well it must be something wrong with my query or table set up because I have
19 records in the table test and when I run the union query it adds only 1
record. Is it possible that the problem is in the table set up. This does
pose a problem that I will have to fix later though. The original query
had(past tense) POID.POReqs and POID.POItems where POID where joined so when
the filter is run it would filter the criteria [Forms]![PO]![POID]. Since I
had to remove it since you can not have the same name field twice in a table
it now pops up with a inputbox for POItems #. That is what caused such a
delay of my first reply, I had to figure out why I was getting the message
that the number of columns in my union query are not equal. Is this the
cause of the problem?

This just proves that even as much as I thought I knew about access, that it
is a very vast and there is always more to learn. By the way, you guys are
great, it is nice to see others that like and love to help others learn.
Thanks much.

"Steve Schapel" wrote:

You will need to enter some records into the Test table. I think you
will need 19. Did you do that already? You will need to enter data in
at least one of the fields, in order to create the records, so I suppose
that will be in a field that is not shown on the report.

Then, test out the queries by running them directly, to see how many
records are returned. The Union Query should return x+19 records, where
x is the number of records returned by your original query.

Let us know how you get on with that.

--
Steve Schapel, Microsoft Access MVP