[AccessD] FW: A2K2 - Report Grouping - Page Numbering

Jim,

In order to create a more usable TOC, I tried to add the Group Header to my
already existing page number printing routine.  Producing something like
this in my text file:
"1R-RN10/ESDS(21H) 5"
"1R-RN10/ESDS(22H) 6"
"1R-RN11/ESDS(21H) 7"
"1R-RN11/ESDS(21H) 7A"
"1R-RN11/ESDS(22H) 8"
"1R-RN11/ESDS(22H) 8A"

I created a text box control in the page footer and set it's source to a
text box control in the group header.  Then set a variable equal to the text
box control in the page footer.

Here's the weird part...if I open the report in preview mode and manually
page down through all the pages, I get exactly what I need.  But, if I open
the report in preview mode and try my "print to file" method, I get garbage.
It's almost as if the writing to the text file can't keep up with the print
routine.  Is that the likely cause, or is it something else?  Although
currently unreliable, obviously, I would prefer to utilize the "print to
file" method as it is much faster.  Any suggestions?



Mark



-----Original Message-----
From: Jim Dettman [mailto:jimdettman at earthlink.net] 
Sent: Wednesday, February 04, 2004 9:39 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] FW: A2K2 - Report Grouping - Page Numbering


Mark,

<<  Since this page numbering routine only
works at print time, I had to figure out a way to generate a TOC without
actually wasting a ream of paper.  Therefore, instead of actually printing,
I utilized "Print to file" and added an On Page event to write the page
number to a text file as it was "printed".
>>

  I was going to ask if you needed to do that.  FWIW one technique to do
that is to reference the Pages property somewhere (i.e.. a hidden text
control).  This forces the report engine into a 2 pass mode.  On the first
pass, it prints nothing so it can figure out the total pages, then goes back
and prints.

  You can use this to your advantage by inserting code to record your custom
page numbers to a table, thus building up a table of contents.  It is also
handy when you want to do Page x of y over a group rather then the entire
report.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark S.
(Newport News)
Sent: Wednesday, February 04, 2004 9:17 AM
To: '[AccessD]'
Subject: [AccessD] FW: A2K2 - Report Grouping - Page Numbering


Virginia,
The code is posted below.


Robert,
Thank you for your interest.  Although I am no expert, I do know how to code
in VB/VBA...it was my unfamiliarity with report events and their sequence
that prompted my posts.  Jim Dettman was able to guide me down the right
track.

<blog>
What prompted this unique page numbering sequence is military specifications
for a drawing submittal.  I maintain a database of electrical cables
utilized on the Seawolf class submarines.  When I need to print out a
routing of these cables, each cable gets its own page number, but the route
for a single cable may span several pages.  Thus I needed a page numbering
routine that may look something like "5, 5A, 6, 7, 7A, 7B, 8, etc.".  To
complicate matters further, three other circumstances had to be taken into
consideration.  First, the starting page number is a moving target during
drawing development.  Second, the following letters cannot be used
"I,O,Q,S,X,Z" (A little bit of history...this is a throw-back to yesteryear
when drawings were done by hand.  These letters if not written correctly,
could resemble numbers like 0, 1, 2, & 5, or, in the case of X, it could be
mistaken for T.)  Jim unwittingly handled this second problem quite nicely
without even knowing it.  Third, the resultant page numbers must be
translated into the drawing's table of contents.  For the current drawing,
there are 467 cable routing pages.  Since this page numbering routine only
works at print time, I had to figure out a way to generate a TOC without
actually wasting a ream of paper.  Therefore, instead of actually printing,
I utilized "Print to file" and added an On Page event to write the page
number to a text file as it was "printed".

All in all I'm a happy camper, primarily because it is done now, and my NEXT
drawing looks to be ~60-70% bigger than this one was.  The code may not be
as elegant as some would like...but it works:) </blog>


Mark


<code>
Option Compare Database
Option Explicit

Dim intMajor As Integer
Dim intMinor As Integer
Dim strPageNumber As String

Function GetPageChoice()
   Dim choice As String
   Do
      choice = InputBox("Enter a Starting Page Number:", _
         " Number Report", "1")
      If Not (IsNumeric(choice)) Then
         MsgBox "Value Entered is not a Number."
      End If
   Loop While Not (IsNumeric(choice))
   GetPageChoice = CInt(choice)
End Function

Private Sub Report_Open(Cancel As Integer)
   intMajor = GetPageChoice - 1
End Sub

Private Sub GroupHeader0_Print(Cancel As Integer, _
   PrintCount As Integer)
   If PrintCount = 1 Then
      intMajor = intMajor + 1
      intMinor = 0
   End If
End Sub

Private Sub PageFooterSection_Format(Cancel As Integer, _
   FormatCount As Integer)
   intMinor = intMinor + 1
   'IOQSXZ are intentionally removed from the list below.
   Me![txtPageNumber] = Trim(Format$(intMajor, "###") & _
      Mid$(" ABCDEFGHJKLMNPRTUVWY", intMinor, 1))
End Sub

Private Sub Report_Page()
   Dim strOutputFile As String
   Dim strPath As String
   strPath = "C:\Temp2\"
   strOutputFile = "DA4700-3808_PageNumbers.txt"
   Open strPath & strOutputFile For Append As #2
   'IOQSXZ are intentionally removed from the list below.
   strPageNumber = Trim(Format$(intMajor, "###") & _
      Mid$(" ABCDEFGHJKLMNPRTUVWY", intMinor, 1))
   Write #2, strPageNumber
   Close #2
End Sub
</code>



-----Original Message-----
From: Robert L. Stewart [mailto:rl_stewart at highstream.net]
Sent: Tuesday, February 03, 2004 4:10 PM
To: accessd at databaseadvisors.com
Cc: Mitsules, Mark S. (Newport News)
Subject: Re: A2K2 - Report Grouping - Page Numbering


Mark,

I have been working with Access since 1.0 and have never done or had a
request for something like you are wanting to do.  But, here is an outline
of what you are going to have to do.

You cannot use the Access pages numbers for any thing except to see if you
are on a different one.

You will have to collect the surrogate page number that you want to start
with.  I would suggest a form to gather all of this.

In the page footer, you will have to set a variable to the current page
number.

In the page header, you will have to check to see if it is different from
the one in the page footer variable.

If it is, then you should use the asc() function to increment and the
char() function to return the next surrogate page i.e. 5A.

If the grouping puts things on different real pages, then you would
increment the numeric portion of your surrogate page number in the group
header/footer the same way you did for the Alpha portion of the surrogate
page number.

I am going to take a leap here and say that it is my guess that you do not
know how to code in VBA.  So what you are going to have to do is find
someone on list willing to write it for you based on the description above.

Robert

At 12:00 PM 2/3/2004 -0600, you wrote:
>Date: Tue, 3 Feb 2004 08:22:10 -0500
>From: "Mitsules, Mark S. (Newport News)" <Mark.Mitsules at ngc.com>
>Subject: RE: [AccessD] A2K2 - Report Grouping - Page Numbering
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <D3E2A213B8F45B4DAC92EB5FD98ED1EF010EBA17 at nnse14.nns.com>
>Content-Type: text/plain
>
>Group,
>
>I realize that my question yesterday was rather long-winded, but I am 
>in search of someone who has experience in customizing report page 
>numbers. I'm not talking about just changing the starting page 
>number...I mean custom page numbering based on report grouping.  No one 
>has done this?  Can anyone point out an online reference?
>
>Mark
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com