Access Report does not page number properly

Do you have textbox somewhere in the report with a control source of
=Pages
It can be visible or not but should correct the problem.

There are several expressions for page numbers on reports (with or without the total pages value).  Open the Expression Builder and select the one you want as a textbox's control source.

Also . . . give those labels meaningful names!
Page-number-expressions.jpg

This is why you can't simply insert code into your database without knowing the consequences.
Often a fix for one issue will lead to another issue.
The fix for that issue will introduce yet another issue, ad infinitum.

Please bear in mind that you already have a fair amount of code on this report already...

Just my 2c

TheNelson...I will check

Boaq2000 ...this is the second year of running this and this is the first time it appeared like this.

I have in the header
="Page " & [Page]

Where do you suggest putting the =Pages, I put it in the header (not visible) and then in the offending footer (not visible) and no changes.

ok,

Like I said it was just my 2c.

I am sure that Helen of Nelson can get you sorted.

Put it in the page header (or footer), not the report header or footer.  It is a page number, so it needs to be in a page header or footer.

No problem...just wanted to let you know...the ugly bug (problem) decided to show up this year...although last year it was patched and patched because of changes and changes.

Ok...will try and let you know (page header).

I put in the page header. It did not help.

I had =1 in the grouping and sorting and removed it. That did not help either. Do I need to remove the:
me.page = 0 in the groupfooter ?

I am going to try...that...I have backed up so I can back track.

What is the purpose of putting Me.Page = 0 in the group footer (I don't know if it would work, in any case)?
What is the purpose of 1 in the grouping (and which group is it)?
Page numbering is pretty simple, usually.  Just put one of the standard expressions in a textbox in the page footer (or header), and it should print the correct page number.  Are you trying to set it in code somehow?  That could be the problem.

No...that solved the problem if one customer is run. But if all are run, then the page numbers increase and do not restart between customers. I am trying this on screen, instead of printing.

Sorry about the delay in response...I had a trouble call to take care of.

If I just print one customer then the regular printing page commands work OK...

but if I want to print all customers and each customers' page numbers reset to begin with 1 for each customer then taking me.page = 0 out causes a problem, ie. each customer has a starting number other than 1 for their first page.

I do not remember why I put =1 in the grouping and sorting, too many revisions ago.

That is all customers together in one print job.

If I understand correctly...

Your Group, is a group over one customer. It has a property "page break after section", so that the next customer will start on a fresh page.

In the format event, you set Me.Page = 0. This means that the next page will be numbered 1. This is standard practice. If your page numbering is in the page footer, you should instead use Me.Page = 1 in the customer's group header, but if you want the page numbering in the page header, your only choice is the code you have.

Now consider this problem: what if there isn't enough space at the bottom of the page? The Format event will be triggered, setting Me.Page = 0. But then, Access finds out it can't put the footer on that page, retreats, skips to the next page (now number 1), and prints the footer there -- the problem you mention.

How to get around that?

Normally, you would put Me.Page = 0 in the Print event, because that event occurs only once (unlike the Format event). But apparently you have tried that, judging from your commented code. Have you tried *only* the Print event?

Your comments also indicate "places page = 1 on Report Footer pages". If you really number the pages in the footer, it's much easier to use Me.Page = 1 in a group header...

I hope this helps
(°v°)

V...sorry I did not get back to you..another workstation with a problem.

The page number is in the header. And what you describe is correct. I will try in the Print only.

The user does look (view) the generated pages before they print.

Thanks...I will get back.

V...when I took out the me.page=0 (format) and just put it in the Print, the numbering corrected itself. But I realize why I put it in format when I viewed all of the customers.

If the users want to look at all of the customers, but skip some, they put a page number in the the
Statements-problem.doc

Oops...I was not finished...

This is what happens if I remove the me.page=0 in the format.

They are looking at the entire customer statements. They put in the page number they think is close to one they want to print. Then the page number on the report changes to the number they have skipped to. So if they wanted to skip to page 150, then the page number would be 150 on screen preview of the page instead of page 1.

That is irrelevant. The box displays the physical page number, as if you never changed it through code. But I might have misunderstood your comment.
(°v°)

I will give  you a screen shot.

This is when they select all customers to print. It starts at the first customer which has page 1.

Then is they start skipping around. Here I picked page 150 and the page number on the page you see should have been Page 3.
Statment-problems-2.doc

I see, my mistake. You mean that if the report isn't printed in sequence, the Page=0 instruction isn't run, so the result is wrong. (I wrote my previous comment before the second part of your explanations).

You can still set the page number in the format event, but you need to be ready to undo it in the retreat event. Store the old page number in a module variable, and reset it if the section retreats. I haven't tried this, mind you. It's just an idea, but it's what the retreat event is meant for.

I was under the impression that the print event occurs also in print preview, but I was mistaken. If you skip to page 100, all format events occur (they are needed for pagination; to choose what you will see on page 100) but not the print events unless the page is actually displayed... If you only use [page down], it works.

(°v°)

Sorry for not responding...I have been working on a problem away from my desk.

I basically understand the theory. I am not sure how to go about it yet. If they move a page forward or backward from the jumped page, what needs to happen (on retreat also) ?

In the format event, store the current page number, and then set it to zero. In the retreat event, set it back to its previous value.

The problem occurs if the section is formatted, doesn't fit, retreats, and prints on the next page. If you "undo" on retreat what you did on format, it should work. (Again, I didn't test this.)

