Solved

MS Access - Reset page number total and pages by Group to Work on Header vs Footer

Posted on 2010-09-22
27
1,113 Views
Last Modified: 2012-05-10
I have followed the standard solution provided by Microsoft support to do this, and it only works if the page numbers is in the footer.

http://support.microsoft.com/kb/841779

The form I am trying to build requires the page of page concept to exist in the Page Header Section.  Not in the footer sections.    So by moving the following Text Box around between Footer and Header it works or fails.  

=[OrderNo] & " -  Page " & [Page] & " of " & [GroupXY]

Open in new window


How would I modify the code to allows this to work on the Header instead? testForm.mdb
0
Comment
Question by:KylePN
27 Comments
 
LVL 84
Comment Utility
Leave it in the Footer, and set that to Visible = False. Make sure to Name that Textbox something, like "txPNFooter"

Add a Textbox to your Header, and set the ControlSource to the name of your Footer control:

= [Me]![txPNFooter]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Try LSM's suggestion first.


The way Access reads Report Data, to display it, may make this difficult
(Page breaks, Retreat Event, two pass printing, ...etc) and any other combination or design factors or Property settings may effect this.

Normally (but not always) you can swap "Group" data between group headings.
Page header/Footer data may be tricky...
This is because Access must first "read" all the data on page first, then determine what "Page" it is on.

Obviously, this might not be possible in the page header, because no records for that page have been read yet.

But again, this may all be irrelevant if LSM's post works for you...

(I think I may have had a fix for this, I'll check tonight if your are interested)

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
You may be able to workaround this buy creating a query based on the PageNumbers table and creating a new field to add (or subtract) a certain value...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Is it a big deal just to keep it in the footer?
0
 

Author Comment

by:KylePN
Comment Utility
In response to boaq2000 I am trying to re-produce a very specific form as a report called a DD250.   The page numbering is in the header unfortunately.  

For some reason (I am not an expert at using the controls like this) it does not like it.  It wants to prompt for Parameter Value "Me".
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
try:

=[txPNFooter]
0
 

Author Comment

by:KylePN
Comment Utility
I had tried that already with no luck.  It does the exact same thing as if simply putting the original text box in the header.

I thought maybe I could apply some logic to the text box in the header, but I am not thinking that will work because for page between intial and end page I have no way to determine what page I am in..

I could force them to always print one document at a time, instead of by range but the requirement is to print/generate the reports by range.    

You would think the ability to apply page x of y by group would exist by default in MS Access regardless if you want it to show in the header vs the footer.



 
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<You would think the ability to apply page x of y by group would exist by default in MS Access regardless if you want it to show in the header vs the footer.>

As I stated, Access "Reads" the records sequentially, hence, it can't determine how many records will fit on the page until it formats the page.
Things like: Page breaks, can grow, ...etc, will effect how many records fit on a screen.
This is why Access has "Two Pass Printing" behind the sceens.
(For lack of a simpler explanation)... One Pass to get the records and the second pass to get the Page and Page count just right..
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
There is another technique for doing this...

I am investigating it now
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Check you sample,
AFAICT it is not accurately calculating the sheets either (in the header or the footer)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Please confirm the above first...
0
 

Author Comment

by:KylePN
Comment Utility
I am not quite sure I follow.  In the test database if I add more records or remove records the paging in the bottom appears correct based on the lines and orders I put in there.    I have hidden the position, but if you unhide it, and add back the order no on the header everything looks good.

An aside:  
-------------------
The DD250 form can only allow 4 records to print per page (based on the detail we populate).  There always has to be 4 records per page in order to enforce the lines on the form to print correctly.  I tried on Format options to draw the lines, but it had more issues than it was worth.  So my backup solution was to determine n, where n = count(lines) Mod 4.   For each n a case exists to add 1,2, or 3 fake records.  Those fake records are those with position > 9999 since the ERP can never go past this number.    

In the version I have the code will delete the fake records before re-adding them in case the order line detail changes.  (ie someone cancels, deletes, adds a line to an order)    For the test database provided you have to delete them manually by running the query:  qryDeleteFillerLines.  the VB Code for adding them back exists within OrderData form.  Simply created a button in there to run it.  

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Sorry, I misdiagnosed the issue.
Your sample seems to be functioning correctly for the page footer...

Still working on a workaround.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Try this,
It uses another popular technique:
http://www.mvps.org/access/reports/rpt0013.htm
...to reset the Group page numbers.
I modified it to do the same in the Page Header.

I also modified it to get the correct count of the last Group's page count.

Let me know if the basic functionality is acceptable.

JeffCoachman

Access-EEQ26492414ResetGroupPage.mdb
0
 

Author Comment

by:KylePN
Comment Utility
I apologize for the delayed response.  I was dealing with some emergency issues this past week.   I am unable to look at the report design to see if the paging is in the page header.  If it is, it might work.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<I am unable to look at the report design to see if the paging is in the page header.  If it is, it might work.  >
The page numbering is on the top of every page.

I posted this as an MDE file just to see if the functionality was what you wanted.

Again, let me know.

;-)

Jeff
0
 

Author Comment

by:KylePN
Comment Utility
I would make the total 1 of 36 pages invisible, but yes I think that would work by Order.  As I mentioned as long as I can put it in the group header, or even in the order header it will work.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
The issue with putting the page numbering in the Group Header is that the Group header typically only appears on the first page of the Group.
If the Group spans more than one page, how can the Group Header display the page number if it only appears on the first page for a 6 page group?
Make sense?

To workaround this, you could try setting the "Repeat Section" property of the Group header to "Yes".
Unfortunately, the code will incorrectly display "1 of 1" for all groups.

