How to avoid blank pages in an report

[jj]

Hi Newsgroup

I got a huge problem - I have posted this problem in another newsgroup
once - but without any responses so I will try again here.

I created a dynamic report and the fields is displayed
based on a query. The report contains about 34 fields so if the query has
34 fields, all
34 fields in the report shows up. With a page width wider than one page. But
if the query has 10 (which most of
them have) fields, the report only shows 10 fields and hides the rest. Here
the width is less than one page but here it keeps showing two
where one is blank. I have sat the textbox'es to cangrow/can shink to yes,
and also with reporthead and reportfoot and also with group and
detailsections.

Help would really be appriciated.

Thanks
JJ

 

[Allen Browne]

CanShrink and CanGrow apply to the vertical movement of controls only. The
text box will not grow/shrink horizontally.

I take it that you placed 34 text boxes side-by-side across your report.
Then in the Open event of the report you assign a query to its RecordSource,
assign the query's fields to the text boxes' ControlSource, and set the
Visible property to False for the unused text boxes.

You will also need to set the Left and Width of your text boxes, based on
the number you need to show. Programmatically, these properties are in
twips, where 1440 twips = 1 inch. You will need to save the report so that
its Width fits in the page size less margins. For example if you use Letter
size paper in Landscape orientation with 0.5" left and right margins, the
Width property must be 10", i.e. 14400 twips.

So, if you use Query1 as the report's RecordSource, and
CurrentDb.QueryDefs("lqPC").Fields.Count
is 20, then the Width of each one will be 14400 / 20, and the Left of each
one will be n * that (where n is the values from zero to 19.)

The boxes will then use up all available space on the page, however many
fields are in the query, and the boxes will grow vertically if ther CanGrow
is set.

 

[David S]

My experience has been that the width of Access Reports won't change
from how it is set in design mode even if you hide controls and change
me.Width on the report. However, if you are using access 2002 or later
you can dynamically adjust the width when you go to print preview like
this:

Dim rpt As Report
DoCmd.SetWarnings False
DoCmd.OpenReport "rptReport1", acViewPreview
DoCmd.RunCommand acCmdZoom75
DoCmd.Maximize
Set rpt = Reports("rptReport1")

With rpt.Printer
.Orientation = acPRORPortrait
.DefaultSize = False
.ItemSizeWidth = 11300
End With
DoCmd.SetWarnings True

I don't know of a way to change the report size when going directly to
print (although I'd know how to do it if there is a way).

Anyway, I hope this is of some help.

-- David.

 

[jj]

Thanks for your reply

Unfortunatly it must work for access 2000 too.

 

[jj]

Hi Allen

Thank you for your answer.

I'm not sure if I understand it though.

If I understand you right the report will always fit to on page no matter if
it is 10 or 34 fields which is used - Is that right?
Because when it is more than 20 fields there is not enorgh space on one
landscape page and here it must me wider ?!.

Thanks
Jacob

 

[Allen Browne]

So you want it to print 2 landscape pages wide if there are more than 20
fields in the source query?

I'm not sure, but you might have to open the report in design view if you
are modifying its Width. From memory, that does not work well if you try to
do that after the repor has opened. A workaround might be to have 2 reports
saved (one for < 20 fields, and a wider report.) Open the narrow one by
default, and if you discover you have more than 20 fields to work with,
cancel it's Open event and open the other one instead.

 

[jj]

Thx Allen - I will make two reports

So you want it to print 2 landscape pages wide if there are more than 20
fields in the source query?

I'm not sure, but you might have to open the report in design view if you
are modifying its Width. From memory, that does not work well if you try
to do that after the repor has opened. A workaround might be to have 2
reports saved (one for < 20 fields, and a wider report.) Open the narrow
one by default, and if you discover you have more than 20 fields to work
with, cancel it's Open event and open the other one instead.