CODEWITHSUNDEEP
ms-accessvbams-access-2007
epilimic
epilimic

Reputation: 27

Access - Limit records per group in a report so they don't spill over to the next page

I'm having a slight issue with a report. I need to limit the number of records pulled for each page. I've tried a number of things but can't figure it out.

Basically, I have a report that pulls contact info and payment history for our customers. The report groups based on the phone number and sorts based on the most recent payment date. These contacts in the report are to be printed out on 3x5 index cards. The issue I'm having is that some people have multiple payments and it is spilling over onto a new card.

Here's an example of what is going on:

example

Any ideas on how I can limit it so that it ONLY pulls enough records to fill up the 3x5 card and then stop? I'm using access 2007.

Upvotes: 0

Views: 1022

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123664

One possible approach might be to add some VBA code to the report that will suppress (Cancel) the printing of the Detail band after n items. I just tried the following code in an Access 2010 report and it seems to work, but only when I view the report in "Print Preview". (The code apparently has no effect when viewing the report in "Report View".)

Option Compare Database
Option Explicit

Dim mySeq As Long

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    mySeq = 0
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If FormatCount = 1 Then
        mySeq = mySeq + 1
    End If
    Cancel = (mySeq > 3)  ' don't print more than 3 detail bands per group
End Sub

Upvotes: 2

Related Questions