How to limit number of records showed in report

You need to build a report with grouping, say Customer and it contacts, but show only 10 first contacts or less. You can’t use top 10 in a query – it will show only 10 records at all. So how you can do this? The trick is to use Detail section Format event, which runs for each contact record and count number of records there, once you reach the limit – cancel event. Here the code in report’s class module:

'Variable to count number of lines
Private mlngLines As Long

Private Sub Detail_Format(Cancel As Integer, _
    FormatCount As Integer)
    If FormatCount = 1 Then
        mlngLines = mlngLines + 1
    End If
    If mlngLines > 11 Then Cancel = True
End Sub

Private Sub {GroupHeaderName}_Format (Cancel As Integer)
'Reset it for each group
    mlngLines = 1
End Sub

There is more two way to do the same in KB article 153747

Technorati tags: access, reports, tricks