This tutorial explains how to add vertical line in Access Report using VBA Line function, while the line tool fails to draw a continuous vertical line.
Issue with using Line tool to add vertical line in Access Report
Vertical line in Access Report is one of the hottest topic in this website. In Access Report, it is possible draw a vertical straight line using “Line” tool under “Design”. However, the line is not continuous across records.
Lets take a look at what I mean the line is not “continuous”. In the Report Design View below, we set the Detail Height to 1cm, and the Height of the vertical line is also set to 1cm. Ideally, each vertical line in the Report should be joined together.
When we switch to the Print Preview, you can find the vertical line in Access Report is not continuous, there is a little gap between each Detail (last name).
In this example, I have made sufficient length for Last Name text box and you can see the full text of last name. If the text box length is not long enough to show full text and if it is set to “Can Grow”, the Last Name text box will grow two rows and you will find each gap is even wider, because Detail Height becomes longer than the line height we previously set. We need to draw a line across Detail dynamically regardless of the Detail Height or Group Height.
Syntax of Access Line Method
Access Line Method allows you to draw straight line (vertical, horizontal, inclined) or rectangle in Access Report, we are illustrating vertical line in this example.
expression.Line(Step(x1, y1) – Step(x2, y2), [color], [BF])
| (x1, y1) | Position of the starting coordinate of the line |
| (width, height) | width: width of rectangle height: height of rectangle |
| [color] | Optional, indicate the color of line |
| [BF] | Optional, fill with specified color if you draw a rectangle |
Note that Line Method can only be used in OnPrint or OnFormat event property for a report section, or the OnPage event property for a report.
Example – Using Line Method to add vertical line in Access Report
In this example, will insert a vertical line on the right of Last Name Text box using Line Method.
Navigate to Property Sheet of the report, click on the “Event” tab > On Format > Code Builder, and then insert the following code
Me.Line (Me.LastName_tb.Left + 2000, 0)-Step(0, 1000), vbBlue
| Argument | Explanation |
| (Me.LastName_tb.Left + 2000, 0) | x1 coordinate is 2000 units from the left border of “LastName_tb” to the right. Note that Text box does not have Right Property, you can only estimate the x1 coordinate from the left border.
y1 is 0 (top of the Detail) |
| Step(0, 1000) | width of rectangle is set to 0 because we only want a verticale line height is set to 1000, it doesn’t matter how long as long as it is longer than the height of the Detail |
| vbBlue | Set color of verticle line to blue |
Result – Using Line Method to add vertical line in Access Report
Below are two lines for comparison, the one on the right is created by Line Tool where you can see the line is broken. The left one is created by Line Method.
If you still find the line broken after using Line Method, try zooming in the Report, sometimes what you really print out is different from what you see in Print Preview.
Outbound References of drawing vertical line in Access Report
http://msdn.microsoft.com/en-us/library/office/ff198297%28v=office.15%29.aspx