How to underlay a section in Microsoft Access Reports

AID: 8911

Status: Published

2080 points

BySeyerIT
TypeTips/Tricks
Posted on2011-12-19 at 03:16:59

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the Page Footer so that the bottom of my report would always be the same design. Here is an example of what my printed report would come out as:

SP32-20111217-115124.jpg

155 KB
Broken report

In the end, this is what I wanted to end up with;

SP32-20111213-122832filled.jpg

173 KB
Finished report

Another issue was that I wanted my horizontal gridlines to run all the way to the bottom of the report regardless of whether there was enough data on the page to fill the detail section to the bottom or not.

I finally came to a function called MoveLayout. When you set movelayout to false, the section basically becomes suppressed and your other sections will print on top of that section just as they do in Crystal Reports.

In my example the best section for me to underlay to accomplish my goal was the page header section. By creating the page header almost the size of the entire page and then setting the On Format = to Me.MoveLayout = False, I could design the section which acts as the backround image for the entire page. In other words, I could draw up all of my borders and gridlines which would then display through the entire page without any breaks.

Most likely it will take a little time tweaking the exact alignment you are looking for as in my case I needed some white space at the top of the page header to account for the Group Header that would appear there. Here is my example of my design;

Thanks!

SP32-20111219-073201.jpg

413 KB
Underlay Page

Other things I did with the report was to set the GroupHeader as Repeat Section so that if my details did span multiple pages, the groupheader would always reprint.

Let me know if you have any questions as I may have forgotten some details to this.. This is my first Article I'm posting so please be sure to comment.

Asked On: 2011-12-19 at 03:16:59ID8911
Tags: Microsoft
,; access
,; report
,; underlay
,; section
,; suppress
,; lines
Topic: Access Reports
Views: 1440

About The Author

SeyerIT

Comments

Expert Comment

by: DoDahD on 2012-02-06 at 13:37:57ID: 42109

This kind of report may be easier to code as a mail merge into a Word Table.
Your grid layout and page header/footer would be in Word and the next record option in a Mail Merge can deliver the Access records.
Let me know if you want to discuss this further.
IMHO, Access reports are great for delivering the data, but not for filing in forms -- unless you want to add some VBA code!

Expert Comment

by: boag2000 on 2012-02-15 at 06:15:38ID: 43032

Thanks for the article.

Reports with a "Grid" style layout come up here often.
As you can see, this is not something that is "easy" to do in Access reports.

I'll expand a bit on DoDahD's post about the distinction between "Forms" and "Reports"
The Grid style Report layout is basically a printout of a Form.
In other words, it is basically a form that you use the Access report engine to create.
This is why Grids are not easy to do in Access Reports.

Often a related question to this is how to "Pad" the report page with "empty" rows.
So what I typically do is set up the grid in the detail section, then pad the report with enough records to fill the page.

But again, alternate approaches are always welcome, so thanks for the submission.
As a reference, there are some great posts on the Lebans site on this topic:
http://www.lebans.com/PrintLines.htm

;-)

JeffCoachman

Author Comment

by: SeyerIT on 2012-02-15 at 06:35:55ID: 43033

Thanks for your input!

I'll look into these alternatives when this type of report comes up again! I'm always open to improving my methods..

Expert Comment

by: JDettman on 2012-02-15 at 06:44:58ID: 43034

I think the major thing to walk away with from this is the fact that you can address the entire page in a header event and with .movelayout false, continue on down the page overlaying anything you've placed on it.

That's never been obvious to anyone before and opens up the door for a wide range of things. For example, placing a water mark on the page.

I believe everyone up till now has always visualized this as the report engine walking down the page and not thinking you could do anything below the current layout spot you were at.

If you were trying to do anything with the page, you always used OnPage, but now that's not the only option.

Definitely something new that was brought to light and one to keep in the bag of Access tricks.

Jim.

Expert Comment

by: boag2000 on 2012-03-19 at 12:53:18ID: 46923

No problem Buddy, I understand.

And to be fair, I do realize that you perhaps signed up here so that you could get "targeted" help, (not just Google Hits...)

The bottom line is that unless you are doing something fairly unique, they fastest way to an answer may be via Google.

Looping files in a folder, is like Filtering records by a form.
...it is a fairly standard question.

Now, ...if I wanted to do something really unique like "underlay a section in Microsoft Access Report"
Then I would post a question here, and hopefully someone may have some insight...
;-)

Jeff