News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Sunday, March 11, 2007

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: , ,


Anonymous Anonymous said...

When I do this I still get only 10 records, for the first group (and no records for subsequent groups).

2:15 PM  
Blogger Alex Dybenko said...

Oops, there is an error, instead of Report_Open should be {GroupHeaderName}_Format event, where {GroupHeaderName} is a name of group header

2:22 PM  
Anonymous Anonymous said...

Wow. I love it when I can get an exact, clear answer to the question I have. This worked beautifully, thanks!
J - New York, NY

7:58 PM  
Anonymous Anonymous said...

Thanks a lot this was very helpful. I use access for my work, and this has made my job a lot easier.


10:57 AM  
Blogger khrazhar said...

Hi Alex,

I make a report to create a card design. It is based on a query which show all the records in the table. However, when I generate a report that pointing to the query, access only display few card only and not all the records. Do you know how to solve this? Your help will be great for me.

Thanks a lot..


11:47 AM  
Blogger Alex Dybenko said...

Hi Adam,
check report's Filter property, perhaps you have filter there...

6:25 PM  
Blogger khrazhar said...

Hi Alex,

Thanks for your reply. I've checked the filter, and I've found the mistake. It's my bad because I haven't sort the results. So the result don't display the last design at the bottom. It's work now. However thanks a lot for helping. Wish you have a nice day!


4:06 AM  
Anonymous Anonymous said...

i have a problem that is driving me crazy,im using access 2007 and i tried every possible way to break my report into pages but none of them worked, i tried your way but also still showing all the records in the dataset, am i missing something !!!

11:10 AM  
Blogger Alex Dybenko said...

Perhaps you need to use grouping? also you have put a hidden page break and make it visible for certain record number to make a page break

2:26 PM  

Post a Comment

<< Home