(°v°)

I will try...I will not say it sounds simple...I may get cursed ...and be humbled...all in one swoop.

I am finishing up another problem ...and will get on this...thanks...I will get back with you tonight ...either way.

Sorry ...I do not know what time it is where you are...just let me know ...you may have other plans...

Ok...

Good and bad news....

Good news...that worked to solve the  problem when skipping customers. It starts at the correct page number.

Bad news...I left in and then took out the Me.Page=0 in On Print event and the last page is still putting Page 1, whether I print all the customers and just one.

Here is the new code

Option Compare Database
Option Explicit
 
Private mpage As Integer
Const cMaxSpace = 15000 '15120 is the bottom   ' adjust here!
 
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If (Me!price = 0) And (Me!TR_TRN_CD = "WTD") Then
        Me!theDesc = "Withdrawn"
        Me!theDesc.TextAlign = 3
   Else
        Me!theDesc = Me!price
        Me!theDesc.TextAlign = 3
   End If
 
End Sub
 
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim lngSpace As Long
    
    lngSpace = cMaxSpace - Me.GroupFooter2.Height - Me.Top
    If lngSpace < 0 Then lngSpace = 0
    Me.GroupFooter1.Height = lngSpace
    
End Sub
 
 
Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
mpage = [Page]
Me.Page = 0 ' places page = 1 on Report Footer pages
End Sub
 
Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
'Me.Page = 0
ind = True
End Sub
 
Private Sub GroupFooter2_Retreat()
Me.Page = mpage
End Sub
 
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
ind = False
End Sub
 
 
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
End Sub
 
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
End Sub
 
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = globalrecsource
End Sub

Select allOpen in new window

I'm glad one problem is solved... but whenever you print a single customer, you run the risk of having the wrong page number on the last page.

If we could determine that the group is the last, we could disable the trick (there is no next customer, so there is no need to set Me.Page=0). This starts to sound like a workaround. I'll have to think about it.

(°v°)

That's fine...but I do get the same problem with all the customers and one customer too !

The last page ...if it does not have detail, but prints the footer...is Page 1

I have been wondering if I could control the page number by comparing the grouping ?

I think I will try.

Well...that worked for a single customer and fixed the page numbering on the last page.

But on all the customers ...I am getting invalid "use of null" before it appears in the preview.

Always a speed bump.

Could there be some interaction with your other group footer? In particular, does it support the Height=0 (the section must be blank for that to work)?

> I am getting invalid "use of null"

That should be reasonably easy to fix. What are the lines you added just before getting that error? If you don't remember, comment out pieces of code until you locate the line. Then, add some tracing mechanism (debug.print or msgbox) to display the value that you think should have a value but clearly doesn't...

You can also re-post your current state of the code.

(°v°)

Invalid use of Null - debug points to the line.

It happens at GroupHeader0_Format
msort=me.[by_sort]

It has a comment above it that says "helps page number if doing one buyer"

Option Compare Database
Option Explicit
 
Private mpage As Integer
Private msort As String
Const cMaxSpace = 15000 '15120 is the bottom   ' adjust here!
 
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If (Me!price = 0) And (Me!TR_TRN_CD = "WTD") Then
        Me!theDesc = "Withdrawn"
        Me!theDesc.TextAlign = 3
   Else
        Me!theDesc = Me!price
        Me!theDesc.TextAlign = 3
   End If
 
End Sub
 
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim lngSpace As Long
    
    lngSpace = cMaxSpace - Me.GroupFooter2.Height - Me.Top
    If lngSpace < 0 Then lngSpace = 0
    Me.GroupFooter1.Height = lngSpace
    
End Sub
 
 
Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
' helps page numbering if doing one buyer
mpage = [Page]
If msort <> Me.[by_sort] Then
   Me.Page = 0 ' places page = 1 
End If
End Sub
 
Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
' does not help page numbering
'Me.Page = 0
ind = True
End Sub
 
Private Sub GroupFooter2_Retreat()
' keeps the page numbers good if skipping around
Me.Page = mpage
End Sub
 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
