Use VBA to scale a report's dimensions

[fritz]

I need a VBA function that will reduce (downscale) all the dimensions of a report.
I create reports with so many horizontal fields (controls), that they do not fit into the width of a standard A4 page.
The result is that Access prints them on two pages wide.
What I do manually is to create the report in a readable size until it displays the data correctly.
Then I select all items in the report manually and reduce their widths and heights by a percentage.
Then I reduce the fonts to an applicable size to fit the new size of controls.
The controls can then be moved left and closer together, to create free space on the right.
Now I can reduce the page width to within paper limits.
Then I compact sections vertically, so that the result is a nicely compressed report.
Access then renders the form one page wide,
Can anyone help me with the code for this tedious manual process please.
Thanks

 

[Guus2005]

I believe you're describing a .NET functionality.
Are you sure you want to do it in runtime?

In designtime you can select the controls you want to change the size of and press ctrl-shift-arrowkeys to increase or decrease it. Using the control key decreases the step size of each time you press the arrowkey.
Using the formatting tools align, justify, etc you can get the controls where you want.
Apart from that, you can set the property CanGrow of each textbox so that when the text doesn't fit, the textbox will grow downwards to make room for more text.

I've seen a api call which calculate the amount of space needed depending on the chosen fonttype. You can search the web for such functionality.

Imo it is better, easier, faster to create a static report.

HTH

 

[fritz]

Thanks Guus2005,

What I do manually (as described) is in design time in Access report designer and I would think that the "desired code" will also work like that. In access you design the report format once and then store it as a named report. Whenever you open it again, it acquires new relevant data from the database and displays it.

I am interested in what you call a "static report". As I understand Access only have static reports, at least as default. Please I am not an expert and wish to learn, so please elaborate. I know nill of .net .

I must add that I need to email the reports from out of the Access application.

 

[Guus2005]

Altough i advice against it, it is possible to alter the appearance of a report at runtime thus creating a dynamic report. Because your report will then be data-driven maintenance will be quite complicated as i described earlier.

It is better to create static reports. It the data doesn't fit the textbox use the cangrow property of the textbox and that is the dynamic part of a static report. It happens automatically and doesn't require maintenance.

Two things you can do imo:
create a

1. report with all textboxes neatly aligned and all use the cangrow property.
2. multiline report with all textboxes neatly aligned as wide as needed and continue on the following line.

Enjoy!