2015年3月3日 星期二

Vertical line in Access Report using Line Method

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.


vertical_line


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.


rptVertline1


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).


rptVertline2


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.


vertical_line02


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









ArgumentExplanation
(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
vbBlueSet 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.


rptVertline3


Outbound References of drawing vertical line in Access Report


http://msdn.microsoft.com/en-us/library/office/ff198297%28v=office.15%29.aspx



Vertical line in Access Report using Line Method

沒有留言:

張貼留言