Report Layout

[access freshman]

I have created a report in MS Access. The report looks much like an excel
spreadsheet. Every text box in the Detail section is set to CanGrow.

My problem is when the largest text box grows the other text boxes in the
same row do not grow to the same size. I cannot just draw lines around the
the text boxes and grow them b/c each text box is conditionally formatted.

How can I set the entire detail section to adjust to the same height
according to the largest text box in the section?

 

[Duane Hookom]

If you are concerned only about the borders, you can use the line method to
draw all borders based on the height of the tallest text box. This code
assumes each control in your detail section that you want to 'border' has
'Border' in its tag property:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intMaxHeight As Integer
Dim ctl As Control
'Find highest control in Detail section _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
If ctl.Height > intMaxHeight Then
intMaxHeight = ctl.Height
End If
End If
Next
'Draw a box around each control in Detail _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), , B
End If
Next
End Sub

If you expect background colors of controls to also grow, this solution
won't provide that.

 

[access freshman]

Duane,
Thanks for the response. I do want my background colors to grow as well. Is
there a solution for that?

 

[Duane Hookom]

You aren't asking for much ;-)
You can try remove the conditional coloring of backgrounds and use code. I
have modified my code to check a text box named "City" for the value
"London". I want these text boxes to have a background color of pale yellow.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intMaxHeight As Integer
Dim ctl As Control
Dim lngHilite As Long
lngHilite = 10092543 'pale yellow
'Find highest control in Detail section _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
If ctl.Height > intMaxHeight Then
intMaxHeight = ctl.Height
End If
End If
Next
'Draw a box around each control in Detail _
that has a tag property of "Border"
For Each ctl In Me.Section(0).Controls
If ctl.Tag = "Border" Then
'replace conditional formatting making the _
background of City=London pale yellow
If ctl.Name = "City" And ctl.Value = "London" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), lngHilite, BF
End If
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), vbBlack, B

End If
Next
End Sub

 

[access freshman]

Duane,
Everything is working except the color. When I run the report, the entire
box for "City" shows up black.

 

[Duane Hookom]

Check your code vs mine sample. I copied and pasted directly from my working
sample.

 

[access freshman]

This time I copied and pasted...it works flawlessly. Thank you so much.
Apparently, I had my indents wrong.

 

[access freshman]

Now, how do I do this for other text boxes? I have more than one "City" that
I need to shade.

 

[Duane Hookom]

Just add more sections of code like:
If ctl.Name = "City" And ctl.Value = "London" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), lngHilite, BF
End If

 

[access freshman]

Everything works...you rock. Thanks

Just add more sections of code like:
If ctl.Name = "City" And ctl.Value = "London" Then
Me.Line (ctl.Left, ctl.Top)- _
Step(ctl.Width, intMaxHeight), lngHilite, BF
End If