How to reformat and change report detail and and group footer section

[noorani]

i have applied the following code to solve my task but i didn't get the results. my problem is that i'm trying to print information of Group footer right after the Detail section but in one condition that if the group footer size is more then the size left in page right after the Detail section then the Group footer should be printed to next page along with the last record from the detail section.

here is the module code i have used.

Option Explicit
Dim RecCntr as Long

=============================================
Step 1

' Counter to check the Products record

Function ResetCounter()
RecCntr = 0
End Function

=============================================
Step 2 ***

'Detail Location Function it suppose to move the last Detail section record if the footer section needs to print in next page.

Function SetDetailLoc(Rpt as Report, GrpFtrLoc as Double)
RecCntr = RecCntr + 1
If Rpt.top < GrpFtrLoc * 1440 Then
If reccntr = Rpt![Number Of Products] Then
'This is the last real record of the detail section.
Rpt.nextrecord = False
ElseIf reccntr > Rpt![Number Of Products] Then
'after last detail but not at location yet so move
Rpt.movelayout = True 'Move to next print location
Rpt.nextrecord = False 'Do not go to next record
Rpt.printsection = False 'Don't print the section
End If
ElseIf Rpt.top >= GrpFtrLoc * 1440 Then
'Last record before the group footer section prints.
Rpt.printsection = False
End If
End Function

=============================================
Step 3

'after that in group header i call the fuction in OnFormat Event.

OnFormat: =ResetCounter()
=============================================
Step 4

'Group footer Format property look like this.

ForceNewPage: After Section

=============================================
Step 5

'then OnFormat Event of Detail section look like this.

OnFormat: =SetDetailLoc([Report],7)

=============================================
Step 6

'Then i create the text box in Group Footer section to count the product records which i
use in Step 2 function.

Text Box Name: Number of Products
ControlSource: =Count([Product ID])

=============================================

Please advise me what i'm doing wrong. thanks in advance.

Zeeshan.