[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I suppress page header when printing group footer?

Posted on 2009-02-13
16
Medium Priority
?
1,607 Views
Last Modified: 2013-11-28
I have a report that includes a group footer that I print on its own page.  The report has a page header (column headings for the detail info) that doesn't make sense to display with the group footer.  I can't figure out how to suppress the page header - I've tried some things through code, but haven't had success.   Thanks for your help.
0
Comment
Question by:jolodali
  • 6
  • 6
  • 4
16 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23638754
jolodali,

What most developers do is to put the Column Headings in the bottom of the "Group" footer.

Try it, and report back

;-)

JeffCoachman
untitled.JPG
untitled2.JPG
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 23638957
Try this ...

Include in your report a Group Header with your current Group Footer.  Set them both to Force New Page Before Section.

Then include the following code for the header and footer OnFormat Events.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Me.PageHeaderSection.Visible = True
End Sub

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
Me.PageHeaderSection.Visible = False
End Sub


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 23638967
Also, if you don't have anything to display in the Group Header, just set its size to 0.

ET
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:jolodali
ID: 23639318
Jeff, you answered that most developers put the column headings in the bottom of the group footer, but the illustration you included shows the headings in the group header.   Which did you mean?  After you read my comments to ET, please let me know if you still think your suggestion will work.  
In general, I should clarify that if there is more than one page of details, I need the column headings to appear on all detail pages.  ET, your answer came close, but the column heading are only appearing on the first page of a group (since your approach suppresses the page header until the next group starts).   I appreciate the help - I just need som more.
Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23639534
jolodali,

"you answered that most developers put the column headings in the bottom of the group footer, but the illustration you included shows the headings in the group header."

Sorry, I mis-typed, you are correct, I should have typed: Group Header.
:-O

Even so, my suggestion would not display the Column headings on each page.

I'll play around with this some more over the weekend.

Perhaps etsherman has a workaround already...

JeffCoachman
0
 

Author Comment

by:jolodali
ID: 23640511
Thanks Jeff - This particular report issue has been an annoyance for a long time but I haven't had the chance to tackle until now.  I remember seeing some "2-pass" solutions for reports that addressed doing page numbering within groups (e.g., page 1 of 2, 2 of 2; then 1 of 3, 2 of 3 etc.)  I think that involved a first pass that counted and wrote to a table the total number of pages for each group, then a function for retrieveing the page number text that read from the table.  I'm wondering if I'd have to do something like this to know exactly where I am in the group - I'm hoping that I (I mean you guys :-) can come up with something simpler...   Thanks
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 2000 total points
ID: 23640986
jolodali ....

Just looking at it, I thinks it's not possible to do what you are trying to do because the group footer is a part of the grouping.  Therefore, if you put the column headings in the page header or the group header they both will format before the group footer which means you have no way to control whether or not the headers print.

However, if it is a must that you have the group footer print on a separate page and without the detail column headings for the group then one way to accomplish this is to use a main report with two sub reports linked via a common field.  This way you could ...

1.) On the main report create the generic page header without the detail column headings.
2.) Include a sub report for the detail and group header information.
3.) Insert a page break control.
4.) Include a second sub report with just the group footer summary information.

This way, the page header would print on each page of the report, the 1st sub report would print all the detail info along with the group headers and the 2nd report would print just the summary info for the relative group on a separate page that does not include the detail column information.

ET
0
 

Author Closing Comment

by:jolodali
ID: 31546808
I am accepting ET's answer because though I won't have time to test it out until next week, I suspect it will lead me to the best solution.  I will post an additional comment after I have worked on it.  Thanks again for helping - you too Jeff.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23642108
jolodali,

OK, I'll stop working on my alternate solution then.

I was going to litterally log what pages the group footer printed on, then turn off the Page header for thoes pages.

;-)

JeffCoachman
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 23650227
Actually, boag2000's last approach would be more practical than using the multiple reports I was referring to.  The only reason I did not mention method  is becuase in the asker's last comments he noted that he was looking for something a little simpler.  

