Function DrawLines

[kwvh]

The following is a snippet of code borrowed a long time back I used for a specific report.

Code: Select all

Option Compare Database
Option Explicit

Function DrawLines(R As Report, ParamArray xPos())
    ' Number of twips in one inch.
    Const OneInch = 1440
    ' Maximum height of a section in inches.
    Const MaxHeight = 22
    ' Loop index
    Dim i As Integer
    ' Draw vertical line at xPos(i) inches from left margin. Covers entire section.
    For i = LBound(xPos) To UBound(xPos)
        R.Line (xPos(i) * OneInch, 0)-(xPos(i) * OneInch, MaxHeight * OneInch)
    Next i
End Function

I now need to do something similar to a series of reports that include 4 subreports each. The main report and subreport all need the same set of vertical lines. I wanted to have a function like the one below for each set of 5 reports (the main plus the 4 subreports).

Code: Select all

Option Compare Database
Option Explicit

Function DrawLines(R As Report, ParamArray xPos())
    ' Number of twips in one inch.
    Const OneInch = 1440
    ' Maximum height of a section in inches.
    Const MaxHeight = 22
    ' Loop index
    Dim i As Integer
    ' Draw vertical line at xPos(i) inches from left margin. Covers entire section.
    For i = LBound(xPos) To UBound(xPos)
        R.Line (xPos(i) * OneInch, 0)-(xPos(i) * OneInch, MaxHeight * OneInch)
    Next i
End Function

Function fDrawLines4LagReports(strReportName As String)
    ' Draw vertical lines at X" from left margin.
    Call DrawLines(strReportName, 2.7917, 3.4063, 4.0104, 4.5833, 5.162, 5.7917, 6.375, 6.9896, 7.6056, 8.195, 8.3229, 9.0625, 9.8292) '8.3542
End Function

Unfortunately, I get an error "Compile Error" "ByRef argument type mismatch". strReport is passed to the function by

Code: Select all

Dim strDocName As String
strDocName = "7rptPerformanceRptDept"
Call fDrawLines4LagReports(strDocName)

strDocName and strReport are both strings. FWIW, I tried dim'ing both as "Reports" with similar results.

Obviously I don't fully understand how this module works, so any ideas are GREATLY appreciated.

Ken

[HansV]

The first argument to DrawLines is a Report object, not its name.

The idea is that youy enter an expression such as

=DrawLines([Report], 2.7917, 3.4063, 4.0104, 4.5833, 5.162, 5.7917, 6.375, 6.9896, 7.6056, 8.195, 8.3229, 9.0625, 9.8292)

in the On Print event property of each of the (sub)reports.

x229.png

[kwvh]

Hans,

Thanks for the quick reply. As usual, I left out an important detail. I am using Access 2003 for this project, instead of Access 2010, so placing "=Drawlines . . . 22)" is not allowed in the properties for On Print.

So in the "On Format" event I use the following code to call the local function,fDrawLines4LagReports, which calls the Drawlines function within the global module "modDrawLines". I made the modDrawlines a global module so I could use it for any report, instead of incapsulating within each report.

Code: Select all

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     Call fDrawLines4LagReports
End Sub

Function fDrawLines4LagReports()
    ' Draw vertical lines at X" from left margin.
    Call DrawLines(Me, 2.7917, 3.4063, 4.0104, 4.5833, 5.162, 5.7917, 6.375, 6.9896, 7.6056, 8.195, 8.3229, 9.0625, 9.8292)
End Function

I used On Format instead of On Print, which I did at the time to get around a reoccurring issue when the user previewed first and then printed from the preview. For some reason it worked better that way. And at the time we were working in Access 2000.

So if I haven't muddied the waters too much, is there a better way to get the LineDraw to run for the reports and subreports? I neglected to mention that reports and subreports contain 3-5 groupings (i.e. subtotals sections) each that also use the DrawLines.

Thank you Hans for your guidance and help.

Ken

[HansV]

I am using Access 2003 for this project, instead of Access 2010, so placing "=Drawlines . . . 22)" is not allowed in the properties for On Print.

I've used the technique of entering a formula =function(...) in an event property in all versions of Access starting with Access 95 up to and including Access 2007, so it should work in Access 2003 too.

[kwvh]

Hans,

It worked that time. Obviously I did something wrong the first time.

THANKS! ! !

Ken

[kwvh]

Is there a way to change the width/weight of the vertical line? I found a referece to "post 705,669" on another board, but it was a dead link.

Thanks in advance for your consideration.

Ken

[HansV]

Yes, add a line like this to the DrawLines function, above the line For i = LBound(xPos) To UBound(xPos):

R.DrawWidth = 20

where 20 is the width of the line in twips, where 1440 twips = 1 inch. If you prefer to specify the width in inches, use something like this:

R.DrawWidth = OneInch / 16

(this will draw a line of 1/16 inch wide)

[kwvh]

Hans,

Outstanding. THANKS!