Limit number of records on a report 4

[SamEastman]

How can I limit the number of records on a report? I want only 10 records per page per group? My group could have 40 records and I only want 10 per report page.

 

[scriverb]

Yes, this can be done with a little VBA code in your report.

1. In the reports Design mode click the code button at the top. You will see the two lines below in black. Add the blue line.
Option Compare Database
Option Explicit
Dim vCounter As Integer
2. Close the code window. Right click the Report Header bar and select properties. In the On Format property select Event Procedure and click the button(...) at the right. You will see the black lines below. Add the blue line.
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
vCounter = 1
End Sub
4. Close the code window. Right click the Detail Section bar and select properties. In the On Format property select Event Procedure and click the button(...) at the right. You will see the black lines below. Add the blue line.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If vCounter = 10 Then
Reports![ReportName].Section(acDetail).ForceNewPage = 1
vCounter = 0
Else
Reports![ReportName].Section(acDetail).ForceNewPage = 0
End If
vCounter = vCounter + 1
End Sub

Insert the name of your report for the RED code above. This will give you 10 detail lines per page. Bob Scriver

 

[InfoNow]

Hi Bob,

This works great! However, I would like to have only one page printed, the first page. The rest of the records I do not want. How would like go about doing that?

Thanks

 

[PaulBricker]

You can use the DoCmd.PrintOut method to print just the first page.

Paul

 

[scriverb]

Hello Paul, if there are more than one grouping will this print the first page of each grouping? I think he wants to have the first 10 records of each grouping printed even if there are 40 for one group and 22 for another group.

I was thinking of using a Union query to perform a Top 10 select by the groups and combine them for printing as the RecordSource.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[PaulBricker]

What about this. Set the Force New Page property for the Header to Before Section. Then in the Detail section you can add a counter textbox. Set the control source to =1, the visible property to No and the Running sum to Over Group. Then in the Format event for the Detail section use

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text74 > 10 Then 'this is the counter textbox
Me.Section(acDetail).Visible = False
Else
Me.Section(acDetail).Visible = True
End If
End Sub

This will return the Group with ten records. The Force New Page puts each group on a new page. Then you just have to use the Sorting and Grouping to set the field Ascending or Decending to get your top 10.

Paul

Paul

 

[scriverb]

That should do it. Great solution.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[PaulBricker]

Thanks Bob. It should work. Now if I had only read the question to begin with. Must be the anticipation of the long weekend. Happy Thanksgiving.

Paul

 

[daudi]

How would this work on subreports?
I would like to print a given number of records on a subreport and then force a new page on the master report with the rest of records. In other words, each master report should contain a subreport with a given number of records. I'd also like to include sub-totals for each of the subreports.

 

[PaulBricker]

To do this you would probably have to add a textbox to the detail section of your Subreport. Set the control source of the textbox (I'll call this textbox TEXT0) to =1, the Running Sum property to Over All and the Visible property to No. Then add a Pagebreak to your Main Report (I'll call PageBreak1)and set the Visible property to No. Then in the Format Event for the Detail section in your Subreport, put this code.

I TEXT0 Mod 15 = 0 Then
Reports!MainReport.PageBreak1.Visible = True
Else
Reports!MainReport.PageBreak1.Visible = False
End If

I haven't tested this out but it may work.

Paul

 

[daudi]

Thanks Paul,
Wehere to put the pagebreak.

 

[PaulBricker]

After your subreport.

Paul

 

[PaulBricker]

daudi, I had a chance to try this out. You will need to adjust my suggestions some. If the SubReport is in the Detail section of the Main report then all you have to do is add the textbox that increments to the Detail section of your MAIN report and NOT the Subreport. Add the PageBreak after the subreport and then in the Format event for the Detail section in the MAIN report use

If TEXT0 Mod 10 = 0 Then
Me.PageBreak1.Visible = True
Else
Me.PageBreak1.Visible = False
End If


Paul

 

[dhookom]

daudi,
I would probably use two separate subreports. The first would select the TOP N records from the report's record source. The second subreport would select the remaining records using a subquery in the record source like:
IDField NOT IN (SELECT TOP N FieldID FROM tblSameTable ORDER BY SomeField)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.