Forcing a new page in a report created with VB

[CMPS]

Hi Experts,

I am trying to create a report using code and have it begin a new page after printing out 15 records. It appears the system can not move to a new page unless the size of the section or group is larger than the page. When this happens it does not format properly. Is there a way to force the reords into groups of 15 to then using the force new page command?

Has anyone ever done anything like this or know how this can be done?

Regards,
Callum

Option Compare Database

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

MonthlyShipmentsSQL = "SELECT * from SalesOrderLines"

MonthlyInkTotalSQL = "select * from MonthlyInkTotals where companyid = 'BER05' and ProcessEndDate = #1/15/05#"

Set curDatabase = Application.CurrentProject.connection
Dim MonthlyInkTotalRS As New ADODB.Recordset
Dim MonthlyShipmentsRS As New ADODB.Recordset
Dim OrderLinesRS As New ADODB.Recordset
MonthlyInkTotalRS.Open MonthlyInkTotalSQL, curDatabase, adOpenStatic
Me.GroupHeader0.Height = 4500

MsgBox MonthlyInkTotalRS.RecordCount
For J = 1 To MonthlyInkTotalRS.RecordCount
If J > 1 Then
Me.CurrentY = Me.CurrentY + 560
End If
Me.CurrentX = 5
Print MonthlyInkTotalRS!InkCode
CurrentY = CurrentY - 180
CurrentX = 1350
Print "DESCRIP"
CurrentY = CurrentY - 180
CurrentX = 2300
Print MonthlyInkTotalRS!PreviousTotal
OrderLinesSQL = "SELECT SalesOrderLines.SalesOrderLineNum, SalesOrderLines.SalesOrderID, SalesOrderLines.InkCode, SalesOrderLines.Amount, SalesOrderLines.Price, SalesOrders.CompanyID, SalesOrders.SalesOrderDate" & _
" FROM SalesOrderLines INNER JOIN SalesOrders ON SalesOrderLines.SalesOrderID = SalesOrders.SalesOrderID " & _
" WHERE (((SalesOrders.CompanyID)='ber05') AND ((SalesOrders.SalesOrderDate) Between #12/15/2004# And #02/15/2005#)) and SalesOrderLines.inkcode = '" & MonthlyInkTotalRS!InkCode & "'"
OrderLinesRS.Open OrderLinesSQL, curDatabase, adOpenStatic
If OrderLinesRS.RecordCount > 0 Then
Me.CurrentX = 3000
X = 3000
Y = Me.CurrentY - 180
For I = 1 To OrderLinesRS.RecordCount
Z = X + ((I - 1) * 800)
CurrentY = Y
Me.CurrentX = Z
Print OrderLinesRS!Amount
Me.CurrentX = Z
Print OrderLinesRS!SalesOrderID
Me.CurrentX = Z
Print OrderLinesRS!SalesOrderDate
OrderLinesRS.MoveNext
CurrentY = Y
rowcount = rowcount + 1
Next
End If
OrderLinesRS.Close
CurrentY = Y
CurrentX = 6250
Print MonthlyInkTotalRS!ShipedTotal & MonthlyInkTotalRS!PreviousTotal
CurrentY = Y
CurrentX = 7600
Print MonthlyInkTotalRS!AmountRemaining
MonthlyInkTotalRS.MoveNext
If rowcount >= 10 Then
' MainReport.ForceNewPage
' Me.GroupHeader0.ForceNewPage
End If
Next
End Sub

Private Sub MainReport_Format(Cancel As Integer, FormatCount As Integer)
End Sub

Private Sub Report_Open(Cancel As Integer)
End Sub

 

[PaulBricker]

First, you would have to Force the New Page from the Detail Section of the Report. Maybe put a pagebreak in the detail section (set to false), and then in your conditional statement in the Header, set the value of the pagebreak to True if RecordCount >= 10
That might get you closer.

Paul

 

[CMPS]

Thanks for the response Paul.

That makes sense but I am not clear on how to put a pagebreak in the detail section.

I have tried this,

PageBreak = True

Is that correct?

Thanks again

Callum

 

[PaulBricker]

Open the report in design view. You should have a toolbox available and from that toolbox, you should be able to add a pagebreak to your report. Then open the properties box for the pagebreak, and set the value to False.
The value of the Record Count may have to be determined in the Format Event for the Detail Section. I don't know if you can do it from the Header section, but you can try it both ways. The real question is whether you will get an accurate RecordCount from either section. When I have forced a pagebreak, I have used a hidden textbox in the detail section. I'd name the textbox RecCounter. I set the Control Source to =1, and the Running Sum property to Over Group (or you can set it to Over All if you don't have groups). Then my code would go in the Format Event for the Detail Section and it would say

If Me.RecCounter Mod 10 = 0 Then
Me.PageBreak0 = True
Else
Me.PageBreak0 = False
End If

How this all plays out for you, I'm not sure but do what yo can and post back with specific problems.

Paul