Multiple Column Report With Varying Column Widths

[Guest]

Does anyone know if you can mess with the formatting of a report using VBA
when it loads to modify the widths its columns?

I have a report that I have set up that has four columns on it. The first
column has labels that identify what can be found in that row. The
subsequent three columns then have numbers that are test results. As you can
imagine the labels require many more characters than the results so if I
could change the width of column 2, 3 & 4 I could get more results on a page
and that would be great!

Any help would be greatly appreciated.

 

[strive4peace]

Why do you need to modify the column widths when the report runs? Why
can't you just modify the basic report design? Will the width of any
column need to change in the future? If so, why?

Is your report truly in columns (Page Layout) or is the data displayed
in columns (Columnar Report)?


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[RoyVidar]

Does anyone know if you can mess with the formatting of a report
using VBA when it loads to modify the widths its columns?

I have a report that I have set up that has four columns on it. The
first column has labels that identify what can be found in that row.
The subsequent three columns then have numbers that are test
results. As you can imagine the labels require many more characters
than the results so if I could change the width of column 2, 3 & 4 I
could get more results on a page and that would be great!

Any help would be greatly appreciated.

I think you can play with the Width property of the controls, both
in the reports on open event, and the on format event of the section
in which the controls reside.

Try for instance making the control very small, then use something
like this in the forms on open event

Me!txtNameOfFirstControl.Width = 2500

Or in the on format event of the Detail section

Me!txtNameOfFirstControl.Width = _
Len(Me!txtNameOfFirstControl.Value & vbnullstring) * 100

To play with the left position of the control - i e where it "starts",
try playing with the .Left property of the control (more detail
section=

Me!txtNameOfSecondControl.Left = Me!txtNameOfFirstControl.Width _
+ Me!txtNameOfControl.Left + 100

Note that the measures are in Twips (567 twips per cm, 1440 twips
per inch).

I think that if you wish to do something like this in the reports
on open event, you'll probably need to open a recordset based on the
recordsource of the report, loop it, and find the largest text, then
apply some calculations. Here is some more air code.

dim rs as dao.recordset
dim lngLen as long
const clngAdd as long = 100 ' factor

set rs = currentdb.openrecordset(me.recordcource)
do while not rs.eof
if len(rs.fields("NameOfField").value) > lngLen then
lngLen = len(rs.fields("NameOfField").value)
end if
.movenext
loop
rs.close
set rs = nothing

Me!txtNameOfFirstControl.Width = lngLen * clngAdd
Me!txtNameOfSecondControl.Left = Me!txtNameOfFirstControl.Width _
+ Me!txtNameOfControl.Left + 100

I've only used approximates like this, when the need arose. For more
precise measuring, if needed, here are a couple of links (watch for
linebreaks)
http://www.lebans.com/textwidth-height.htm
http://groups.google.com/group/microsoft.public.vb.winapi/browse_frm/thread/a7bcbe490121763d/

 

[Guest]

I appreciate both of your responses and realize that I should have made my
original post more clear.

The report that I have has 4 columns 1.875" wide created in the Page Layout
(Setup). I used the Across Then Down version. I also used some code that
looks at the position of control and its type to make the labels not visible
after the first column's width (If Me.Left > (1.878 * 1440) And
ctlMyCtl.ControlType = acLabel Then ctlMyCtl.Visible = False).

The labels have the name of the test result and must be up to 1.875" wide.
I then use text box controls that have the actual data in them in the
subsequent 3 columns. The result are a maximum of 0.75" wide so if I could
change the width of column 2, 3 and 4 from the Page Setup value of 1.878" to
a programmatically entered value of 0.75" I could add one or two more columns
on each page thereby reducing the wasted space on the page.

I could not find anything about a collection that refers to the "Columns" in
any of the help documentation.

Vidar, I appreciate your time putting in the detail you did and understand
what you are trying to do (I think) but because I was not specific enough
with my initial post I think we may be talking about two different report
formats, correct?

 

[RoyVidar]

I appreciate both of your responses and realize that I should have
made my original post more clear.

The report that I have has 4 columns 1.875" wide created in the Page
Layout (Setup). I used the Across Then Down version. I also used
some code that looks at the position of control and its type to make
the labels not visible after the first column's width (If Me.Left >
(1.878 * 1440) And ctlMyCtl.ControlType = acLabel Then
ctlMyCtl.Visible = False).

The labels have the name of the test result and must be up to 1.875"
wide. I then use text box controls that have the actual data in
them in the subsequent 3 columns. The result are a maximum of 0.75"
wide so if I could change the width of column 2, 3 and 4 from the
Page Setup value of 1.878" to a programmatically entered value of
0.75" I could add one or two more columns on each page thereby
reducing the wasted space on the page.

I could not find anything about a collection that refers to the
"Columns" in any of the help documentation.

Vidar, I appreciate your time putting in the detail you did and
understand what you are trying to do (I think) but because I was not
specific enough with my initial post I think we may be talking about
two different report formats, correct?

You're right, I haven't the foggiest what you're talking about ;-)

If you don't get any answers here, I'd suggest reposting to
microsoft.public.access.reports

 

[strive4peace]

It appears that you are doing things the hard way...

Are you reporting from a table that has it's information stored
vertically? If so, the wisest thing you could do is loop through it and
convert it to a horizontal format with label and values on the same row.

If you need help with code to do this, please share more about your data
structure.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*