Create dynamic border around the Detail of a report that shrinks and grows

[Eddy Sincere]

I want to have a border around the entire Detail section of a report that shrinks and grows based on the data. I found the Draw Line VBA but am not sure how to make it specific to the Detail section. (I don't have a "Notes" field.) I attached a sample DB with the VBA in it. The VBA is in the Event tab, Detail, On Print command.

Any direction would be much appreciated.

Line Around Detail.zip

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim X1 As Single, Y1 As Single
Dim X2 As Single, Y2 As Single
Dim Offset As Single
Dim Color As Long

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

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

'X and Y coordinates for the top left corner of the box.
X1 = Me![Notes].Left - Offset
Y1 = Me![Notes].Top - Offset

'X and Y coordinates for the bottom right corner of the box.
X2 = Me![Notes].Left + Me![Notes].Width + Offset
Y2 = Me![Notes].Top + Me![Notes].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
End Sub

[ranman256]

reports have line and shape objects to put on the report.
you shouldnt have to program anything. They stretch with the report.

[Eddy Sincere]

I tried using the "Line" and "Rectangle" tools but they don't grow/shrink with the Detail and I can't find the correct property to choose that will allow flexibility. Which property should I adjust?

[Eddy Sincere]

I just tried again and with no luck. Also, based on what I've seen around the web the shape objects are not meant to be dynamic, which is why people resort to VBA.

Perhaps I'm not being clear about my problem. I'm looking for a border that is bound to the size of the report Detail, not an individual record. When I use the objects they repeat on an individual records, which gives me a bunch of individually bordered records on my report. I just need one big border around the entire Detail.

Hope this helps clarify the problem.

[Eddy Sincere]

Are there no takers? If not I'll ask in another forum and post my answer here. Thanks for trying.

[Eddy Sincere]

I found two options.

The first is VBA inserted into the On Page event. It draws a line around the entire page. You can adjust the code so that it draws a line around just the Details section by changing the word Report_Page to Report_Details. This worked reasonably well, but not my favorite.

Private Sub Report_Page()


Me.DrawStyle = 0
Me.DrawWidth = 11

' Draw a border around the page.
Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B

End Sub

The second was much easier. I created a sub report and dropped it into the main report. Then I added a border around the sub report. I prefer to avoid sub reports because I think you can make some buggy reports with too many of them, but in this case I was desperate. It worked well enough that I used it permanently.

Solved!