Link to home
Create AccountLog in
Avatar of rmk
rmk

asked on

Work Around for Incorrect Pages property

I have an Access 2003 report that uses movelayout, nextrecord, and printsection to dynamically control some complex reporting requirements in the appropriate format and print events. The report works fine except that the Pages property is almost always incorrect. I've tried adding a ReportFooter section with a text box with a controlsource of =[Pages] and another textbox called txtLastPageNbr with a controlsource of =[Page]. Then in the page footer I have a textbox with a controlsource of ="Page " & [Page] & " of " & [txtLastPageNbr]. Much to my surprise, Access was not smart enough to first calculate txtLastPageNbr in the report footer before printing each page footer. Each page footer simply prints Page x of x where x increases by 1 on every page. Does anyone have a work around for this Pages property problem?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<Much to my surprise, Access was not smart enough to first calculate txtLastPageNbr in the report footer before printing each page footer>>

  That's because the report footer is the last section to be formatted.

1.  Place a hidden control on the report and set it's controlsource to pages.

2.  Declare a variable at module level as lngPages

3.  Then in the page footer OnFormat event do:

      If FormatCount = 1 and Me.Pages = 0 then
         lngPages = lngPages + 1
     End If

4.  and also do:

    Me![txtLastPageNbr] = lngPages

  That should let you count the pages on your own.

  Refering to [Pages] forces two pass mode.  On the first pass (Me.Pages = 0), you count each page.  On the second pass (the one that actually prints), you will get the correct number of pages.

Jim.

 
Avatar of rmk
rmk

ASKER

That did notwork either.

So I tweaked your suggestion as follows, but still with no success:

Private mlngMyPages As Long

Public Property Get MyPages() As Long
    ' must be public so that it can be referred to
    ' in a textbox controlsource on the page footer
    MyPages = mlngMyPages
End Property

Private Property Let MyPages(ByVal vlngMyPages As Long)
    mlngMyPages = vlngMyPages
End Property

Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
    If Me.HasData Then
        If Me.Pages = 0 And FormatCount = 1 Then
            If Me.Page = 1 Then
                MyPages = 1
            Else
                MyPages = MyPages + 1
            End If
        End If
        Debug.Print Me.Page, Me.Pages, MyPages, FormatCount
    End If
End Sub

The report actually has 10 pages, but Access runs into problems at page 8, as can be shown by the output of the debug.print statement when the report is opened in preview mode
 1             0             1             1
 2             0             2             1
 3             0             3             1
 4             0             4             1
 5             0             5             1
 6             0             6             1
 7             0             7             1
 8             0             8             1
 1             8             8             1
Then when I click on the goto last page navigation button, the debug.print statements adds the following output:
 2             8             8             1
 3             8             8             1
 4             8             8             1
 5             8             8             1
 6             8             8             1
 7             8             8             1
 8             8             8             1
 9             8             8             1
 10            8             8             1



  I'm left wondering why we don't see 10 pages on the first pass of two pass mode (Me.Pages = 0).

  Is any of the formatting being done in the OnPrint event?   OnPrint is not fired on the first pass of two pass mode.  Anything that manipulates the layout must be in the OnFormat events.

  Nice work on the debugging BTW!

Jim.

Avatar of rmk

ASKER

This Pages problem is documented in lots of places on the web, but I hav never seen a workaround.

In my experience, NextRecord and PrintSection typically have to go in the print event because they need to refer to PrintCount. Am I wrong?

In my case I have:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
...    
    Dim lngCnt          As Long
...  
    If Me.HasData Then
        If Me.chkShowEmplAddrFlg.Value Then
            lngCnt = 6
        Else
            lngCnt = 4
        End If
        '
        If PrintCount = 1 Then
            ' set some textbox values here
        Else
           ' clear some textbox values here so we don't show any values
        End If
        '
        If Me.txtDetailNbr.Value = Me.txtDetailCnt.Value _
        And Me.txtDetailCnt.Value < lngCnt Then
            If PrintCount < lngCnt - Me.txtDetailCnt.Value Then
                Me.NextRecord = False
            End If
        End If
    End If
...
End Sub