' helps page numbering if doing one buyer
msort = Me.[by_sort]
End Sub
 
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
ind = False
End Sub
 
 
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
End Sub
 
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
End Sub
 
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = globalrecsource
End Sub

Select allOpen in new window

So, 'msort' is a String, while [by_sort] is either a control or a field. Both can be Null, and setting Null to a string will raise the error. Ensure it isn't, use Nz() to convert Null to "", or kindly explain what the [by_sort] should contain.

(°v°)

By_sort is a field that contains the shorten alphabetic value for a name the customer without spaces or any special markings, but now that you make me think about it. The first few pages contain blank customers.

I will try the nz and get back.

NZ solved the Invalid use of Null.

Is there a way to ask for more points for all this guidance ?

Ok...that solved the last page problem, but now if I go forward to page 150, the page number starts at 150 for the customer that hits there, but the next customer starts at page 1. If I go backwards then this is what happens...Page 2...Page 1...Page 150...Page 149...

If I do pagedown everything is ok, but I know the users, they are going to try to guess what page a customer is and jump to it (not necessarily to print, but to look at them).

I am trying to read back on the solution to the recent problem.

I did the following and everything is working now...I am not going to remove the lines I commented out.

Notice that GroupFooter2_Print
mpage = 0

is back

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
' helps page numbering if doing one buyer or all
'mpage = [Page]
'If msort <> Me.[by_sort] Then
   Me.Page = 0 ' places page = 1 on Report Footer pages
'End If
End Sub
 
Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
' does not help page numbering
Me.Page = 0
ind = True
End Sub
 
Private Sub GroupFooter2_Retreat()
' keeps the page numbers good if skipping around
Me.Page = mpage
End Sub
 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
' helps page numbering if doing one buyer
'If Nz(Me.by_sort) = "" Then
'   msort = ""
'Else
'   msort = Me.[by_sort]
'End If
End Sub

Select allOpen in new window

> Is there a way to ask for more points for all this guidance ?

No, there isn't, and that's quite OK. A simple "thanks" is worth more than points, anyway. If the question evolved and there was a clear break to a new topic, you could close and use "ask a related question" to continue the process. This wasn't the case.

> everything is working now...

That is great! Kudos to you. It seems that in the end, simplifying was the key, no? It's often the case. When I start deleting more code than I'm writing, I know my project is close to completion!

Cheers!
(°v°)

well...I thought I would test again. And the last page number problem is back. The last page is set to Page 1.

I need to try to remember what I did the first testing that it worked correctly

From your original question

> The problem is that if the last page just has a footer and no detail, then it numbers the page as 1. [... The] same results for those customers whose last page just has footer information.

Is that still the case? Or is it really only on the very last page of  the report, when that last page has no detail section?

I must say I'm out of ideas. I'm still wondering if the first group footer, with variable height, can influence this. For example, if lngSpace<0, it means there will be no room for the second footer, so Me.Page=0 should only be set on the second formatting pass, when FormatCount=2. If not, the first footer will "push" the second footer to the bottom, and Me.Page=0 should be set at the first pass.

The avoids "undoing" the page numbering on retreat, but I have no idea whether it will influence anything else.

When debugging reports, I sometimes put some unbound text boxes on relevant sections and write debugging information in them from code. For example:

    txtTmp = "page " & Me.Page & " reset on pass " & FormatCount

and similar messages. Other times, I use plenty of Debug.Print to write a log of events, preview, page down to the problem, and close. Then I try to understand what went on. Finally, I almost always give different background colours to the sections, so I can understand how the report is built.

Tell me of your progress, we will end up figuring this out.

(°v°)

I have not given up...just have been under the weather. I am getting back on this later today.

proffittware,

Not meaning to interrupt here, but remember my post here:
http:#a25627003

There are just somethings that you will ask the report to do that will conflict with some other feature that you might want.
Forcing you to create workarounds for workarounds.

If you posted a sample that clearly demonstrated the issue, and along with that posted a clear example of *exactly* what you needed the output to be, this may save us some time.

The reason is that, in addition to the code you already have, there are literally dozens of Properties and Layout options that we are not aware of.
Any of which that might be effected by (or have an effect on) your desired result.

As any expert will tell you: "Anything is Possible"
How much time and energy you are willing to spend on this is another story.

This seems to be the crux:
  "The problem is that if the last page just has a footer and no detail"
How is your report set up so that this is even possible?
If there are no detail records, then there should be no last page with just a footer.

Again, I don't want to interrupt the rapport (no pun intended) you have with harfang.
It's just that, perhaps at this point a sample might save us form making suggestions on a report that we can't see all of the attributes of.

JeffCoachman

I have posted a sample of the code  and output above (10-22 @ 11:10 and 11:50). I have to go out for about an 1.5 hours. But will be back at work and trying some.

