making group footer invisible in report is messing up the number of pages

[Treebeard]

Very strange problem I just ran into.

I have a report which is a basic "products sold" report for a specified
period of time.

There is ProductID, Price, Qty ; this is very simple.

Now the users wants a line for every product at every price the product was
sold at , and if it were sold at more than one price, a summation line for
that product, for example:

ProductID PRICE QTY
102 $2.00 4
--------------------------
134 $3.00 6
------------------------
185 $2.50 3
185 $2.75 4
-----
7
---------------------
198 $4.87 6
----------------------
234 $5.34 2
----------------------
345 $9.34 7
----------------------
234 $5.65 34
----------------------
etc
etc



Ok, I thought I had it and everything looked good, except , all of a sudden,
the "Number of Pages " is off!
The report is 16 pages, and on the first page it says "Page 1 of 13" ; on
the 2nd page "Page 2 of 13" and so on, until it say "Page 13 of 13 ", then
"14 of 13" then "15 of 13" and finally on the last page "16 of 13".

I have the standard ="Page " & [Page] & " of " & [Pages]
in the page footer

I should explain how I'm using a summary line only for those products which
were sold at more than one price. Basically I'm making the GroupFooter
invisible if I see that there is only one price for a product.

In the report code I'm defining the following variables:

strProductID As String
bolMultiplePrices As Boolean

In the DETAIL_PRINT i have this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' check to see if this is the same
' as the last Product
'
If strProductID = Me.ProductID Then
bolMultiplePrices = True
Else
bolMultiplePrices = False
End If
strProductID = Me.ProductID

End Sub


***************

Then in the group footer :

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If bolMultiplePrices Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
End Sub
*************************

If I remove the GroupFooter code everything works fine and the page count is
correct, however every product prints out with a summary line , which puts
an extra 40% of pages onto the report. It looks something like this:


ProductID PRICE QTY
102 $2.00 4
-----
4
--------------------------
134 $3.00 6
-----
6
------------------------
185 $2.50 3
185 $2.75 4
-----
7
---------------------
198 $4.87 6
-----
6
----------------------
234 $5.34 2
-----
2
----------------------
345 $9.34 7
-----
7
----------------------
234 $5.65 34
-----
34
----------------------
etc
etc


Any suggestions? I hope I made myself clear.

Jack

 

[Marshall Barton]

It is unreliable to use VBA code in event procedures to try
to check values from one detail record to another. Because
of CanGrow, KeepTogether, etc, the records may not be
processed in sequential order.

The problem with the page number is that you're using the
print event to determine if the section should be visible or
not, but the Pages value was already calculated in the first
pass through the report's formatting.

Get rid of of your existing code and let's try a completely
different approach. Add a text box named txtDetailCount to
the detail section. Set its control soutce ecpression to =1
and RunningSum property to Over Group.

Now you can use that to determine if the total in the group
footer should be visible or not:

Me.GroupFooter0.Visible = (Me.txtDetailCount > 1)

It's not clear to me if you really want to make the entire
footer invisible or just the total text box. If it's just
the text box, then you can use the same logic to make the
text box invisible and use CanShrink to make the footer
section reclaim the space it used.
--
Marsh
MVP [MS Access]

Very strange problem I just ran into.

I have a report which is a basic "products sold" report for a specified
period of time.

There is ProductID, Price, Qty ; this is very simple.

Now the users wants a line for every product at every price the product was
sold at , and if it were sold at more than one price, a summation line for
that product, for example:

ProductID PRICE QTY
102 $2.00 4
--------------------------
134 $3.00 6
------------------------
185 $2.50 3
185 $2.75 4
-----
7
---------------------
198 $4.87 6
----------------------
234 $5.34 2
----------------------
345 $9.34 7
----------------------
234 $5.65 34
----------------------
etc
etc



Ok, I thought I had it and everything looked good, except , all of a sudden,
the "Number of Pages " is off!
The report is 16 pages, and on the first page it says "Page 1 of 13" ; on
the 2nd page "Page 2 of 13" and so on, until it say "Page 13 of 13 ", then
"14 of 13" then "15 of 13" and finally on the last page "16 of 13".

I have the standard ="Page " & [Page] & " of " & [Pages]
in the page footer

I should explain how I'm using a summary line only for those products which
were sold at more than one price. Basically I'm making the GroupFooter
invisible if I see that there is only one price for a product.

In the report code I'm defining the following variables:

strProductID As String
bolMultiplePrices As Boolean

In the DETAIL_PRINT i have this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' check to see if this is the same
' as the last Product
'
If strProductID = Me.ProductID Then
bolMultiplePrices = True
Else
bolMultiplePrices = False
End If
strProductID = Me.ProductID

End Sub


***************

Then in the group footer :

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If bolMultiplePrices Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
End Sub
*************************

If I remove the GroupFooter code everything works fine and the page count is
correct, however every product prints out with a summary line , which puts
an extra 40% of pages onto the report. It looks something like this:


ProductID PRICE QTY
102 $2.00 4
-----
4
--------------------------
134 $3.00 6
-----
6
------------------------
185 $2.50 3
185 $2.75 4
-----
7
---------------------
198 $4.87 6
-----
6
----------------------
234 $5.34 2
-----
2
----------------------
345 $9.34 7
-----
7
----------------------
234 $5.65 34
-----
34
----------------------
etc
etc


Any suggestions? I hope I made myself clear.

Jack