If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
REPOST: Maintaining Page Numbers with multiple reports.
Having been growled at for being unaware of protocol I have taken
advice from an MVP before sending this. If this offends protocol I apologise in advance. This is a repost of a thread with subject: Maintaining Page Numbers with multiple reports The problem is to have number continuity with a series of reports and at the same time prepare a contents list. I have the first of five reports printing as planned. The problem is that the second report is numbered as expected but the page number does not increment and the tblContents is not populated. I have copied the post from the previous thread under my sig. The VBA follows: Option Compare Database Option Explicit Dim intLastPage As Integer ' =========== This is the Second Report Sponsor Listing next is PostCode Private Sub Report_Open(Cancel As Integer) ' On the FIRST report that is run ' re-set the table field to Zero. ' CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;", dbFailOnError ' On ALL the reports, including the first one DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") End Sub ' ============== Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage End Sub ' ============= Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) ' I've combined your Report Footer Format and Print events here. ' Your previous intPageNumber is not needed here. ' We'll use [Page] instead. CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";", dbFailOnError CurrentDb.Execute "Update tblcontents Set tblContents.PostCode = " & [Page] + 1 & ";", dbFailOnError End Sub ' ================ Thanks, Robin Chapple ====Previous Thread Follows===== On Sun, 31 Jul 2005 20:31:49 +1000, Robin Chapple wrote: Robin, See my comments marked with *** in line below. *** snipped *** I have returned to this project following some domestic trauma. The first report has 30 pages. It correctly sets intLastPage to 30 and puts 31 into the contents table for the first page number of the next report. I have now added the code to the second report which has just three pages. The first page in page number 31 as expected, The second page is page 62 and The third page is page 93 which looks like the second report second page number is second report first page plus the "intLastPage" and so on. Here is the VBA: Option Compare Database Option Explicit Dim intPageCount As Integer *** You have combined 2 different posts from 2 different threads in 2 different news groups. intPageCount is not needed as it serves the same purpose as intLastPage. See *** below Dim intLastPage As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage *** This is not the correct event for this. It must go in the Report Header Format event, not the Page Header. You want to increment just the first page of each report by 30, not each page by 30. The report itself takes care of incrementing the second, third, etc. pages. The Page Header event is run on each page, therefore you were getting 30 added to each page. The code in the Report Header is run just once per report. The first page of the second report is 31 (1+30), the next page is 32 (31 + 1), the third page is 33 (32 + 1), etc., which is what you want. End Sub Private Sub Report_Open(Cancel As Integer) DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") *** This is OK. End Sub Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) intPageCount = [Page] CurrentDb.Execute "Update tblcontents Set tblContents.District = " & [intPageCount] + 1 & ";", dbFailOnError *** This is OK, but you can combine this code with the code in the Report Footer Print event. End Sub Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) DoCmd.SetWarnings False DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";" DoCmd.SetWarnings True *** This is OK, but you can include the above Format event code with this. End Sub =============== Thanks, Robin Chapple Here is what the entire code will look like. I've changed the DoCmd.RunSQL to CurrentDb.Execute statements. Using the Execute statement instead of RunSQL there is no need for SetWarnings False and SetWarnings True. Watch out for improper e-mail line wrapping on the longer lines. Option Compare Database Option Explicit Dim intLastPage As Integer =========== Private Sub Report_Open(Cancel As Integer) ' On the FIRST report that is run ' re-set the table field to Zero. CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;", dbFailOnError ' On ALL the reports, including the first one DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") End Sub ============== Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage End Sub ============= Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) ' I've combined your Report Footer Format and Print events here. ' Your previous intPageNumber is not needed here. ' We'll use [Page] instead. CurrentDb.Execute. "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";", dbFailOnError CurrentDb.Execute "Update tblcontents Set tblContents.District = " & [Page] + 1 & ";", dbFailOnError End Sub ================ I hope this explains why you were getting such large page numbers, and gets you on-track again. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#2
|
|||
|
|||
Having been growled at for being unaware of protocol I have taken
And there was an apology. Get over it :~) that the second report is numbered as expected but the page does not increment and the tblContents is not populated. I You are using the print event of the REPORT footer. You want the PAGE footer if you are going to update something every page, and the REPORT footer for something you update at the end of the report. Use the PAGE footer if you are adding 1 on each page. Don't use the PAGE footer if you are using the [page] value and letting it auto-increment. Also, you need to check the PrintCount/FormatCount to make sure you don't add multiple times. Sometimes sections get formatted multiple times before they are printed, or printed multiple times. (davd) "Robin Chapple" wrote in message ... Having been growled at for being unaware of protocol I have taken advice from an MVP before sending this. If this offends protocol I apologise in advance. This is a repost of a thread with subject: Maintaining Page Numbers with multiple reports The problem is to have number continuity with a series of reports and at the same time prepare a contents list. I have the first of five reports printing as planned. The problem is that the second report is numbered as expected but the page number does not increment and the tblContents is not populated. I have copied the post from the previous thread under my sig. The VBA follows: Option Compare Database Option Explicit Dim intLastPage As Integer ' =========== This is the Second Report Sponsor Listing next is PostCode Private Sub Report_Open(Cancel As Integer) ' On the FIRST report that is run ' re-set the table field to Zero. ' CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;", dbFailOnError ' On ALL the reports, including the first one DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") End Sub ' ============== Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage End Sub ' ============= Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) ' I've combined your Report Footer Format and Print events here. ' Your previous intPageNumber is not needed here. ' We'll use [Page] instead. CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";", dbFailOnError CurrentDb.Execute "Update tblcontents Set tblContents.PostCode = " & [Page] + 1 & ";", dbFailOnError End Sub ' ================ Thanks, Robin Chapple ====Previous Thread Follows===== On Sun, 31 Jul 2005 20:31:49 +1000, Robin Chapple wrote: Robin, See my comments marked with *** in line below. *** snipped *** I have returned to this project following some domestic trauma. The first report has 30 pages. It correctly sets intLastPage to 30 and puts 31 into the contents table for the first page number of the next report. I have now added the code to the second report which has just three pages. The first page in page number 31 as expected, The second page is page 62 and The third page is page 93 which looks like the second report second page number is second report first page plus the "intLastPage" and so on. Here is the VBA: Option Compare Database Option Explicit Dim intPageCount As Integer *** You have combined 2 different posts from 2 different threads in 2 different news groups. intPageCount is not needed as it serves the same purpose as intLastPage. See *** below Dim intLastPage As Integer Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage *** This is not the correct event for this. It must go in the Report Header Format event, not the Page Header. You want to increment just the first page of each report by 30, not each page by 30. The report itself takes care of incrementing the second, third, etc. pages. The Page Header event is run on each page, therefore you were getting 30 added to each page. The code in the Report Header is run just once per report. The first page of the second report is 31 (1+30), the next page is 32 (31 + 1), the third page is 33 (32 + 1), etc., which is what you want. End Sub Private Sub Report_Open(Cancel As Integer) DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") *** This is OK. End Sub Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) intPageCount = [Page] CurrentDb.Execute "Update tblcontents Set tblContents.District = " & [intPageCount] + 1 & ";", dbFailOnError *** This is OK, but you can combine this code with the code in the Report Footer Print event. End Sub Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) DoCmd.SetWarnings False DoCmd.RunSQL "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";" DoCmd.SetWarnings True *** This is OK, but you can include the above Format event code with this. End Sub =============== Thanks, Robin Chapple Here is what the entire code will look like. I've changed the DoCmd.RunSQL to CurrentDb.Execute statements. Using the Execute statement instead of RunSQL there is no need for SetWarnings False and SetWarnings True. Watch out for improper e-mail line wrapping on the longer lines. Option Compare Database Option Explicit Dim intLastPage As Integer =========== Private Sub Report_Open(Cancel As Integer) ' On the FIRST report that is run ' re-set the table field to Zero. CurrentDb.Execute "Update tblPage Set tblPage.intPageNumber = 0;", dbFailOnError ' On ALL the reports, including the first one DoCmd.Maximize intLastPage = DLookup("intPageNumber", "tblPage") End Sub ============== Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [Page] = [Page] + intLastPage End Sub ============= Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer) ' I've combined your Report Footer Format and Print events here. ' Your previous intPageNumber is not needed here. ' We'll use [Page] instead. CurrentDb.Execute. "Update tblPage Set tblPage.intPageNumber = " & [Page] & ";", dbFailOnError CurrentDb.Execute "Update tblcontents Set tblContents.District = " & [Page] + 1 & ";", dbFailOnError End Sub ================ I hope this explains why you were getting such large page numbers, and gets you on-track again. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Page number difficulties | Tjodrik | General Discussion | 3 | May 9th, 2005 02:54 PM |
Changing Page Numbers | Wood | General Discussion | 1 | August 14th, 2004 05:54 AM |
Consecutive Page Numbers on Multiple Reports | Randal | Setting Up & Running Reports | 1 | July 12th, 2004 06:23 PM |
Printing Page Numbers for Multiple Worksheets | Greenguitar | General Discussion | 1 | June 14th, 2004 03:12 PM |
TOC using paragraph numbers instead of Page numbers | cmac | Page Layout | 8 | May 16th, 2004 01:32 PM |