Here is the report in design mode screen shots. There are two pages because I could not get a print screen on one page.
Statement-problems-3.doc

Again, I would like:
An actual sample of the database file itself, that displays the issue.
A clear, graphical explanation of the output you are expecting.

harfang (5:29)
<The problem is that if the last page just has a footer and no detail, then it numbers the page as 1. [... The] same results for those customers whose last page just has footer information.>
<Is that still the case? Or is it really only on the very last page of  the report, when that last page has no detail section?> The answer to this is yes, at this point I have only run into the last page with only the second footer and not detail that does not have the correct page numbering.

Boaq2000...your request will take a while...the front end is over 20M and then there is the back end 10M which I will have to make it local. Maybe put both in one MDB.

I am going to have to remove data to protect the customers and the company

Well...here is the database and a graphical flow and description of what the users wants.


New-fe-102409-1-test.mdb
Statements4.doc

I forgot to add this, User can select one buyer (customer), horse or all.

If they select all, they want to be able to jump around (back and forth) try page 150. The page number must represent Page 1 for the start thru ? for the customer they want to look at or print.

Out of respect for all the work harfang has done on this, ...I will refrain from commenting, until you have worked through his above post.

;-)

Jeff

I will try and get back ...

Please Jeff! If you have opened the database, please do post your comments, it would be a loss not to. If if haven't, it's another matter. -- (^v°)

Well...the first thing ...is removing the page header ...also removes the page footer...which they want the wording at the bottom of the page...on the last page only...

Sorry. I meant: delete the textbox and reduce the size to 0. Indeed, if you use the View menu, it removes both the page header and footer.

(°v°)

No problem...

I am going to test some more, but I have been able to get consistent results with the changes you recommended and some others I added to the PageFooter,

Again ...I am going to test some more...

Option Compare Database
Option Explicit
 
Private mpage As Integer
Private msort As String
Const cMaxSpace = 15000 '15120 is the bottom   ' adjust here!
 
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If (Me!price = 0) And (Me!tr_trn_cd = "WTD") Then
        Me!theDesc = "Withdrawn"
        Me!theDesc.TextAlign = 3
   Else
        Me!theDesc = Me!price
        Me!theDesc.TextAlign = 3
   End If
 
End Sub
 
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim lngSpace As Long
    
    lngSpace = cMaxSpace - Me.GroupFooter2.Height - Me.Top
    If lngSpace < 0 Then lngSpace = 0
    Me.GroupFooter1.Height = lngSpace
    
End Sub
 
 
Private Sub GroupFooter2_Print(Cancel As Integer, PrintCount As Integer)
ind = True
End Sub
 
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.Page = 1
Cancel = True
End Sub
 
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
ind = False
End Sub
 
 
Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
' do not put page footer unless actual last page
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
   Me.Page = 0
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
 
End Sub
 
Private Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
' do not put page footer unless actual last page
If ind Then
   Me.Label113.Visible = True
   Me.Label114.Visible = True
   Me.Label115.Visible = True
   Me.Page = 0
Else
   Me.Label113.Visible = False
   Me.Label114.Visible = False
   Me.Label115.Visible = False
End If
 
End Sub
 
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = globalrecsource
End Sub

Select allOpen in new window

Ok...I have put the code through many passes.

The last page has always been correct. Every new customer starts with Page 1 and increments properly.

Using the pagedown and pageup the pages are number correctly.

But ...if I jump to a page number...the paging is not correct. It always puts page one on the first page that is displayed even though it actually may be the second or third page.

Can you post another sample of the dB as is.
Please include the explicit, specific steps to see this issue.

I was a bit taken aback by all the queries than need to be run to see the report for "all"...

Yes...will do...yes on the queries...It was a DOS program converted and then changes have occurred often because of the variations and customer service.

What I meant by converted was the data.

Here is the new DB and a word document showing the last problem I have to solve. If it is not possible...just let me know.
New-fe-102509-1-test.mdb

Oops...I hit submit before attaching Word document.
Statement5.doc

harfang...I did try...I will try again.

do I remove the me.page=0 from the footers ?

harfang...I just did not get it last night....with those instructions and today's ...it is working GREAT !

I do not know how to thank you...for sticking with me...but ...

Thank you sincerely.
proffittware

> ...it is working GREAT !

That is very good news; I'm glad for you.

> I do not know how to thank you...

Don't worry, you just did. Success with your project!
(°v°)

I hope you did not get any grief over looking at my code.

Not at all! What makes you think that? Your code is quite clean and readable, especially compared to the average on EE...
(^v°)

Well, from what I gathered...our time differences may have put you at your work when you were trying (did) help me.

And thanks for the compliment on my code.