Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Repeating Header 3
- Thread starter pbrown2
- Start date
-
2
- #2
-
1
- Thread starter
- #3
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
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
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
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
- Thread starter
- #5
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
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
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
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
- Thread starter
- #7
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.
Let me know if that works.
Tom
This is the link that should take you directly there.
Let me know if that works.
Tom
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
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
- Thread starter
- #10
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
Thank you for any and all help,
PBrown
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
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
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
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
- Thread starter
- #12
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
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
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
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
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
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
- Thread starter
- #15
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
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
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.
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.
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
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
- Thread starter
- #18
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
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
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
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
- Thread starter
- #20
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
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
- Status
Similar threads
- Locked
- Question
- Locked
- Question