Change report width based on total columns widths

  • Thread starter Thread starter heidii
  • Start date Start date
H

heidii

Hello to all:

I have created a report that dynamically hides and unhides all my
columns and changes the sizes of my columns based off a datasheet
subform I have. The code I made works perfect. If I hide columns in
my form then they don't show when I open the report and the rest of my
columns on my report adjust themselves left to remove the gap.

Here is my dilema:

At times, I may only end up choosing say 5 columns on my form and I
have a total of 14 columns, so the report is set up for legal size. I
want to beable to add code to count the columns that are still showing
after my OnFormat code runs and then adjust the page size down to the
width of the total columns. That way it could possibly me printed on
regular paper instead of legal at times.

Any help would be great.

Here is the code I have in my report now:

'this is on the detail section of my report OnFormat event
'there is code that is partly identical in the page header OnFormat
event to handle the labels.
'the LLeft on the page header code is named LLeft2

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim LLeft As Long

LLeft = txt1.Left

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[LOT#].ColumnHidden Then
Me.txt1.Visible = False
Me.txt1.Width = 0
txt1.Left = LLeft
LLeft = LLeft + txt1.Width
Else
Me.txt1.Visible = True
Me.txt1.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![LOT#].ColumnWidth
txt1.Left = LLeft
LLeft = LLeft + txt1.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[RANCH].ColumnHidden Then
Me.txt2.Visible = False
Me.txt2.Width = 0
txt2.Left = LLeft
LLeft = LLeft + txt2.Width
txt3.Left = LLeft
LLeft = LLeft + txt2.Width

Else
Me.txt2.Visible = True
Me.txt2.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![RANCH].ColumnWidth
txt2.Left = LLeft
LLeft = LLeft + txt2.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[WSDACert#].ColumnHidden Then
Me.txt3.Visible = False
Me.txt3.Width = 0
txt3.Left = LLeft
LLeft = LLeft + txt3.Width
txt4.Left = LLeft
LLeft = LLeft + txt3.Width

Else
Me.txt3.Visible = True
Me.txt3.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![WSDACert#].ColumnWidth
txt3.Left = LLeft
LLeft = LLeft + txt3.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[BLOCKTYPE].ColumnHidden Then
Me.txt4.Visible = False
Me.txt4.Width = 0
txt4.Left = LLeft
LLeft = LLeft + txt4.Width
txt5.Left = LLeft
LLeft = LLeft + txt4.Width

Else
Me.txt4.Visible = True
Me.txt4.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![BLOCKTYPE].ColumnWidth
txt4.Left = LLeft
LLeft = LLeft + txt4.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[STATUS].ColumnHidden Then
Me.txt5.Visible = False
Me.txt5.Width = 0
txt5.Left = LLeft
LLeft = LLeft + txt5.Width
txt6.Left = LLeft
LLeft = LLeft + txt5.Width

Else
Me.txt5.Visible = True
Me.txt5.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![STATUS].ColumnWidth
txt5.Left = LLeft
LLeft = LLeft + txt5.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[COMMODITY].ColumnHidden Then
Me.txt6.Visible = False
Me.txt6.Width = 0
txt6.Left = LLeft
LLeft = LLeft + txt6.Width
txt7.Left = LLeft
LLeft = LLeft + txt6.Width

Else
Me.txt6.Visible = True
Me.txt6.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![COMMODITY].ColumnWidth
txt6.Left = LLeft
LLeft = LLeft + txt6.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Block].ColumnHidden Then
Me.txt7.Visible = False
Me.txt7.Width = 0
txt7.Left = LLeft
LLeft = LLeft + txt7.Width
txt8.Left = LLeft
LLeft = LLeft + txt7.Width

Else
Me.txt7.Visible = True
Me.txt7.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Block].ColumnWidth
txt7.Left = LLeft
LLeft = LLeft + txt7.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[WSDA SITE NUMBER].ColumnHidden Then
Me.txt8.Visible = False
Me.txt8.Width = 0
txt8.Left = LLeft
LLeft = LLeft + txt8.Width
txt9.Left = LLeft
LLeft = LLeft + txt8.Width

Else
Me.txt8.Visible = True
Me.txt8.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![WSDA SITE NUMBER].ColumnWidth
txt8.Left = LLeft
LLeft = LLeft + txt8.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[MASTER VARIETY].ColumnHidden Then
Me.txt9.Visible = False
Me.txt9.Width = 0
txt9.Left = LLeft
LLeft = LLeft + txt9.Width
txt10.Left = LLeft
LLeft = LLeft + txt9.Width

Else
Me.txt9.Visible = True
Me.txt9.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![MASTER VARIETY].ColumnWidth
txt9.Left = LLeft
LLeft = LLeft + txt9.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[VARIETY].ColumnHidden Then
Me.txt10.Visible = False
Me.txt10.Width = 0
txt10.Left = LLeft
LLeft = LLeft + txt10.Width
txt11.Left = LLeft
LLeft = LLeft + txt10.Width

Else
Me.txt10.Visible = True
Me.txt10.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![VARIETY].ColumnWidth
txt10.Left = LLeft
LLeft = LLeft + txt10.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Acres].ColumnHidden Then
Me.txt11.Visible = False
Me.txt11.Width = 0
txt11.Left = LLeft
LLeft = LLeft + txt11.Width
txt12.Left = LLeft
LLeft = LLeft + txt11.Width

Else
Me.txt11.Visible = True
Me.txt11.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Acres].ColumnWidth
txt11.Left = LLeft
LLeft = LLeft + txt11.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Planted].ColumnHidden Then
Me.txt12.Visible = False
Me.txt12.Width = 0
txt12.Left = LLeft
LLeft = LLeft + txt12.Width
txt13.Left = LLeft
LLeft = LLeft + txt12.Width

