Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2011-03-17
Medium Priority
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 400 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.

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


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, ...post 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 1600 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

609 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