Scaling a report preview so that all fields show to the right on one page

[tcarnahan]

I previously posted about a report I created that is based on a SQL Server pivot (pass through) query. As such, based on what is selected before the report retrieves data from the query, my report could have the standard 3 fields on the left and fields 4 through 12 based on the number of fields the pivot query returns.

I already have the code worked out to display or not display controls containing fields 4 through 12, based on whether or not the pivot query returns them. It works very nicely to give me a report with varying number of fields (I hide the controls I don't use on the left of the report).My report is designed to be used in preview and not necessarily printed (probably not).

What I am experiencing looks a lot like what happens if you have a 11 inch paper size in landscape orientation selected but your report is 14 inches wide. The right so many inches of the report "spills over" on the preview to a following page.

I vary the report.width based on the number of fields returned by the query and spacing between fields. In the extreme case where I have 12 fields returned, I get fields 1 through 9 on the first page and fields 10 thru 12 on the following page (minus fields 1 thru 3) when I preview the report.



I use the printer property with .default set to False and I set .PaperSize = acPRPSUser and adjust the width there, but when previewed, fields 10, 11, and 12 still spill over to the following page. When I use .zoom, I just get a smaller version of fields 1 thru 9 on one sheet and 10 thru 12 on the next. I tried using a paper size of 11 x 17 ... it still spills over.

I would like to preview the report and have the visible "paper" outline on the screen vary with the number of fields returned by the pivot query and use zoom to scale the report so that it shows on the screen as fields 1 thru 12 on one page.

I realize this is probably not the right vehicle to use for this presentation, but I already have 95% of it done .... I just need to preview on page on one screen.

Thanks ahead of time for any suggestions you have!

Here is an example of what I see in the preview with 12 fields. In this example, Fields 10, 11, and 12 show no data for these records, but they do return values for other records:

[tcarnahan]

I forgot to mention in my screen print that the one on the left is what I see on page 1 and the next page contains the "spill over". I picked a bad example. If I had shown you page 11 and 12, on the second page, you would have seen the header labels and the data in fields 10,11, & 12.

[June7]

That is code to set the report page layout properties? What procedure is the code in? Post the code.

If there aren't any features that demand a report object (such as grouping) might be easier to program a form.

Do you want to provide db for analysis? Follow instructions at bottom of my post.