Draw a box around the detail section?

This is kind of "Excelish".  Let's say I have 20 detail records on a page.  I'd like to put a rectangle around each column, but surround all 20 records, not each individual record's cell.  So, turning on border's won't work.  What I've done so far is I've drawn a line and put one on the edge of each field.  With the detail section the same height as the fields, these lines look like one long line when printed.  The only problem I have is to get the top and bottom lines only ONCE in the detail section - BEFORE the first record and AFTER the last record per page!  This is purely visual.  If it won't work, no big deal.  I'm sure there are properties that know the first last record, but per page?  or per section?  The page might fit 25 records and if the client has 35 records, 25 on the first page and 10 on the second, the top and bottom lines of the "rectangle" have to show in the proper place, right before the first record and right after the last records.  It seems like Crystal Reports used to do this without fail - that was years ago when I used that.  Ideas?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try addind the line to the top/bottom of the report's page header / footer sections

I had the same type of an issue.  Not sure if this will work for you but you can read it and decide for yourself:


Lean beat me to solution n°1...

Basically, you can record the current position during the detail's format event, and then use the page footer event to print the additional ruler. But it's complex, messy, and doesn't work on all devices.

Let's keep things simple:

* Create the raw list with only the lines *between* columns
* Create another sourceless blank report and drag the first on it as subreport
* Choose the border you want on the subreport control.

This works for several pages as well...

Good luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jcampanaliAuthor Commented:
Page header/footer won't work because of space "built-in" between detail and header/footer, even if sections are same height as field.  Access still puts space in between.  The other experts Q offered no solution.  harfang was close, except the report and page header and footers get FUBAR.  When I just do detail, it looks ok except that the border displays where there are no columns (i.e. in between subreport columns).  I'll probably just modify design to fit what I can.  I can border the header labels and the footer subtotals to create the appearance of one continuous box.  Where I don't have subtotals, I can create empty labels with a border.
Hmm. Of course, using the subform solution, you would use the main report for report header/footer and page header/footer, not those of  the subform.

Also I don't understand this:
> except that the border displays where there are no columns
* the subreport control provides a border _around_ the whole list
* you build in the lines _between_ the columns, as you know how to do...

> Page header/footer won't work
> because of space "built-in" between detail and header/footer,
Wrong, there is no such thing.

The first section of each page goes exactly below the page header. If you have a horizontal line there, it will touch your vertical lines. Provided you do not leave "built-in" space below the ruler, of course...
However, the page *footer* will be moved to the bottom of the printable area, defined by your margins. This  is why you can't easily use this method, excelt if you are ready to calculate very precisely what height the page footer needs to be...

Anyway, good luck, too bad you didn't ask for more information before giving up.
jcampanaliAuthor Commented:
You are right about the header.  It is just the footer with the problem due to margin issue.  What I mean by no columns is that I have 11 fields across - 4 touching, 3 touching, and 4 touching, each of the three groups have a gap in between them to separate them visually.  When used as a subreport, the subreport border displays where there are no fields boxes.  If all 11 fields touched, it would work.
Now, I understand, of course, that Page Headers and footers can go in the sub.  But how can I put report grand totals at the end of the report if the report recordsource is blank?  Nevermind, I can use DSum boxes and get them lined up.  So, I guess the only issue is that I have my fields visually grouped by purpose, thus making the subreport border look interesting.  Otherwise, good job!
Ok, I see what you mean now. You want three boxes. That one is tough...
(a while later)
There is a solution for non-growing sections. You can use Me.Top to check how far down the page the current section is printing. If there is not enough room for the next one, you must be on the last section of that page... If the section can grow, however, you can't tell for sure.

Anyway, here is my sample module. It shows a bottom vertical line only if the detail section is the last on that page. An additional report footer (or group footer) is needed to "close the box" on the last page, obviously.

Option Explicit

' The following constants must match your report's settings
' (in twips, or 1440th of an inch)
Const cPaperHeight   As Long = 29.7 / 2.54 * 1440   ' A4
Const cBottomMargin  As Long = 1 * 1440   ' 1 inch

Dim glngMaxTop As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim fLastDetail As Boolean
    ' check if there is enough room for one more detail section:
    fLastDetail = ( _
        glngMaxTop - Me.Top - Me.Detail.Height _
        < Me.Detail.Height)
    ' show a line only on last detail of a page:
    Me.linBottom.Visible = fLastDetail

End Sub

Private Sub Report_Open(Cancel As Integer)

    ' this is the maximal .Top setting for normal sections (e.g. Detail)
    glngMaxTop = cPaperHeight - cBottomMargin _
        - Me.PageFooterSection.Height
End Sub

Of course, the report metrics can also be obtained by extracting the values from Me.PrtMip and Me.PrtDevMode (see the examples for these properties in Help), but that is besides the point of this question.

Have fun!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.