Report Border
By Richard Rost
4 years ago
Draw a Border Around a Report Page in Access
In this Microsoft Access tutorial, I'm going to show you how to draw a rectangle border around your reports with the Line command.
Lillianna from Newark, New Jersey (a Silver Member) asks: Is there any way to draw a border around the page of a report? I'm not trying to be too fancy, but I like the way it looks.
Members
Members will learn some different drawing options for lines and rectangles, as well as how to draw a circle with a pie slice. Oooh. Ahhh.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Course
Pre-Requisites
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Border on a report page, how to draw a report page border in microsoft access, draw lines, draw circles, draw rectangles, draw shapes, How do you add a border in access, Add a border to a page, DrawStyle, DrawWidth, Drawing Circles, Pie Slices, FillColor, FillStyle
Subscribe to Report Border
Get notifications when this page is updated
Intro In this video, I will show you how to draw a border around a report page in Microsoft Access using the Line command and a small amount of VBA code. We will look at using built-in shapes for single-record reports and then walk through the process of adding a programmatic border for more advanced reports with multiple records. I will demonstrate how to adjust the border's placement, thickness, and work with report margins to get a clean result.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to draw a border around a report page in Microsoft Access using the line command.
Today's question comes from Liliana in Newark, New Jersey, one of my Silver members. Liliana says, is there any way to draw a border around the page of a report? I'm not trying to be too fancy, but I like the way it looks.
Yes, of course, there's a couple different things you can do. If you want to just use a background image, I show how to do that in my gift certificates video. This only works for one record at a time. You can't do things like grouping and sorting and have multiple records on the page at the same time. But if you just want to do one thing, like a gift certificate or a special customer report or a product with a product picture and have a fancy background around that, then I show how to do that in this video, so you can go watch that one.
But assuming you want to do a little bit more with a more advanced type of report and you want to put a border around it, you're going to need one line of VBA code, at least one, maybe two or three if you want some options set in there.
If you don't know any VBA programming, go watch this video now. This is my intro to VBA. It's 20 minutes long. It teaches you everything you need to know about VBA. It's not scary.
To put a proper border around your page, you have to know a little teeny tiny bit of VBA. I'm going to show you, but go watch this video first if you haven't watched it yet. And also, optionally, go watch my blank template video and my invoicing video, which show you how I built the database that will be used in today's class.
So here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In this database, we've got customers, customers have orders, and orders can have invoices.
Now, let's say you want to put a border around this invoice, kind of like the one I showed you in the preview. I've had some clients in the past who had printed forms that they've been using for decades. They had a Word document they'd been using, and they want the new stuff in Access to look like their old documents.
Let's put a border around it.
Now, if you're only dealing with a single record, the order form has the parent record and then it's got child records down here. This invoice actually is data from two different tables. You get your order, you get your details. So if we were dealing with a simple record that was just one record at a time, we could just draw a rectangle and put it in the background of the report. That would be all we'd need to do.
For example, if I create a blank report and I don't need a page header and I don't need a page footer, I'm just going to make a detail section here and I'm going to bind this report to, let's say, the customer table. All right, customer T. There we go.
Now I can add fields from my customer table. Let's just grab a couple of them like this. All right, drag and drop, drop them there.
Now let's go to Print Preview real quick. It's looking like that. Let's say we only want to have one per page. So what I'm going to do is go to the detail section's properties, and I'm going to say, force new page after section. So every time the detail section finishes, it forces a new page.
Print Preview. Now I got one per page. I'm going to turn that alternating background color off. Come over here. Go to None.
Now let's say I want to put a border around this page. That's really easy to do. All you do is come up here in the control box, find the rectangle tool. Draw a rectangle like that. Let's go to the Format tab. Go to the Shape Fill and make it transparent so we can see everything. Go to the Shape Outline, make it whatever color you want, blue, and then change the thickness. Maybe make it nice and thick like that. There's your border.
You want to size this thing so it's the size of the page. Just open this up, click, drag it down. You get the point. Right click, Print Preview, and you just size this out, set your margins wherever you want them. There's your border. But that's only good for one record at a time.
This will not work with something like the invoice that's got multiple records in there. I've tried playing around with putting lines on there, vertical lines, and stretching them out. There are tricks you can play, but none of them are perfect.
Let me close this. I'm not going to save this. This is not what I want.
If you go into, for example, the invoice here, some people want to have a vertical line in each of these columns. You can do that. That's easy to do. Let's come in here. Let's say between each of our products, we want to put a little vertical line in there, like a column thing. Take your line tool, draw it right there. Save it. Now when I go to Print Preview, let's see what it looks like.
There you go, a little line there. Of course, you've got to match it up. You have to put a little segment of it in the header part there. It takes a little time to make this thing perfect, but you get it in there and you get it just set up right. You have to paste it up here like this. I can never grab this thing. Come here. Gotcha. Line it right up over the top of that one. Resize it now, so it fits in that little space.
This is more art than science. Let me see what I'm going with here. There. All right, I got that segment matched up now. Save it. Let's do it again. This will take you some time. I got the top matched up. You do the same thing with the bottom piece. You can do some vertical lines.
But then, I've tried to put horizontal and vertical lines around the outside. You get this big dead section here that the lines won't fill. So the best solution in a case like this, where you just want a single standard border, is to use the line tool.
There's a line drawing command in Visual Basic in VBA. You can turn it into a rectangle, and it'll literally draw a rectangle around the report.
How do you do that? This is where a line of VB code comes in. Go to Design View, and I'm going to get rid of that one I tried to put down here. Click. Goodbye.
Now, we have to go to the report properties. Double click, go to the Events tab, and you want to find the On Page event, right down here. On Page is the macro or function that runs before the page is printed or print previewed or turned into a PDF. Basically, when it is formatting the page, because there is also an On Format, but On Page is a specific event you want to use.
Let's hit the dot dot dot button. That will bring up our editor and it's literally one line of code with maybe a couple modifications. I'm going to type in the line and then I'll explain it.
It's Me.Line. In other words, draw line. Then you give it the starting coordinates and the height and width. We're going to start at 0,0, which is the upper left corner and go through with a dash. Now we have to give it the width and the height. I'm going to use the width and the height of the whole report. So it's going to be Me.ScaleWidth, comma Me.ScaleHeight.
What is ScaleWidth and ScaleHeight? Don't worry about it. I cover that in my more advanced classes.
Now that's just going to draw a line. If I want to make a box out of it, you go comma comma B. That's just an option you do and it makes it a box.
Save it. Back out here. Close it down. Always close and reopen your stuff. Then reopen it. There it is. There's a little thin border going around it. I'll zoom in so you can see. See right there.
Now it's missing over here on the right. Why is that? Well, it kind of overlaps. There's a margin there and it doesn't always take that margin into consideration. I've noticed this a lot.
What I like to do, I'm going to go back to my code editor, is come in here and bring it back. These are measured in twips. You can change the scale, that's why it's called scale width and height. You can change that. You can make it whatever scale you want to. You can work in twips, you can work in pixels, you can work on a one to one hundred scale, you can do inches. It doesn't matter. But the default is twips.
What's a twip? That's another long video in and of itself. Basically, there's 1,440 twips in an inch.
You have to bring the width back a little bit so it's not poking over the side of the right margin. So I'm going to say, let's bring it back like 40 twips.
Let's take a peek now. See what it looks like. Close it and reopen it. Close Print Preview. Back in here and go. There it is. Perfect. Forty is about right.
You might have to play with it. You might have to change the margins a little bit and get everything looking exactly the way you want it. There's your border that goes all the way around the page.
There are a ton of options you can change. All kinds of things. For example, if you want to make the line width thicker, do something like this: Me.DrawWidth = 30. In other words, we're going to make that line width 30 twips in size, so you get a thicker border.
Watch this. See, it's thicker. Want to go even thicker still? Let's bring it up to 100. Ready? Go. Nice. Bad.
That's it. That's the simple way using one line of VB code. Basically, you can get a border around your pages. You can change color, you can change the fill, you can change all kinds of stuff.
I'll cover some of that in the extended cut for the members. We'll talk about the draw style. We'll see how to make that a dashed or dotted line box, whatever. The draw width we kind of already talked about. I'll show you how to draw circles. You can draw a circle anywhere you want. I just dropped a circle over the top of the report, but you could do that anywhere. How to cut out pie slices. We'll talk about the fill color, the fill style. Lots more in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. There are a lot of them. It's like 300 or so. You get some free classes every month and all kinds of cool stuff.
Sign up today and I hope you learned something. I'll see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To add a background image to reports in Access
B. To group and sort records in Access
C. To draw a border around a report page in Microsoft Access
D. To insert customer data into a report
Q2. What simple tool can be used to draw a rectangle border for a single-record report?
A. Line tool
B. Rectangle tool
C. Image tool
D. Data field tool
Q3. Why does using a static rectangle border not work well for reports with multiple records, like invoices?
A. It only displays the border on the first page
B. It leads to overlapping data fields
C. It cannot handle records from multiple tables and groupings efficiently
D. It is too time-consuming to create
Q4. When putting a border around a report that displays one record per page, what property should you adjust to ensure each record prints on a separate page?
A. Page Footer
B. Detail Section's Force New Page property
C. Group Header
D. Sorting and Grouping
Q5. In the VBA solution, which event on the report should be used to draw the border at print time?
A. On Format
B. On Load
C. On Open
D. On Page
Q6. What is the basic VBA syntax used to draw a border (rectangle) around the report?
A. Me.Box (0, 0, Width, Height, "B")
B. Me.RectangleDraw (StartX, StartY, EndX, EndY)
C. Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B
D. Me.DrawBorder (Me.Width, Me.Height, "Rectangle")
Q7. What do Me.ScaleWidth and Me.ScaleHeight represent in the VBA code?
A. Height and width in inches
B. The size of the current object in pixels
C. The width and height of the report in twips
D. The size of the margins only
Q8. What is a 'twip' in Microsoft Access reports?
A. A type of font style
B. The unit of measurement, 1,440 twips equal one inch
C. A VBA object property
D. The height of the current field
Q9. Why might you subtract a value (like 40) from the width when drawing the border?
A. To increase the page size
B. To account for the right margin and ensure the border does not overlap the edge
C. To center the rectangle vertically
D. To make the border dashed
Q10. Which property would you adjust in VBA to make the border line thicker?
A. Me.LineWidth
B. Me.Thickness
C. Me.DrawWidth
D. Me.ShapeBold
Q11. What feature is discussed as a benefit of Silver (and higher) membership at AccessLearningZone.com?
A. Discounts on Access software
B. Early access to SQL Server videos
C. Extended cut videos with advanced tips and techniques
D. Priority tech support for Microsoft products
Answers: 1-C; 2-B; 3-C; 4-B; 5-D; 6-C; 7-C; 8-B; 9-B; 10-C; 11-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how to add a border around a report page in Microsoft Access using the line command.
A question came in asking if it's possible to draw a border around the page of a report, not for anything too fancy, but simply for a nicer visual effect. The answer is yes, and there are a couple of different ways to approach this.
If you only need a background image and you're dealing with one record per page, you can use the background image method that I demonstrated in my gift certificates video. That technique works well for single-record reports, such as a gift certificate or a one-off product sheet with a customer picture, but it doesn't work when you have multiple records displayed on one page or when you're grouping and sorting data.
For more complex reports, where you want a border around a page that contains multiple records, you'll need to use a small amount of VBA code. If you're not familiar with VBA programming, I recommend watching my introductory VBA video. It's a straightforward, 20-minute walk-through that will show you the basics and help you get started.
Today's demonstration uses my TechHelp free template, which you can download from my website. In this example database, there are customers, those customers have orders, and each order can have associated invoices.
Suppose you want to put a border around the invoice report. This is a situation I've seen before, where clients are moving from old Word-based forms to Access and want their new reports to have a similar appearance.
If you're working with just a single record in your report, such as a simple customer report, you can very easily draw a rectangle and place it in the background. Here's how that process looks: create a blank report, bind it to your table (such as the customer table), and add a couple of fields. You'll probably want to set the report so it only displays one record per page. To do that, set the detail section's properties to force a new page after each record. That way, in Print Preview, you get one record per page. Then, use the rectangle tool to draw a border around the detail section, format its outline and fill, and adjust the size to match the page. This works well for single-record-per-page reports.
However, this approach does not work for reports—such as invoices—that show multiple records per page. Attempts to use vertical or horizontal lines to create borders don't usually work well because of gaps and alignment issues.
In a report like an invoice, you might want vertical lines between columns. This can be accomplished by drawing vertical lines precisely where you want them, segment by segment. It's more of a manual process and requires a bit of attention to detail to get the segments to line up correctly, especially in the different report sections.
But if your goal is a clean, uniform border around the entire report page—even when dealing with multiple records—the most reliable method is to use VBA code to draw a border dynamically. Access has a line-drawing command that can be harnessed in the On Page event of your report.
To do this, first open your report in Design View. Go to the report's properties and switch to the Events tab. Look for the On Page event—this event fires just before the page is rendered for viewing or printing. Open the code editor from there.
The code uses the Me.Line command, which draws a line or shape on the report. You'll specify starting coordinates (usually 0,0 for the upper-left corner) and use Me.ScaleWidth and Me.ScaleHeight for the width and height, which ensure that your border matches the size of the report page. To create a rectangle, supply the appropriate argument so that a box is drawn around the edges.
Often, after previewing the report, you'll see that the border might overlap or extend into the margins, especially on the right side. This is because ScaleWidth and ScaleHeight are measured in twips (there are 1,440 twips in an inch), and you'll likely need to subtract a small amount—like 40 twips—from the width to compensate for the automatic page margins. You may have to experiment with this value to achieve the best result.
There are many additional options you can apply to this border. For example, you can set the line width (the thickness of the border) using the DrawWidth property. Adjust this value as desired to get a thin or thick outline. You can also change the color, the style (such as dashed or dotted lines), and even draw different shapes, like circles. These customizations let you style the report border just the way you want it.
In the extended cut for members, I go into further detail on these advanced drawing options. We explore draw styles, more complex shapes, different fill and outline settings, and more to give your reports exactly the look you want.
If you're interested in more advanced features and want access to hundreds of extended cut videos, including free monthly classes and other resources, consider becoming a member.
For a full step-by-step demonstration of everything discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Drawing a border around a report page in Access
Using the rectangle tool for single-record reports
Configuring report detail section to force new pages
Adding and customizing shape outlines and thickness
Placing and aligning vertical lines for columns
Using the On Page event in Access reports
Writing VBA code to draw a border with Me.Line
Understanding Me.ScaleWidth and Me.ScaleHeight
Adjusting border width to account for page margins
Modifying DrawWidth to change border thickness
Working with measurement units (twips) in Access |