Else
Me.txt12.Visible = True
Me.txt12.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Planted].ColumnWidth
txt12.Left = LLeft
LLeft = LLeft + txt12.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Grafted].ColumnHidden Then
Me.txt13.Visible = False
Me.txt13.Width = 0
txt13.Left = LLeft
LLeft = LLeft + txt13.Width
txt14.Left = LLeft
LLeft = LLeft + txt13.Width

Else
Me.txt13.Visible = True
Me.txt13.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Grafted].ColumnWidth
txt13.Left = LLeft
LLeft = LLeft + txt13.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[LPMA DATE].ColumnHidden Then
Me.txt14.Visible = False
Me.txt14.Width = 0
txt14.Left = LLeft
LLeft = LLeft + txt14.Width

Else
Me.txt14.Visible = True
Me.txt14.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![LPMA DATE].ColumnWidth
txt14.Left = LLeft
LLeft = LLeft + txt14.Width
End If

End Sub
 
I'm doing something similar, but the code is quite lengthy so I don't want
to reproduce it all here.
What you'll need to do is sum up all the widths of the visible columns (be
sure to add in the width of any vertical lines you may have separating the
columns). Compare this width to "letter" width (10.4583 inches for me), and
if it's less than or equal to that change the report's paper size:

Dim rpt As Report
DoCmd.OpenReport rptName, acViewPreview,,,acHidden
Set rpt = Reports(rptname)
'... Sum up report column widths here
If totalWidth <= 10.4583 Then
rpt.Printer.DefaultSize = False
rpt.Printer.ItemSizeWidth = 14400
rpt.Printer.PaperSize = acPRPSLetter
End If
DoCmd.RunCommand acCmdPrint
DoCmd.Close acReport, rptName, acSaveNo

I don't know if there's a better way, but this works for me. There are a lot
of other details to consider - centering the report on the page, for
example - but you can post specific questions in the newsgroup as you
encounter them.

I hope this gets you started.

Carl Rapson

heidii said:
Hello to all:

I have created a report that dynamically hides and unhides all my
columns and changes the sizes of my columns based off a datasheet
subform I have. The code I made works perfect. If I hide columns in
my form then they don't show when I open the report and the rest of my
columns on my report adjust themselves left to remove the gap.

