How to Put Continued in Access Report Group Header After 1 Page
Repeating Header
Repeating Header
(OP)
Is there away to have (Continued) appear after the header when it repeats on the next page?
Thank you for any and all help,
PBrown
Source: https://www.tek-tips.com/viewthread.cfm?qid=691427
RE: Repeating Header
2
THWatson (TechnicalUser)Take a look at the Microsoft Knowledge Base, article 207624, and see if this helps.
Tom
RE: Repeating Header
(OP)
I have looked at the knowledge base and attempted to use the idea they provide. However, if a section does repeat, the "Continued" does appear for that section on the next page, but also for all other headers on that page. If nothing is continued, the "Continued" does not appear. Below is what is currently being used, any suggestions?
Public CurrentGroupVal As String
Function SetGlobalVar(InReport As Report)
CurrentGroupVal = InReport!SetGroupVal
End Function
Function SetContinuedLabel(InReport As Report)
If InReport.Page <> 1 Then
InReport!continuedLabel.Visible = _
IIf(Trim(InReport!CheckGroupVal) = _
Trim(CurrentGroupVal), True, False)
End If
End Function
This comes from MS Knowledge Base Article 207624 as suggested by THWatson.
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
Sorry, but I've been tied up for a couple of days.
What I assume from your post is that the "header" you are talking about is a Group Header.
If that is the case, I think you would have to put a label that says "...continued" in the group header, and then put code in the Format event to make that visible or not. The code would use the PrintCount method to evaluate.
Tom
RE: Repeating Header
(OP)
I have tried the Count, but If that is used, any item that has over one item, the "Continued" shows....
Also tried:
If PrintCount = 1 Then
[Continued].Visible = True
Else
[Continued].Visible = False
End If
However "Continued" appears in every header. Tried changing the code to = 1, <> 1, < 1, >1. The only difference is that unless it is = 1, "Continued" never appears.
Do I need to add another field?
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
This is driving me crazy. Frustrating as to why it doesn't work properly. There has to be a way.
Currently, I am wondering if the problem lies with the fact that more than one section gets printed on a page, but sometimes a secion runs over into a second page. Have you tried forcing a new page after the group footer section, which would guarantee only one group gets handled at a time?
If you try that, let me know.
I have to think more about this. And I am going to be out for the rest of the day shortly.
Tom
RE: Repeating Header
(OP)
Forced new page after each section and the problem persists. Let me know if you think of anything.... I am off to search more sites... thanks for your help!
PBrown
RE: Repeating Header
I just had a quick look on the Access Web. Take a look there, in the Reports tab, and see what it says in the "Print first and last page for a group". Maybe you can extract something from that which will fit your project.
This is the link that should take you directly there.
http://www.mvps.org/access/reports/rpt0013.htm
Let me know if that works.
Tom
RE: Repeating Header
PBrown
Hey...I got something that works!
I did up a dummy table with only two fields. One is a ProductID field (autonumber). The other is Product. I populate the Product field with a items called Nuts, Bolts and Bait. I put enough Nuts in to be sure that Product would force over onto a second page.
Then I did this to set up the report.
1. Sort and group on Product, with both a Group Header and Group Footer.
2. Set the Product Group Footer to Force New Page after section.
3. Put a label in the Product Header. The label's name is "lblContinued" (without the quotes) and the label shows "Continued" (again without the quotes)
4. Set the Product Header's Repeat Section property to Yes (this is an important step!)
5. In the Product Header's Format event, I put this code...
Static strProduct As String
lblContinued.Visible = (Product = strProduct)
strProduct = Product
So the entire code in the Format event is
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Static strProduct As String
lblContinued.Visible = (Product = strProduct)
strProduct = Product
End Sub
I tried omitting Step 2 and it didn't work. I also tried removing the Product Footer but that didn't work either.
I assume you can follow this same procedure and put the code in the Product Header's Print event, if you prefer to put it there rather that in the Format event. But what I have done seems to work fine.
I'll be interested in hearing how you make out.
Tom
RE: Repeating Header
(OP)
Looks Great. However, since I saw the "force a new page", I thought I had better see the length of the report. When I set to "force a new page" the report goes to over 20 pages, compared to the current 9. Simply because there are some groups that are only one line. Therefore, since I know more "areas", which is a group, will be added, it would come down to a pack of paper for report, which is just too much paper. I have printed the article out from your suggested site, but have yet had a chance to look over it. Hopefully, the day will calm down and allow me to try to work with your above code and / or look at the print out. I will keep you informed and please do the same if you run accross anything else. Currently, I trying to find something from other forums, etc.
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
Yes,I understand. Seems a waste of paper to print a page for one line when a group is only one line. Maybe there's a way to manipulate the code somehow so that it will work without forcing a new page.
If I can figure something else out, I will let you know. And if you find something, please let me know, because I can tuck it away for future reference in case I want to do the same.
Woody's Office Portal is a decent site. You might want to place a post there. You will have to sign up (it's free), and then use the "Jump" to go to the Access part. The address is www.wopr.com
You could even post the code and ask if anybody knows how to make it work without forcing a new page.
It would be nice to call it a day on this one, wouldn't it? You've been going round the horn on it for quite a while.
Tom
RE: Repeating Header
(OP)
It would be soooo nice to call it a day, and hopefully soon it will be over.
I guess this is what I get for answering, "Do you know anything about Access?" with, "Some"...lol.....
Will keep you informed and again, thank you for your help!
PBrown
RE: Repeating Header
PBrown
I know what you mean. I spent a lot of time a few years back, building a database for a church, after saying one night at a meeting, "Why are we still doing it this way, when it would be a piece of cake to put this together in Access?"
Tom
RE: Repeating Header
PBrown
Hey...I got it! Just a little tweaking.
1. Set the Group Footer, Force New Page property to None.
2. In the Sorting and Grouping, have these settings
Group Header Yes
Group Footer Yes
Group On Each Value
Interval 1
Keep Together No
The "Keep Together" No is the only change I made.
This will let you have more than one Product, or Area, in your case appear on a page, but the word "Continued" will appear in the Group Header when a group goes over onto a second page.
Try it and let me know.
Tom
RE: Repeating Header
(OP)
Works Great! Sorry I did not get a chance to try it and let you know sooner. But it is month end....
There is only one thing about using the code.
If the first detail can not fit on the page with the header, the header appears with nothing after it and the next page starts with "Continued". I.E.
Area1 (Header)
Detail 1
Detail 2
Detail 3
Detail 4
Area2
---------Next Page
Area2 Continued
Detail1 ...................................................................
Detail2 ..............................................................
Is there away to tell the report that if atleast one detail does not appear, to force a new page? I know this can be done in the sorting and grouping but then the "Continued" does not work....
Thank you again for your help!
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
You are absolutely right. Good catch. Which leads me to say we're that close, and yet...
Because of your discovery that if a new Group was about to start at the bottom of a page, but didn't actually print until the top of the next page, there was a problem, I decided that we had to do a little trickery.
So I switched tactics entirely. For the moment, leave in the 3 lines of Static variable code, but comment them out by putting an apostrophe ' at the front of each of those 3 lines.
Then try this...
1. In the Detail section of your report, put an unbound text box, and set its Control Source to =1. Then set its Running Sum property to "Over Group." What this will do is create line numbers for the records in each group.
2.
RE: Repeating Header
Sorry! I hit the "submit" button by mistake.
Carrying on my instructions...
2. In the On Format event for the Group Header, below the 3 lines that you commented out, put the following...
If Me.YourTextBoxName > 1 Then
lblContinued.Visible = True
Else: lblContinued.Visible = False
End If
What this does is check to see if the running sum is greater than 1. If so it prints the Continued label. If not, it doesn't. This gets around the awkward page break at the bottom of a page.
After you have seen how this works, all you have to do is set the Visible property for that text box in the Detail section to "No" to hide it.
I tried it under several situations at this end and it worked fine.
Let me know how it works for you.
Tom
RE: Repeating Header
(OP)
I have tried this before, the problem is that if there is more than one line, "Continued" is visible, even if the section is started and finished on the same page.
I.E.
Area1 Continued
Detail 1
Detail 2
Detail 3
Area2 Continued
Detail 1
---------- Next Page
Area2 Continued
Detail 2
Detail 3
And so on.... There almost has to be away to "work" with your first code or this one.... I can not believe Access was not programmed with this not in mind.....
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
Hmmm. It works perfectly at my end, so it seems to me it should at your end.
Check to see that Keep Together is set to "Yes" in the Group Header section, the Detail section, and the Group Footer section. But the Keep Together in the Sorting and Grouping box should be set to "No." Also, the Repeat Section in the Group Header has to be set to "Yes."
I did some research on why my Static code wouldn't work (or why it worked sometimes but not others). It seems that the expectations are that the Force New Page will be set to "Yes". But this is not what you want, because of the extra paper.
I tried a couple of other methods too, but they also wanted the Force New Page set to Yes.
In any event, could you check your various settings and see whether or not they are the same as mine? If this works at my end, then it's a matter of discovering which of your settings is different from mine (I think).
If you can't get it to work, then I need some things from your end. I will need the various settings for your Group Header, Detail, Group Footer sections, and Sorting and Grouping box. It will also help to know the exact number of rows of Detail in the first 3 or 4 Areas.
Tom
RE: Repeating Header
(OP)
All Keep togethers set to "Yes" then sorting and grouping set to "No". But "Continued" keeps appearing for all headers.
Here is some info about the report:
Area1
15 detail lines
Area2
5 detail lines
Area3
5 detail lines
Area4
3 detail lines
Area5
5 detail lines
Area6
20 detail lines
Report Header Height: 1.1354"
Page Header Height: 0.5417"
Area Header Height: 0.2083"
Detail Height: Set at 0.2299" but can grow according to the size of the description the user types.
Also,
I have been directed to Microsofts Knowledge Base Article 306127. However keep getting "Runtime Error 3219" Invalid Operation and debug goes directly to:
Set GrpPages = DB.OpenRecordset("Plant Pages", DB_OPEN_TABLE)
This may hold a clue, but I can not seem to get it working.
I was hoping to use this for and see how it works and possible "Tweek" it to work for what I need. The only items I changed from the article is that in the table, I used Area instead of Country and I named the table Area Pages instead of Category Group Pages.
What do you think?
Again,
Thank you for any and all help,
PBrown
RE: Repeating Header
Thanks for the details. I'll have a further look.
Tom
RE: Repeating Header
PBrown
Thanks for the details. I have set my section heights to exactly the same as yours. I also made my table to show exactly the same number of items as you have. My report is printing perfectly, with the pages breaking appropriately and the "Continued" label only appearing when it is necessary. So I don't quite get why mine and yours are operating differently?
Do you have any sensitive data in your database that would preclude your sending it to me? If not, I'd be happy to have a look at it.
As for the Knowledge Base article 306127, did you notice that early on in the instructions it said to set the ForceNewPage property to "Yes" in the Group Footer? Doesn't that in itself defeat what you want?
Tom
RE: Repeating Header
(OP)
That must have slipped by me (Force new page)...
As for sending the report, no can do. The DB is up to 14MB and even when zipped it only goes down to ~4MB and we can not send e-mail outside the company that is over 2MB.
However, I did find something else in the Microsoft Knowledge base. It will not put continued in the actual header, but it can place it in the page header. However, I am having difficulty in getting it to work. Article # 182823. It says that reports may require more work, but I don't even know where to start...
Your help is greatly appreciated!
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
I agree - it's too much to send.
As for Article 182823, I just had a quick peek. Did you notice that it says this applies to Access 97 (aren't you in Access 2000)? Also, near the very bottom of the article, it says you need to set the Force New Page property to "Yes" to control the layout. Back to the same old "force new page" deal. It might not be Murphy's Law, but it must be Access'.
As an alternative, why don't I send you what I worked up. If you post your e-mail address here, I'll ship you my little db I worked up for this. Don't put your e-mail address in the normal form. Rather, put it like this "jsmith*at*promises.com" That will encrypt it a little.
Tom
RE: Repeating Header
(OP)
I had the code from the article working, however, I accidently made that Copy of the FE into a MDE file. Therefore, can not recover the report or the Module. Any suggestions?
As for the e-mail, I would like to save that for a "last option" since all incoming e-mails are screened and we must account for any they deem "Personal".
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
I just worked with the code from the Article 182823 also, just to see. And it worked fine. And you don't have to set the section heights at what they say, the heights can be anything.
It's pretty straightforward. Just follow the instructions. Just keep the Knowledge Base article open and cut and past in the code for the Module into your new module. When you have done that, click on Save, and it will give you the option of naming your module.
Now, as for making your FE stuff into an mde file, ouch. Do you have a backup of your database? If you don't, I'm not sure whether or not you can get back. But I haven't tried it. You may want to make another post about that situation.
I understand the e-mail reluctance. No problem. What bugs me is that something works here but not at your end, and I'd like to understand why. If you have an e-mail address at home, I could ship my little sample to you there. Your call.
I sure hope you can resolve this mde file matter.
Tom
RE: Repeating Header
(OP)
Currently we are using Access97
As for the cut and paste of the code,
When you cut and pasted, did
=SetContinuedLabel (Report)
turn into:
=SetContinuedLabel([Report])
for some reason, on both the page header and footer, (report), automatically turned into ([report]). I believe this is why the "Continued...." is only appearing on the last page.
Any ideas?
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
Well, actually, I only cut and pasted the code for the Module. But I just checked and Yes, the (Report) got changed to ([Report]) in each case.
I assume you must have recovered from your .mde file.
Tom
RE: Repeating Header
(OP)
Not yet.....
Waiting for CIS to recover the file. I am currently attempting to recreate what I did on a copy, but am having trouble. Below is what I currently have, perhaps you can see or dont' see a needed part:
Module: Continued
Option Compare Database
Option Explicit
Public CurrentGroupVal As String
Function SetGlobalVar(Inreport As Report)
CurrentGroupVal = Inreport!SetGroupVal
End Function
Function SetContinuedLabel(Inreport As Report)
If Inreport.Page <> 1 Then
Inreport!ContinuedLabel.Visible = _
IIf(Trim(Inreport!CheckGroupVal) = _
Trim(CurrentGroupVal), True, False)
End If
End Function
Report:
Page Header On Print: =SetContinuedLabel([Report])
Page Footer On Print: =SetGlobalVar([Report])
Text Box in Header: CheckGroupVal
Control Source: Area
Visible: No
Text Box in Footer: SetGroupVal
Control Source: Area
Visible: No
Can you see where I may have missed something?
Other than that, the grouping is set to Keep With Fist Detail.
I hope to have my restored database shortly after lunch, but who knows.....
Thank you for any and all help,
PBrown
RE: Repeating Header
PBrown
Well, I did spot a couple of things.
1. You should have a capital "R" in report in "InReport"
2. My setting for Sorting and Grouping is Group On "each value", and Keep Together is "No". However this may work differently in Access 97. Not sure
Here's the code for the module if you just want to copy it and paste it in.
Option Compare Database
Option Explicit
Public CurrentGroupVal As String
Function SetGlobalVar(InReport As Report)
CurrentGroupVal = InReport!SetGroupVal
End Function
Function SetContinuedLabel(InReport As Report)
If InReport.Page <> 1 Then
InReport!ContinuedLabel.Visible = _
IIf(Trim(InReport!CheckGroupVal) = _
Trim(CurrentGroupVal), True, False)
End If
End Function
Hope CIS can recover your file.
Tom
RE: Repeating Header
PBrown
Just to bring a little levity to a frustrating situation, an image sprung to mind. Do you remember the old movie "The Russians Are Coming, The Russians Are Coming", starring Alan Arkin, Joe E. Brown, etc.?
Throughout the entire movie, Joe E. Brown is trying to play Paul Revere, keeps riding his mule around and around the pen because the mule won't go through the gate so that Brown can go and warn everybody of the impending danger.
So I see you there on your Access mule, but the durn thing just won't go through the gate.
Oh well...
Tom
RE: Repeating Header
PBrown
Still puzzled why my second last code works here but not there. So I converted my Access 2000 file to Access 97, and it was still fine at this end.
Tom
RE: Repeating Header
(OP)
It finally went throught the gate!....lol....
I don't know what happened, but once the original file was recovered and I cut and pasted its module and on print, ect into the copy I have been working on, everything worked just fine. This has taught me that if I intend on "tweeking" a database, make a copy of it first, not just copies of the reports. That way, if I accidently delete a working report while am going through cleaning it up, the report can be recovered by the back up or copy.
As a side note, even with the keep together set to "With first Detail". I simply "touched up" the page header and sqeezed, "Continued..." in. Now it looks as if it is apart of the group header.
I have even used the items from the report (the on print, and text boxes) on a different report for the same purpose and it works like a charm. I don't like to speek too soon, but it looks as if this case is closed (Knock on Wood)! Needless to say, I am very greatful for your help in this *Mind blowing* problem, however, this is something I am sure to use in other DB's. Therefore, I am off to do some finishing touches on a few switchboards for easy user navigation.
Perhaps one time, you will have a problem that I can help with, and I am sure I will "see you around" Tek-Tips
Once again, Thank you for all your help!!!!
PBrown
RE: Repeating Header
PBrown
You are welcome! The important thing is that it's working. You can feed the mule some hay and let 'er rest!
Have fun.
Tom