Horizontal lines that grow with text

[kcolbert01]

I have used code to create a horizontal and vertical lines on a report to create a speadsheet like effect. The vertical lines are set according to the width of the text fields across the top of the page (sort of a column like effect) so they never change. The horizontal lines, however, grow with the text box and continues across the page thus creating the spreadsheet like effect that I am looking for. I created the horizontal lines this way (insteat of just putting a line around the text box) because I need the line to go across the page.

The only problem I am having is making the line not be created if the text box has no data. Any thoughts or ideas would be helpful - this problem is driving me mad. I have been trying to outsmart this program for 2 days!!!

Here is the code:

Dim X1 As Single, Y1 As Single
Dim X2 As Single, Y2 As Single
Dim X3 As Single, Y3 As Single
Dim X4 As Single, Y4 As Single
Dim Offset As Single
Dim Color As Long

' Specify unit of measurement for coordinates on a page...
Me.ScaleMode = 1 ' ...in twips (1440 twips = 1 inch).

' Define an offset of 1/8 inch from the text box to the rectangle.
Offset = 1440 / 40

' X and Y coordinates for the top left corner of the box.
X1 = (Me![12a].Left - Offset) * 14400
Y1 = Me![12a].Top - Offset
X3 = (Me![12b].Left - Offset) * 14400
Y3 = Me![12b].Top - Offset

' X and Y coordinates for the bottom right corner of the box.
X2 = (Me![12a].Left + Me![12a].Width + Offset) * 14400
Y2 = Me![12a].Top + Me![12a].Height + Offset
X4 = (Me![12b].Left + Me![12b].Width + Offset) * 14400
Y4 = Me![12b].Top + Me![12b].Height + Offset

Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.
' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
Me.Line (X3, Y3)-(X4, Y4), Color, B


Now I need a way to tell it to NOT draw the line if there is no data.
Thanks in advance for your help!

 

[ducker]

couldn't you check to see if the field was empty and skip the horizontal line?

 

[RuralGuy]

If Len([Field1] & [Field2] & [Field3]) > 0 Then
'-- Draw the line
End If

 

[ducker]

yea something like that

 

[kcolbert01]

ok - where exactly would I put that code? Please be very specific as I am new to this...

Thanks!

 

[RuralGuy]

Where exactly do you have the code to draw the line?

 

[kcolbert01]

the code is on the OnPrint of a group header. I've been experimenting and it is currently there like this:

If Len([12b] & [12c] & [12d] & [12e] & [12f] & [12g] & [12h]) > 0 Then

' The 14400 is an arbitrary number to increase the line
' to the max of a section.
Dim X1 As Single, Y1 As Single
Dim X2 As Single, Y2 As Single
Dim X3 As Single, Y3 As Single
Dim X4 As Single, Y4 As Single
Dim X5 As Single, Y5 As Single
Dim X6 As Single, Y6 As Single
Dim X7 As Single, Y7 As Single
Dim X8 As Single, Y8 As Single
Dim X9 As Single, Y9 As Single
Dim X10 As Single, Y10 As Single
Dim X11 As Single, Y11 As Single
Dim X12 As Single, Y12 As Single
Dim X13 As Single, Y13 As Single
Dim X14 As Single, Y14 As Single
Dim X15 As Single, Y15 As Single
Dim X16 As Single, Y16 As Single
Dim Offset As Single
Dim Color As Long

' Specify unit of measurement for coordinates on a page...
Me.ScaleMode = 1 ' ...in twips (1440 twips = 1 inch).

' Define an offset of 1/8 inch from the text box to the rectangle.
Offset = 1440 / 40

' X and Y coordinates for the top left corner of the box.
X1 = (Me![12a].Left - Offset) * 14400
Y1 = Me![12a].Top - Offset
X3 = (Me![12b].Left - Offset) * 14400
Y3 = Me![12b].Top - Offset
X5 = (Me![12c].Left - Offset) * 14400
Y5 = Me![12c].Top - Offset
X7 = (Me![12d].Left - Offset) * 14400
Y7 = Me![12d].Top - Offset
X9 = (Me![12e].Left - Offset) * 14400
Y9 = Me![12e].Top - Offset
X11 = (Me![12f].Left - Offset) * 14400
Y11 = Me![12f].Top - Offset
X13 = (Me![12g].Left - Offset) * 14400
Y13 = Me![12g].Top - Offset
X15 = (Me![12h].Left - Offset) * 14400
Y15 = Me![12h].Top - Offset


' X and Y coordinates for the bottom right corner of the box.
X2 = (Me![12a].Left + Me![12a].Width + Offset) * 14400
Y2 = Me![12a].Top + Me![12a].Height + Offset
X4 = (Me![12b].Left + Me![12b].Width + Offset) * 14400
Y4 = Me![12b].Top + Me![12b].Height + Offset
X6 = (Me![12c].Left + Me![12c].Width + Offset) * 14400
Y6 = Me![12c].Top + Me![12c].Height + Offset
X8 = (Me![12d].Left + Me![12d].Width + Offset) * 14400
Y8 = Me![12d].Top + Me![12d].Height + Offset
X10 = (Me![12e].Left + Me![12e].Width + Offset) * 14400
Y10 = Me![12e].Top + Me![12e].Height + Offset
X12 = (Me![12f].Left + Me![12f].Width + Offset) * 14400
Y12 = Me![12f].Top + Me![12f].Height + Offset
X14 = (Me![12g].Left + Me![12g].Width + Offset) * 14400
Y14 = Me![12g].Top + Me![12g].Height + Offset
X16 = (Me![12h].Left + Me![12h].Width + Offset) * 14400
Y16 = Me![12h].Top + Me![12h].Height + Offset

Me.DrawWidth = 3 ' Width of the line (in pixels).
Color = RGB(0, 0, 0) ' Use black line color.

' Draw the rectangle with the Line method.
Me.Line (X1, Y1)-(X2, Y2), Color, B
Me.Line (X3, Y3)-(X4, Y4), Color, B
Me.Line (X5, Y5)-(X6, Y6), Color, B
Me.Line (X7, Y7)-(X8, Y8), Color, B
Me.Line (X9, Y9)-(X10, Y10), Color, B
Me.Line (X11, Y11)-(X12, Y12), Color, B
Me.Line (X13, Y13)-(X14, Y14), Color, B
Me.Line (X15, Y15)-(X16, Y16), Color, B

Else
Me![12b].Visible = False

End If

but it's still not quite right. any ideas?

 

[RuralGuy]

You will probably need to set the visibility of all of the fields to false or true depending on the Len() test since you do not know which of the fields had a Len > 0 with this code. What does "but it's still not quite right" mean?

 

[kcolbert01]

What I meant by "not quite right" was that the lines were still printing whether there was text in the fields or not. I am still playing around with it.

I don't mean to be so basic (I am self taught) but what does the "Len() test" mean?

 

[RuralGuy]

Have you tried looking up Len() in VBA help?

 

[kcolbert01]

will do thanks.

I finally got it to do what I needed it to do.....I had to do the custom lines per text field and not just all together at once.

 

[RuralGuy]

Outstanding! Glad to hear you got things working the way you wanted.

 

[binoj]

Do you still remember how you did this? I am in a similar situation. I have a report in which the text controls are aranged horizontally one below the other. These text contols can grow and shrink. I want to draw a line below each control that would move down or up as the control grows or shrinks.

Can you please help me with this? I am a newbie to access.

Thanks,
Binoj.

will do thanks.

I finally got it to do what I needed it to do.....I had to do the custom lines per text field and not just all together at once.