The format event of the group header immediately above the detail section sets MoveLayout=false and it's print event prints several lines of data in the left most 3 inches. There is no detail format event. The print event of the detail section prints to the right of 3 inches and sets NextRecord=false if enough detail items have not been printed for the fixed height of the group header.

ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rmk

ASKER

You've lost me. How can I tell how many times to print a blank detail by just using the format event?
Avatar of rmk

ASKER

I moved all the code from the print event to the format event and tweaked the logic for PrintCount. I also tweaked the debug.print statement and Me.Pages never got past 8 as before.
Avatar of rmk

ASKER

oops - ignore my last note. There were only 8 pages because my logic of what to print where is not quite working, i.e. things are printing on top of each other. So still some debugging needed on my part.
Avatar of rmk

ASKER

I just can't see any way to control how may times to print a blank detail line from the detail format event.
One thing I found helpful when on a similar type project was to make sure I knew what record I was on. I found that the OnFormat event was called one extra time in certain circumstances. So, (thanks to Allen Browne here) I created a hidden running sum control in the detail section which I referred to when I wanted to know which record I was on when running code in the OnFormat event procedure. Here's a link to help: http://office.microsoft.com/en-us/access-help/calculate-a-running-sum-cumulative-total-HP005187388.aspx . Getting a handle on this might resolve some issues with the pages problem.

The following should give you a blank space in the detail section of the report without skipping a record (use the OnFormat event procedure):

Me.Movelayout = True
Me.PrintSection = False
Me.NextRecord = False

You can set a global or module variable to control how many times to run this code. Ex.

Module variable
dim mintSkipCount as integer

OnFormat event procedure
if mintSkipCount <= 3 then
    Me.Movelayout = True
    Me.PrintSection = False
    Me.NextRecord = False
    mintSkipCount = mintSkipCount + 1
End If

You can use other criteria, or other report event procedures to reset the mintSkipCount variable to 0.

 Any chance of posting a sample?  I'm sure we can figure this out.

Jim.
Avatar of rmk

ASKER

I;m out of the office today, and will hopefully post my resolution sometime tomorrow.
Avatar of rmk

ASKER

I've been doing this for a long time (since Access 1.0) and thought I knew just about everything about Access including the 2 pass report writer. However, you have really educated me on the proper way to use the Format and Print events.

Here are the code snippets for my final working solution:
...
Private mlngDetailMin As Long
Private mlngDetailNbr As Long
...
Private Sub hdrCheckNo_Format(Cancel As Integer, FormatCount As Integer)
...
    If Me.HasData Then
        Me.MoveLayout = False
        mlngDetailNbr = 0
        If Me.chkShowEmplAddrFlg.Value Then
            mlngDetailMin = 5
        Else
            mlngDetailMin = 3
            Me.txtEmplAddr1.Visible = False
            Me.txtEmplAddr2.Visible = False
            Me.txtSSNO4.Top = Me.txtEmplAddr1.Top
            Me.txtEmplClassificationName.Top = Me.txtEmplAddr1.Top
            Me.txtExemptions.Top = Me.txtEmplAddr2.Top
            Me.txtMinorityName.Top = Me.txtEmplAddr2.Top
        End If
        '
        Me.linhdrCheckNo1.Height = mlngDetailMin * Me.txtExtEmplName.Height
        Me.linhdrCheckNo2.Height = Me.linhdrCheckNo1.Height
        Me.linhdrCheckNo3.Height = Me.linhdrCheckNo1.Height
        Me.linhdrCheckNo4.Height = Me.linhdrCheckNo1.Height
    End If
...
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
...
   ' txtDetailCnt is in ftrCheckNo as =Count("*")
...
    If Me.HasData Then
        mlngDetailNbr = mlngDetailNbr + 1
        If mlngDetailMin > Me.txtDetailCnt.Value Then
            ' will have to fill in the extra lines
            If mlngDetailNbr > Me.txtDetailCnt.Value Then
                ' past the real data
                ' so print a blank line
                Me.PrintSection = False
            End If
            If mlngDetailNbr >= Me.txtDetailCnt.Value _
            And mlngDetailNbr < mlngDetailMin Then
                ' at the last real data
                ' so stay here until we are done
                Me.NextRecord = False
            End If
        End If
    End If
...
End Sub

  Great to see that you got it working!

Jim.