Put an invisible text box with the value set to 1 in your detail section. Set its property to running sum. From the OnPrint event, check for the value and accordingly force the new page: If Me.Controls("YourControlName") \ 30 = 0 then ForceSectionToNewPage ("NameOfYourReport", acGroupLevel1Header)
NB acGroupLevel1Header if your details are in group level 1
acGroupLevel2Header if your details are in group level 2
9 if your details are in group level 3
etc. See Access help about the Section property
Code:
Function ForceSectionToNewPage(StrReportName As String, iCurrentGroupHeader As Integer)
Dim sHeight As Single
Dim I As Integer
Dim PrtMipString As str_PRTMIP
Dim PM As type_PRTMIP
Dim R As Report
On Error GoTo Ooops
Set R = Reports(StrReportName)
PrtMipString.strRGB = R.PrtMip
LSet PM = PrtMipString
With R 'Summing reports and page sections heights
sHeight = PM.yTopMargin
For I = 1 To iCurrentGroupHeader
sHeight = sHeight + .Section(I * 2 + 1).Height
Next I
If (.top > (sHeight + 5)) And (.Page <> 1) Then
.MoveLayout = True
.PrintSection = False
.NextRecord = False
End If
End With
ExitForceSectionToNewPage:
StrReportName = ""
Set R = Nothing
Exit Function
Ooops:
DoCmd.Beep
DoCmd.Echo True
DoCmd.Hourglass False
MsgBox "Um erro aconteceu na função ForceSectionToNewPage: " & Error$
Resume ExitForceSectionToNewPage
End Function
I'd eagerly tried applying your code in my report, but when running the reprot a message appear as "Compile error User -defined type not defined" and when checking the code, the line
Dim PrtMipString As str_PRTMIP is highlighted in yellow.
Sorry, I forgot to provide the declarations to use along with the PrtMip property:
Code:
Type str_PRTMIP
strRGB As String * 28
End Type
Type type_PRTMIP
xLeftMargin As Long
yTopMargin As Long
xRightMargin As Long
yBotMargin As Long
fDataOnly As Long
xWidth As Long
yHeight As Long
fDefaultSize As Long
cxColumns As Long
yColumnSpacing As Long
xRowSpacing As Long
rItemLayout As Long
fFastPrint As Long
fDatasheet As Long
End Type
I had tried but there is 32 record appearing on 1st page,this is not I intend to achieve, I need to fix a maximum 30 record per page, in my case it should be 1st page 30 & 2nd page the remaining two record.