Changing Report Width with code

[lulugrant_is_a_dog]

I have a report in which the columns are hidden or shown depending on
whether the corresponding checkboxes on a form are checked. I want to
be able to add up the width of all the columns shown and change the
width of the report (so that it could fit on an A4 page).

Here is the code that is run upon opening the report:
---------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
Dim position As Long
Dim chkno As Object
position = 0.68
For K = 1 To 16
strChkNo = "chk" & K
If Forms![frmAdvancedSearch].Controls(strChkNo) = True Then
Me("txt" & K).Left = position
Me("txt" & K).Visible = True
Me("lbl" & K).Left = position
Me("lbl" & K).Visible = True
position = position + Me("txt" & K).Width
Else
Me("txt" & K).Left = 0
Me("lbl" & K).Left = 0
Me("txt" & K).Width = 0
Me("lbl" & K).Width = 0
End If
Next K

'sets the width of the report based on adding up the columns shown and
an extra 0.68 margin
Me.Width = position + 0.68

End Sub
---------------------------------------------------------------------
I know this isn't setting the width of the report, and I think it is
because I have addressed it wrong (Should "Me.Width" be something
else?)

Any help would be greatly appreciated

 

[Marshall Barton]

I have a report in which the columns are hidden or shown depending on
whether the corresponding checkboxes on a form are checked. I want to
be able to add up the width of all the columns shown and change the
width of the report (so that it could fit on an A4 page).

Here is the code that is run upon opening the report:
---------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
Dim position As Long
Dim chkno As Object
position = 0.68
For K = 1 To 16
strChkNo = "chk" & K
If Forms![frmAdvancedSearch].Controls(strChkNo) = True Then
Me("txt" & K).Left = position
Me("txt" & K).Visible = True
Me("lbl" & K).Left = position
Me("lbl" & K).Visible = True
position = position + Me("txt" & K).Width
Else
Me("txt" & K).Left = 0
Me("lbl" & K).Left = 0
Me("txt" & K).Width = 0
Me("lbl" & K).Width = 0
End If
Next K

'sets the width of the report based on adding up the columns shown and
an extra 0.68 margin
Me.Width = position + 0.68

End Sub

That is the correct syntax, however the usable space is
limited to what was specified at design time (don't ask me
why).

You should design the report with its width set to the paper
width less left and right margins so you don't need to mess
with it.