Here is my dilema:

At times, I may only end up choosing say 5 columns on my form and I
have a total of 14 columns, so the report is set up for legal size. I
want to beable to add code to count the columns that are still showing
after my OnFormat code runs and then adjust the page size down to the
width of the total columns. That way it could possibly me printed on
regular paper instead of legal at times.

Any help would be great.

Here is the code I have in my report now:

'this is on the detail section of my report OnFormat event
'there is code that is partly identical in the page header OnFormat
event to handle the labels.
'the LLeft on the page header code is named LLeft2

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim LLeft As Long

LLeft = txt1.Left

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[LOT#].ColumnHidden Then
Me.txt1.Visible = False
Me.txt1.Width = 0
txt1.Left = LLeft
LLeft = LLeft + txt1.Width
Else
Me.txt1.Visible = True
Me.txt1.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![LOT#].ColumnWidth
txt1.Left = LLeft
LLeft = LLeft + txt1.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[RANCH].ColumnHidden Then
Me.txt2.Visible = False
Me.txt2.Width = 0
txt2.Left = LLeft
LLeft = LLeft + txt2.Width
txt3.Left = LLeft
LLeft = LLeft + txt2.Width

Else
Me.txt2.Visible = True
Me.txt2.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![RANCH].ColumnWidth
txt2.Left = LLeft
LLeft = LLeft + txt2.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[WSDACert#].ColumnHidden Then
Me.txt3.Visible = False
Me.txt3.Width = 0
txt3.Left = LLeft
LLeft = LLeft + txt3.Width
txt4.Left = LLeft
LLeft = LLeft + txt3.Width

Else
Me.txt3.Visible = True
Me.txt3.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![WSDACert#].ColumnWidth
txt3.Left = LLeft
LLeft = LLeft + txt3.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[BLOCKTYPE].ColumnHidden Then
Me.txt4.Visible = False
Me.txt4.Width = 0
txt4.Left = LLeft
LLeft = LLeft + txt4.Width
txt5.Left = LLeft
LLeft = LLeft + txt4.Width

Else
Me.txt4.Visible = True
Me.txt4.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![BLOCKTYPE].ColumnWidth
txt4.Left = LLeft
LLeft = LLeft + txt4.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[STATUS].ColumnHidden Then
Me.txt5.Visible = False
Me.txt5.Width = 0
txt5.Left = LLeft
LLeft = LLeft + txt5.Width
txt6.Left = LLeft
LLeft = LLeft + txt5.Width

Else
Me.txt5.Visible = True
Me.txt5.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![STATUS].ColumnWidth
txt5.Left = LLeft
LLeft = LLeft + txt5.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[COMMODITY].ColumnHidden Then
Me.txt6.Visible = False
Me.txt6.Width = 0
txt6.Left = LLeft
LLeft = LLeft + txt6.Width
txt7.Left = LLeft
LLeft = LLeft + txt6.Width

Else
Me.txt6.Visible = True
Me.txt6.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![COMMODITY].ColumnWidth
txt6.Left = LLeft
LLeft = LLeft + txt6.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Block].ColumnHidden Then
Me.txt7.Visible = False
Me.txt7.Width = 0
txt7.Left = LLeft
LLeft = LLeft + txt7.Width
txt8.Left = LLeft
LLeft = LLeft + txt7.Width

Else
Me.txt7.Visible = True
Me.txt7.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Block].ColumnWidth
txt7.Left = LLeft
LLeft = LLeft + txt7.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[WSDA SITE NUMBER].ColumnHidden Then
Me.txt8.Visible = False
Me.txt8.Width = 0
txt8.Left = LLeft
LLeft = LLeft + txt8.Width
txt9.Left = LLeft
LLeft = LLeft + txt8.Width

Else
Me.txt8.Visible = True
Me.txt8.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![WSDA SITE NUMBER].ColumnWidth
txt8.Left = LLeft
LLeft = LLeft + txt8.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[MASTER VARIETY].ColumnHidden Then
Me.txt9.Visible = False
Me.txt9.Width = 0
txt9.Left = LLeft
LLeft = LLeft + txt9.Width
txt10.Left = LLeft
LLeft = LLeft + txt9.Width

Else
Me.txt9.Visible = True
Me.txt9.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![MASTER VARIETY].ColumnWidth
txt9.Left = LLeft
LLeft = LLeft + txt9.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[VARIETY].ColumnHidden Then
Me.txt10.Visible = False
Me.txt10.Width = 0
txt10.Left = LLeft
LLeft = LLeft + txt10.Width
txt11.Left = LLeft
LLeft = LLeft + txt10.Width

Else
Me.txt10.Visible = True
Me.txt10.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![VARIETY].ColumnWidth
txt10.Left = LLeft
LLeft = LLeft + txt10.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Acres].ColumnHidden Then
Me.txt11.Visible = False
Me.txt11.Width = 0
txt11.Left = LLeft
LLeft = LLeft + txt11.Width
txt12.Left = LLeft
LLeft = LLeft + txt11.Width

Else
Me.txt11.Visible = True
Me.txt11.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Acres].ColumnWidth
txt11.Left = LLeft
LLeft = LLeft + txt11.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Planted].ColumnHidden Then
Me.txt12.Visible = False
Me.txt12.Width = 0
txt12.Left = LLeft
LLeft = LLeft + txt12.Width
txt13.Left = LLeft
LLeft = LLeft + txt12.Width

