Question on filling out a report

[Guest]

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 Schapel]

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.

 

[Duane Hookom]

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

 

[Guest]

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

 

[Guest]

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?

 

[Steve Schapel]

Default105,

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

 

[Guest]

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]

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.

 

[Duane Hookom]

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.

 

[Guest]

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.

 

[Guest]

I kindof thought that was the appropriate action but I was having difficulty
figuring out how add the value in. I will research it more.

 

[Duane Hookom]

Modify
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 (3) and report header (1)
intTopMargin = Me.Section(3).Height + Me.Section(1).Height
--
Duane Hookom
MS Access MVP

I kindof thought that was the appropriate action but I was having
difficulty
figuring out how add the value in. I will research it more.

 

[Guest]

That works, in fact I was making it harder by using a absolute number for the
height.

Last question for you as I hope you do not mind. It displays the boxes now
in the detail section, however it boxes over the report footer layout (ie. if
i have 2 records, it showed the 2 records then immediately the report footer
with the boxes over it). Can it be setup that the report footer will always
be at the bottom of the last row of boxes be determining the detail section
height times 15 and set it to start there?

I have been looking for the numbers of the sections on a report. ie detail
= 0, where can I find that information at.

Thanks for all your help

 

[Duane Hookom]

There is a KB article on printing a group footer at a specific location that
might help http://support.microsoft.com/kb/208979/en-us.

Regarding the report sections:
Detail 0
Report Header 1
Report Footer 2
Page header 3
Page Footer 4
Level1 Header 5
Level1 Footer 6
....

--
Duane Hookom
MS Access MVP

That works, in fact I was making it harder by using a absolute number for
the
height.

Last question for you as I hope you do not mind. It displays the boxes
now
in the detail section, however it boxes over the report footer layout (ie.
if
i have 2 records, it showed the 2 records then immediately the report
footer
with the boxes over it). Can it be setup that the report footer will
always
be at the bottom of the last row of boxes be determining the detail
section
height times 15 and set it to start there?

I have been looking for the numbers of the sections on a report. ie
detail
= 0, where can I find that information at.

Thanks for all your help

 

[Steve Schapel]

That works, in fact I was making it harder by using a absolute number for the
height.

What units were you using for the height? Needs to be in Twips. Not
sure of the conversion from inches, but Cm*567. So if you put in a
small number, it would count as a very small distance!

 

[Guest]

Well I figured from trial and error that it had to be a large number. I was
looking for a conversion to convert to twips. Was just looking at the post
from you yesterday and was reading the link you referred. Thanks for
everything, you guys are great, it is great to meet someone how likes to help
others learn. Says a lot about all you MVP's.

 

[Guest]

It was a query problem, I was using union but found it needed to be union all.

Thanks for everything

 

[Guest]

I just want to say that Duane Hookom is the best and I hope to be half as
good as him one day. Thanks Duane, you have help me so much.