IMO, the three reports I was referring to would be way more cumbersome as compared to some VBA code to log and test the page numbers that contain the group footer then make the page header visible or not.

I don't have a problem if you want to split some points with JeffCoachman or accept his answer because for the sake of anyone else reading this question, what he proposed would be the most logical approach eventhough you mentioned you were hoping for something much simpler.

ET
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23656311
etsherman,

I only posted because I had started working on it.
Then your post was submitted and accepted.
   First come, first served...
;-)

Besides, we miss you around here, so I saw no reason to try to "piggy-back" on your solution.
;-)

But you are correct, my technique would not be considered " simple".
It would have to be modified by the user for each particular situation, even if they did not understand it.
I also noticed that the asker seemed to be under some kind time contraint.
So re-opening to split the points might not be worth the trouble.

A wise man once said:
  "There will always be another question."

;-)

Jeff

0
 

Author Comment

by:jolodali
ID: 23656415
OK - since you 2 are still discussing this - I might as well join in again.  When I knock off some other higher priority items I will be coming back to this one.  I'm pretty sure I can handle a more complex solution, but I was trying to provide my boss with more explanation as to why something he thinks is straightforward isn't.  Sometimes there's power in numbers especially when the opinions are coming from geniuses and wizards :-)  

So Jeff - the wise man was right ;-).  I am going to open a new question and if you have time to answer it by providing even a small amount of detail regarding the logging of the group footer pages  I would be eager to award you the new points.   You don't have to provide the code - just the conceptual approach would be fine.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23657332
LOL.

Thanks for checking back in.

Yes, my approach may not even be the best.

That is why it is best to get opinions from multiple experts.
I still have to tweak my solution.
If you post this as a new question, you might very well get another great post, before mine that may be even better!
;-)

Jeff

0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 23658923
boag2000,

Not a problem and I just wanted to be fair in that given the question asked, the most practical approach would be to use some VBA code to turn the page headers on or off rather than working with the sub-reports which would be more cumbersome IMO.  I was just trying to come up with something that did not involve VBA coding because it sounded like the asker wanted to stay away from that.

I know you can get the page number on format of the group footer ... it just gets a bit tricky when working with Access reports because logically it will format the page headers before any footers but I know it can be done with VBA code.   I've done something similar before and if time permits I will look back through my development db's to refresh my memory.


jolodali  ...

>>> ...but I was trying to provide my boss with more explanation as to why something he thinks is straightforward isn't... <<<<

Good luck on this one.  I run into this situation all the time now that I've been providing software consulting, design and implementation over the past few years.   I have custom apps that have been running for the last 10 years successfully and when a new employee or manager starts working for a client and finds out the developer is accessible they soon want to change and re-arrange a lot of things.  Once I review and quote the bill hours to accomplish their request it quickly becomes ... whow .... I didn't realize it was that involve.

IMO, situations like that are related to dinning in a restaurant ....  "customers generally drink differently when the tea refills are free!!!"  :-)


ET





 





0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 23659913
Ok, since I was awarded the points .... here is my take on using VBA to turn the pager header off when the group header is printed on a separate page.

1.) I created a table named tblPageNumbers with a field named GP_PAGENUM (Long Integer).

2.) I used the Group Footer Format, Page Header Format and Report Open Events as shown below.

3.) You will also need to include a control on your report to show the Page 1 of Total Pages.  This will force Access to make to passes while formatting the report in order to determine the total pages at the same time you will store the page number of the group footer in the format event.  Then when you format the report the second time around in the page header format  you will lookup the stored numbers, when a match is found, the page header will be made invisible.

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "insert into tblPageNumbers (GP_PAGENUM) Values (" & Me.Page & ");"
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim lngPage As Long
lngPage = Nz(DLookup("[GP_PAGENUM]", "tblPageNumbers", "[GP_PAGENUM]=" & Me.Page), 0)
    If Me.Page = lngPage Then
        Me.PageHeaderSection.Visible = False
    Else
        Me.PageHeaderSection.Visible = True
    End If
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "delete * from tblPageNumbers;"
End Sub


ET
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23662786
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

829 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