Dont show blank pages in print preview in report

[jj]

I created a dynamic report and the fields is displayed
base on the query. The report contains about 34 fields so if the query has
34 fields, all
34 fields in the report shows up. But if the query has 10 (which most of
them have) fields, the report only shows 10 fields and hides the rest.
What happen is that when I go to print preview, its shows
the pages that have the fields and the blank page. The
blank page infact are the hidden fields. My problem is
that, how do I tell the print preview to display only
the pages that have the fields and don't show the pages
that contain the hidden fields.

Thanks

JJ

[Jamey]

You'll have to monkey around with your report's RecordSource, which it
sounds like you're doing anyway. You'll still have to supply fields for
all controls with a ControlSource property set and use set each
control's CanGrow, CanShrink property to 'Yes'. The Detail section of
the report, and any grouping sections (i.e. GroupHeader0, GroupFooter1)
also need to have their CanGrow, CanShrink props set to 'Yes'.

For your report's RecordSource in the 34 field case use:

SELECT Table1.*
FROM Table
WHERE ...whatever...
ORDER BY ....something.. .;

For the 10 field case use:

SELECT Table1.Field1, Table1.Field2, ...,
Table1.Field10, Null as Field11, ...,
Null as Field 34
FROM Table1
WHERE ...whatever...
ORDER BY ...something... ;

This will keep you from getting prompted for the field values that
don't appear in every iteration of the report and provide those fields
with a Null value.

The last couple of gotchas I've run into for something like this are
making sure you don't have a related Controls collection for the
controls that display your data on the report (e.g. labels with your
text boxes). Secondly, you'll need to play around with the ForceNewPage
property for each section of the report.