Can you restate why this Must be in the Group Header?
AFAICT, putting this in the Page Header results in the exact output you require.
In other words, if the report displays correctly, who cares what section anything is in?, the user will never know the difference.
The Report will correctly display the Page number and total Pages for each group.

As far as the total page count is concerned, ...yes, if you don't want to see it, either set the Visible property of the Control to No, or delete the control entirely.

Again, let me know.

;-)

JeffCoachman
0
 

Author Comment

by:KylePN
Comment Utility
As already mentioned the form is very specific.   I cannot exactly send a custom form/report to the government and expect them to except it.  Would kind of be like making up my own tax form and submitting it to IRS and expecting them to accept it.  Their form has the paging in the header.    If I could accept it in the footer then I would never have posted this in the first place.

At this juncture in time since it is not easily resolved, and there appears to be no solution I am going to force them to print one order at a time.  This is not what they want, but it will have to work.   Most people prefer to print in ranges if possible.   I had hoped this would be simple but I do not have the time to wait for a solution any more.

I appreciate all the time you spent helping.   Thank you,
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
< If I could accept it in the footer then I would never have posted this in the first place.>
?
My recent sample had nothing to do with a "Footer"?
So I am confued...
Did you open the sample and view the Report?
To repeat: my recent sample will display the correct Group Page and Pages number in the "Page header"

To recap, you originally specified that this needed to be in the "Page Header"
    <The form I am trying to build requires the page of page concept to exist in the Page Header Section.  Not in the footer sections. >
...and again here:
    <I am unable to look at the report design to see if the paging is in the page header.  If it is, it might work>

Now it seems that you are requesting this data in the "Group Header"
    <As I mentioned as long as I can put it in the group header>
...So you can see why I am a bit confused.

<I cannot exactly send a custom form/report to the government and expect them to except it. >
You cannot "Send" an Access Report to anyone anyway.
You can only "Export" the report to a format like:
PDF, Snapshot, HTML.

In these formats, the page number(ing) location is irrelevant because these formats do not contain Headers, Footers or Groups.
They only "Display" the report.

Unlike a Report in Crystal Reports (where the report can be exported and actually "contain" the source data), an Access "Report" cannot exist outside of the Parent DB Container.
If your intention all along, was to Export a fully functioning Access "Report" with Data included, complete with design view Headers and footers,...using MS Access, then this is not possible.

So, my sample simply "displays" the page number in the correct location.

In other words, once the IRS "Got" the PDF, Snapshot, or HTML file, they would open it and see that the page number where they wanted it to be.
My simulates the output you specified, just in the Page Header.


JeffCoachman





0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
After all these contributions, if the issue still unresolved, then refrase the issue here.
Supply a sample database with few records. And tell us what to do and expect what.
Make sure you benifitted from the experts' comments, and verify that they did not work for you, so I try to spend some time on the solution.
0
 

Author Comment

by:KylePN
Comment Utility
First, I think I created the issue by confusing Jeff with one of the statements. But I did not realize it.

I opened and reviewed the report twice, and commented.

My first comment said:
 I am unable to look at the report design to see if the paging is in the page header.  If it is, it might work.

To interpret this, it means yes I opened it up, looked at it, but could not determine how the layout was designed to see if it would work in the layout I have in mine. So I said if it is in the page header it might work. For me, it is difficult to say yes to something without being able to see where and how it works. If it was not in the pageHeader then it would not work.

Then I was asked again to look at it, and I responded back with:
I would make the total 1 of 36 pages invisible, but yes I think that would work by Order.As I mentioned as long as I can put it in the group header, or even in the order header it will work.

Here is where the confusion came in.  I did not realize I said "group header."   In my Group header was supposed to be page header.    
At this juncture in time I took the next response to mean it was not possible, not realizing he was outlining a different scenario.  
The reality is that I believe the solution he offered would work, but I took the next responses as if there was a flaw.   I did not want him to waste more time on this if there was no solution.    I apologize for my poor communication.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
KylePN,

No problem buddy.
;-)

Remember that I too wanted to make sure I was understanding the issue.
;-)

So to summarize:
My proposed solution would produce a Hardcopy of the report that would display the Group page and page number in the Page Header.
This produces the exact same output as if this same data was displayed in the Group Header.

Reports in Access cannot be "Sent".
You can only send an "Output" version of the report to anyone.

But this is not normally an issue, as the receiver merely wants the data displayed in the correct location, they care not how this output was designed.

For example, we send reports (as PDF's) to the State that must have certain info in the Page Header. This means that on the top of every page, this data must appear.

Sometimes this data is in the Group Header, but we simply set the Group Header to repeat for every page, thus creating the specific output.
Since the State only gets the PDF file to print, they don't care how the output is designed, only that it displays the data as specified.

So take a moment to review my sample again, if you find that it "Displays" the data in the correct location, I will dig up the MDB file (that you can see the code and how it is structured.)

If not, then can you indicate on a Markup of my report, the exact output you are expecting?

Thanks.

;-)

Jeff
0
 

Author Comment

by:KylePN
Comment Utility
The sample looks good.  
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
Here is the MDB file.

Notes:
In order to create a PDF from this report you can install a utility like this:
http://www.cutepdf.com/

If you need help with creating a PDF or Emailing the Report, let me know.

;-)

JeffCoachman
Access-EEQ26492414ResetGroupPage.mdb
0
 

Author Comment

by:KylePN
Comment Utility
Thank you.  I made the changes outlined within the .mdb and it works well.  I made a minor change to handle the one case that printed blank to show 1 of 1.    Really appreciate the help.   I will look into the cutepdf program if they require it.   I suspect they might.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now