Can Grow Property in Report

[Todd C]

I am creating a report and have about twelve fields.
Each field with the exception of one are text fields. The other is a memo
field.

Each field is bordered in Black. I have set each fields "Can Grow" property
to Yes.

The problem however is that when the memo field size changes and uses the
extra space, the other fields stay the same.

Is there any possible way to make it so that each field height stays the
same as everyother one. Meaning, when the memo field enlarges, the other
fields follow suite. I really hope there is, cause without this working, all
the data looks jumbled

 

[Ken Sheridan]

Todd:

You can do it by drawing a line around each control at runtime using the
Line method of the report object in the detail section's print event
procedure. Here's the code for the report's module:

''''module starts''''
Option Compare Database
Option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim lngHeight As Long
Dim X1 As Single
Dim Y1 As Single
Dim Y2 As Single
Dim X2 As Single

Me.DrawStyle = 0

' Find the height of the tallest control in the row
Dim C As Control
For Each C In Me.Section(0).Controls
If C.Height > lngHeight Then
lngHeight = C.Height
End If
Next C

If PrintCount = 1 Then
' Draw the box around the record
For Each C In Me.Section(0).Controls
X1 = C.Left
X2 = C.Left + C.Width
Y1 = C.Top
' add twenty twips at bottom to
' avoid tight cropping to text
Y2 = lngHeight + 20
Me.Line (X1, Y1)-(X2, Y2), vbBlack, B
Next C

' Call the DrawLine and DrawBox procedures
' The third (lngHeight) argument should be
' more than the maximum possible height of
' the section
DrawLine 3400, 0, 3400, 10000, 5
End If

End Sub

Sub DrawLine(lngLeft As Long, _
lngTop As Long, _
lngRight As Long, _
lngHeight As Long, _
intLineWidth As Integer)

Dim lngColor As Long

' Set scale to twips
Me.ScaleMode = 1
' Set line width in pixels
DrawWidth = intLineWidth
' Make colour black
lngColor = RGB(0, 0, 0)
' Draw line at specified distance from left margin
Me.Line (lngLeft, lngTop)-(lngRight, lngHeight), lngColor

End Sub
''''module ends''''

Leave each control's BorderStyle property as Transparent, as the border is
drawn by the above code.

Ken Sheridan
Stafford, England

 

[Fred]

This is probably a stupid question, but if one of these other fields doesn't
have enough content to use it's "can grow" freedom, what would having it also
"grow" do? Wouldn't it's visible size just be that needed to print it's
contents?

 

[Todd C]

Hey thanks Ken. Now for the more in depth question. Im pretty savvy with
coding, of course that only because I have used the code at one time or
another. This code however, I have never tried, so......
I pretty much understand it. There are just a few I do not.

Me.Section(0).Controls. ?
Me.Line ?
If I named one of my boxes "BXDivision", where would I put that in the code.
Now since I have about 12 Boxes that all need to change, where in the code
would I put them? Ill be playing around with it, just to understand it more,
but I would appreciate the help. Makes things go a little faster.

Thanks again Ken.

 

[Ken Sheridan]

Todd:

Firstly, just to be clear that, in the context of Fred's response, we are
singing from the same hymn sheet, I'm assuming it’s the boxes around the
controls you want to grow to the same height as the tallest in each row,
right? That's what the code does.

1. Me.Section(0).Controls refers to the Controls collection of the report's
Detail section. You could also refer to it using the built in acDetail
constant with Me.Section(acDetail).Controls. This allows the code to lop
through each control in the section and draw a box around them. In the code
as written it assumes every control will be 'boxed', but you might in some
circumstances only want only text boxes to be 'boxed', excluding labels for
instance, in which case you could amend the code so it skips all but text
boxes:

Option Compare Database
Option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim lngHeight As Long
Dim X1 As Single
Dim Y1 As Single
Dim Y2 As Single
Dim X2 As Single

Me.DrawStyle = 0

' Find the height of the tallest control in the row
Dim C As Control
For Each C In Me.Section(acDetail).Controls
If C.Height > lngHeight Then
lngHeight = C.Height
End If
Next C

If PrintCount = 1 Then
' Draw the box around the record
For Each C In Me.Section(acDetail).Controls
If C.ControlType = acTextBox Then
X1 = C.Left
X2 = C.Left + C.Width
Y1 = C.Top
' add twenty twips at bottom to
' avoid tight cropping to text
Y2 = lngHeight + 20
Me.Line (X1, Y1)-(X2, Y2), vbBlack, B
End If
Next C

End If

End Sub

You'll have noticed that I've also removed the DrawLine procedure and the
line calling it from the above. That in fact draws a continuous vertical
line down the page between two controls, so is not something you need.


2. Me.Line calls the Line method of the report object. This draws a line
or a box on the report at the co-ordinates specified The first two in
parentheses (X1, Y1) specify where the box starts at the top left position,
and the second two (X2, Y2) where it ends at the bottom right position.

Ken Sheridan
Stafford, England

Hey thanks Ken. Now for the more in depth question. Im pretty savvy with
coding, of course that only because I have used the code at one time or
another. This code however, I have never tried, so......
I pretty much understand it. There are just a few I do not.

Me.Section(0).Controls. ?
Me.Line ?
If I named one of my boxes "BXDivision", where would I put that in the code.
Now since I have about 12 Boxes that all need to change, where in the code
would I put them? Ill be playing around with it, just to understand it more,
but I would appreciate the help. Makes things go a little faster.

Thanks again Ken.

 

[Larry Linson]

If you use a non-transparent border on the controls, they look messy if not
all the same height, and the developer appears to be a "slob" instead of a
"professional"*. One answer is to use no border, but use a line as the last
control in the detail section to separate it visually from the next detail.

Larry Linson
Microsoft Office Access MVP

* Even though some may think I am a "messy slob" instead of an
"organized professional", I wouldn't want to demonstrate that to
the world with a "messy, slobby" looking report.

 

[Fred]

Ooops......I remembered reading "transparent border" somewhere in the post
and mistakenly assumed this was a case of no borders.