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

Posted on 2011-03-17
Last Modified: 2012-05-11

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

Question by:DowneyCity
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 10

Assisted Solution

t_hungate earned 100 total points
ID: 35160822
Is your Page 3 report a single page?

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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35161916
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.



Author Comment

ID: 35166725
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.

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 35181384
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.


Author Comment

ID: 35182805
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35183182
<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.


Author Comment

ID: 35190499

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

LVL 74

Accepted Solution

Jeffrey Coachman earned 400 total points
ID: 35192000
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...




Author Comment

ID: 35193010

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?

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35193135
<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



Author Comment

ID: 35194052
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?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35195303

<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"

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question