Draw a box around the detail section?

Posted on 2006-04-11
Last Modified: 2008-02-26
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?
Question by:jcampanali
    LVL 34

    Expert Comment

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

    LVL 10

    Expert Comment

    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:

    LVL 58

    Accepted Solution

    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!
    LVL 4

    Author Comment

    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.
    LVL 58

    Expert Comment

    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.
    LVL 4

    Author Comment

    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!
    LVL 58

    Expert Comment

    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!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now