Else
Me.txt12.Visible = True
Me.txt12.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Planted].ColumnWidth
txt12.Left = LLeft
LLeft = LLeft + txt12.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[Grafted].ColumnHidden Then
Me.txt13.Visible = False
Me.txt13.Width = 0
txt13.Left = LLeft
LLeft = LLeft + txt13.Width
txt14.Left = LLeft
LLeft = LLeft + txt13.Width

Else
Me.txt13.Visible = True
Me.txt13.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![Grafted].ColumnWidth
txt13.Left = LLeft
LLeft = LLeft + txt13.Width
End If

If Forms![FRM-RANCH_INFO_SEARCH]![QRY-RANCH_INFO_RANCH_VIEW1].Form!
[LPMA DATE].ColumnHidden Then
Me.txt14.Visible = False
Me.txt14.Width = 0
txt14.Left = LLeft
LLeft = LLeft + txt14.Width

Else
Me.txt14.Visible = True
Me.txt14.Width = Forms![FRM-RANCH_INFO_SEARCH]![QRY-
RANCH_INFO_RANCH_VIEW1].Form![LPMA DATE].ColumnWidth
txt14.Left = LLeft
LLeft = LLeft + txt14.Width
End If

End Sub
Click to expand...
 
heidii said:
thanks for the reply.

I am not sure though how to sum up the visible columns

heidi
Click to expand...

Similar to what you're doing already. In your code, for each visible column
add the width to a variable. After the last column, compare that variable to
the "standard" report width (10.4583 for me but yours might be different;
you'll have to experiment) and if it's less than or equal, change the report
paper size like I showed.

One thing I might point out, I'm doing all of my report formatting from my
form rather than within the report. I don't know if that will make any
difference, but I do know that it works for me. Also, I'm using the actual
width of each column in the report itself rather than the widths of controls
on a form. What I did was create a lookup table containing each possible
report column, its starting (Left) position, and width. Then, for each
column I want visible on the report (specified in a listbox on my form), I
sum the widths to get the total report width. As I said, the code is hard to
reduce to a snippet, or I'd be happy to give some examples.

As I mentioned in the first post, there are a lot of details to be
considered. One example: let's say you have 3 columns on your report (col1,
col2, col3) and you want to hide col2. Do you want col3 to be shifted to the
left to be next to col1? If so, you'll need to adjust the Left property of
col3 (and any remaining visible colmns as well). Are there vertical lines
separating your columns? If so, you'll need to shift them around as well. I
do see that you've considered labels, which is good.

Carl Rapson
 
Back
Top