change column width

[Sam H]

I have a report with labels on the left margin. Details on
how to create the report are on the website :
http://support.microsoft.com/default.aspx?scid=kb;en-
us;210044
However, unlike the example, I want to have my labels to
have a 2" width while my controls to be 1" wide. This
difference in width, unlike the equal width Microsoft
example, is to be controlled by the OnFormat property of
the Detail section. So far I have the following code but
something to allow seperate label and control width is
missing. Please help!
Dim i As Integer
If Me.Left < Me.Width Then
Me.NextRecord = False
For i = 1 To 21
Me("txt" & i).Visible = False
Me("lbl" & i).Visible = True
Next i
Else
For i = 1 To 21
Me("txt" & i).Visible = True
Me("lbl" & i).Visible = False
Next i
End If
Thanks,
Sam

 

[Sam H]

Hi Marsh,
Unfortunately I have more records then columns. If its
not too much effort could you please post your code. Let
me play with it a bit and see if I get anywhere. I'll be
sure to create a backup of my database firt though
Thanks,
Sam

 

[Marshall Barton]

Hi Marsh,
Unfortunately I have more records then columns. If its
not too much effort could you please post your code. Let
me play with it a bit and see if I get anywhere. I'll be
sure to create a backup of my database firt though

OK, you can have it, such as it is. The set up for the code
is pretty much what you had for the KB article's technique,
just make sure the label controls are invisible, set both
the label's and the text box's Left property to 0 and set
the column width to the 1/2 inch for the data columns. Like
I said before make sure that all controls and the detail
section have their CanGrow/CanShrink properties set to No
and the detail section's KeepTogether property is set to
Yes.

You didn't say what version of Access you're using so, if
you're not using AXP, you will have to change the lines of
code that refer to the report's Printer object to refer to
literal values instead.

Depending on what else you have going on in the rest of the
report, you may want to move some of the initialization
parts of the code to the report's Open event and/or header
format events.

The basic idea of the code is to skip the two left columns
before displaying the data starting in column three. Then,
the code in the Page event *tries* to go back and print an
entire page's worth of labels using the report's Print
method.

-----------Watch out for line wrapping-------------
Private DetailTops(50) As Long
Private k As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
Dim ctl As Control
Dim lngColWdth As Long
Dim lngMargin As Long

lngMargin = Me.Printer.LEFTMARGIN
lngColWdth = Me.Printer.ItemSizeWidth

If Me.Left < lngMargin + lngColWdth Then
For Each ctl In Me.Controls 'First column
If ctl.Section = 0 And ctl.ControlType =
acTextBox Then
ctl.Visible = False
End If
Next ctl
DetailTops(k) = Me.Top
k = k + 1
Me.NextRecord = False
Me.PrintSection = False
ElseIf Me.Left < lngMargin + 2 * lngColWdth Then
'Second column
Me.NextRecord = False
Me.PrintSection = False
ElseIf Me.Left < lngMargin + 3 * lngColWdth Then 'Third
column
For Each ctl In Me.Controls
If ctl.Section = 0 And ctl.ControlType =
acTextBox Then
ctl.Visible = True
End If
Next ctl
End If

End Sub

Private Sub Report_Page()
Dim ctl As Control
Dim i As Integer
Dim lngTopMargin As Long

lngTopMargin = Me.Printer.TopMargin

For i = 0 To k - 1
For Each ctl In Me.Controls
If ctl.Section = 0 And ctl.ControlType = acLabel
Then
Me.FontName = ctl.FontName
Me.FontSize = ctl.FontSize
Me.FontBold = (ctl.FontBold = 1)
Me.CurrentX = ctl.Left
Me.CurrentY = (DetailTops(i) - lngTopMargin)
+ ctl.Top
Me.Print ctl.Caption
End If
Next ctl
Next i
k = 0
End Sub