Multicolumn report with vertical labels

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angeldb - 29 May 2006 04:49 GMT

I've got a 4-column report that must have vertical labels on the left side of
the report in between the header and footer of the report on each page. I've
got group labels for labels of the text boxes which display the data from my
query. It looks something like this:

Label1 Text1 .... ....
Label2 Text2 .... ....
GP1 Label3 Text3 .... ....
Label4 Text4 .... ....
....

The best I can do is setup the text box columns in a subreport set up to do
4 columns on each page. The subreport's CanGrow setting has to be set to Yes,
otherwise I don't get all the records. However, the number of child records
is greater then 4. So I get subsequent pages without labels up until the
Parent/Child is a different value.

I've tried Knowledgebase Article Q210044 on how to print labels on the left
side of a multicolumn report, and it would work if my labels are the same
size as my text boxes. But this is not the case since I have a Group label as
previously described. Can this code be written differently to accomodate a
differently-sized label column.

Please do not suggest using Group Headers. I've tried this and setting the
NewRowOrColumn/KeepTogether/CanGrow/CanShrink properties of the Group header
and detail section in different combinations to no avail.

I hope somebody can help me out there. I've been searching in these forums
for the last 5 days trying each possible solution.

Reply to this Message

Marshall Barton - 31 May 2006 05:22 GMT

>I've got a 4-column report that must have vertical labels on the left side of
>the report in between the header and footer of the report on each page. I've
[quoted text clipped - 18 lines]
>previously described. Can this code be written differently to accomodate a
>differently-sized label column.

I put together a variation of the KB article's approach with
the event procedures that allow you to use a multiple of the
column width for your labels.

Make the labels Width small enough to fit within the column
width (regardless of the length of their caption) and make
them all invisible.

Here's a copy of the report's module. Watch out for line
wrapping.
-------------------------------------------------------
Option Compare Database
Option Explicit

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
End If
            ' 3 columns for labels
If Me.Left < lngMargin + 3 * lngColWdth Then '2+ column
Me.NextRecord = False
Me.PrintSection = False
            ' text boxes start in 4th column
ElseIf Me.Left < lngMargin + 4 * lngColWdth Then
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

Signature

Marsh
MVP [MS Access]

Reply to this Message