Need help with Access 2003 – “Print form” more specific on Page 3 report.


Need help with Access  2003 – “Print form” more specific on Page 3 report.

Having trouble printing the following procedure which is in the Report Header of report call Page 3 report

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
     On Error Resume Next

     Me.DrawWidth = 4
     Me.DrawStyle = 0
     Me.Line (100, 13000)-(9899, 1200), vbBlack, B
     Me.Line (2.8 * 1440, 13000)-(2.8 * 1440, 1200)
     Me.Line (3.59 * 1440, 13000)-(3.59 * 1440, 1200)
     Me.Line (4.43 * 1440, 13000)-(4.43 * 1440, 1450)
     Me.Line (5.25 * 1440, 13000)-(5.25 * 1440, 1200)
     Me.Line (6.09 * 1440, 13000)-(6.09 * 1440, 1450)

End Sub

There are four reports linked by account numbers.  The first report (Page 1 report) has 3 subreports called Page 2 report, Page 3 report and Page 4 report.  Page 3 report needs the lines from the procedure above which needs to fit the whole page.  If Page 3 report is printed by itself the procedure works.  What I have read about subreports is that they do not print the report header.  How do I get the “Report header” form to print on Page 3 report only?

Janet L Benavides
City of Downey

Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
First I will say that you might want to reconsider the design of this report.

It is not common to have a separate "Report" for each page.
This is because what you are calling "Page 3 Report", actually spans multiple pages...
Thus making the hard-coded Name irreverent and confusing.

Typically you have only one report and you use page breaks to separate the pages into Groups and/or pages.
Or you create a main report with subreports
...but this is a topic for a separate new question...

For your situation it would be *Much* easier if you drew the lines in manually.
This will only have to be done once. (For each report that needs it).
Then set a Page break after each fund_Dept footer.

As far as making the totals appear at the very bottom of the page, this will be difficult.
Perhaps the topic of another yet another new question...

Sample attached...



Tony HungateConnect With a Mentor Director of TrainingCommented:
Is your Page 3 report a single page?

Take a look at this question an see if it gets you started.

Jeffrey CoachmanMIS LiasonCommented:
Are you absolutely sure this will *Always* be on page 3?

Your code is set to run on the Report Header event, which might also be part of the problem...
There is only One Report Header, ...on the top of the Report.
So you can't really print a report header (or run your code as it stands) on page 3.

What you probably need is for the code to run on the "Page Header", ...then set the code to run on page 3

Can I ask were you got that code from?

...Because the VBA help files provide an excellent example of how this can be done.
(In the VBA help files, research the Line Method)

Sample attached

I am sure you can modify this to work in your DB.


Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

DowneyCityAuthor Commented:
There could be more than one page for "page 3 report".  I will be working on the two solutions and see how it goes today.

DowneyCityAuthor Commented:
The two solutions are not working for me or I am just having problems.  The one from t_hungate is in the Pageheader Section and sub reports do not use page header section.  The one from boag2000 I am still working on but I can not insert a group header above all other group headers.  Also Page 3 report can be more than 1 page.

DowneyCityAuthor Commented:
How could I get this code to print on X Test 3 report only?

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
    If Report = [X Test 3] Then
    End If
End Sub
Sub DrawLine()
    Dim rpt As Report, lngColor As Long
    Dim sngTop As Single, sngLeft As Single
    Dim sngWidth As Single, sngHeight As Single

    Set rpt = Reports![X Test 3]
    ' Set scale to pixels.
    rpt.ScaleMode = 3
    ' Top inside edge.
    sngTop = rpt.ScaleTop + 5
    ' Left inside edge.
    sngLeft = rpt.ScaleLeft + 5
    ' Width inside edge.
    sngWidth = rpt.ScaleWidth - 10
    ' Height inside edge.
    sngHeight = rpt.ScaleHeight - 10
    ' Make color red.
    lngColor = RGB(0, 0, 0)
    ' Draw line as a box.
    rpt.Line (sngTop, sngLeft)-(sngWidth, sngHeight), lngColor, B
End Sub

Right now it is printing on all pages except page1.
Jeffrey CoachmanMIS LiasonCommented:
<I can not insert a group header above all other group headers. >
I think we are having a terminology issue here.
How many grouping levels does the existing report contain already?

The sample I posted puts a border around the the Page header on Page 3.
The code I posted is on the Format event, yours is on the Print event.

<Report = [X Test 3]>
"Report" should not be used as a Variable or object name because it is use to signify the "Report" object itself.

Also, with no context, I have no idea what "Report = [X Test 3]" is trying to do anyway...?

Finally, remember that you did not post any details on how your report is designed (Raw data, Sorting, Grouping, Layout, Other code, Report section properties, ...etc)

So I think we are at the point where you need to post a sample of this database.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.

DowneyCityAuthor Commented:

Sorry for the miss communication.  I am from old school of programing.  Attached I hope are the information and data requested.

Do run the report:
    Go to reports
    Print preview
This will give you want I have been talking about.  

In the document you will see "page 3 report"  called expenditure detail and this is the report I need to add the form (draw lines).  

DowneyCityAuthor Commented:

I had drawn in the lines manually and the bosses wanted the page to look different.  Could you post a sample of a multiple page report that uses page breaks and Groups?  If this is use can you have different queries?

Jeffrey CoachmanMIS LiasonCommented:
<I had drawn in the lines manually and the bosses wanted the page to look different.>
As far as I can tell, my sample displays exactly as you specified...
(Did you examine it?)

So you will have to be a bit more specific on what:
    "the bosses wanted the page to look different"
... means


DowneyCityAuthor Commented:
Two questions:

1.  The boss wants every expenditure detail to have same size of square and lines.  When you draw the lines in the each expenditure detail they are different because of how much data there is.  Is there a way to make every expenditure detail page square the same size in which the group (fund & dept) footer would then print at bottom of page?

2.  If we upgrade to access 2010, would it be possible to use the same reports and print the lines on the third page?
Jeffrey CoachmanMIS LiasonCommented:

<The boss wants every expenditure detail to have same size of square and lines.>
This is not what is displayed in the pdf you posted...
There are no "Square" boxes anywhere...

<If we upgrade to access 2010, would it be possible to use the same reports and print the lines on the third page?>
If we can't get this to work in 2003, then there is no feature in 2010, that I know of, that will do it either.

I am afraid you will have to post a detailed graphic illustrating the *EXACT* output you are looking for based on the data in the sample DB you posted for "Report Page 3"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.