Mitsules, Mark S. (Newport News)
Mark.Mitsules at ngc.com
Wed Feb 4 08:17:08 CST 2004
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