Auto-truncate subreport

[mattlau]

Is there a simple way to get subreports to auto-truncate? I have a subreport that has a list of records by date. I want to set the subreport to only display the last X amount of records. In other words, I only want the subreport to display the last 5 rows, if sorted according to date (ascending, or the first 5 rows if using a descending sort).

Any help would be most appreciated! I am using Access 2003.

 

[mattlau]

Here is one idea:

1) Set the vertical size of the subreport to what would display about 5 records.
2) Set the sort on date to descending.
3) In the primary report, set the subreport value for "Can grow" to "No."

Is there a better way to do this? I feel that the above may work, but it isn't the most Access-savvy approach.

 

[dhookom]

Did you try your solution? I expect it should work.

You could also add a text box to the subreport detail section:

Name: txtRunSum
Control Source: =1
Running Sum: Over All
Visible: No

Then add code to the On Format event of the subreport detail section like:

   Cancel = Me.txtRunSum > 5

Duane
Hook'D on Access
MS Access MVP

 

[mattlau]

I will try that, Duane. Your approach seems more reliable/stable (it doesn't rely on visual formatting).

 

[susieqtotallynew]

Oh, goodness...I have yet another question. It's making me feel SO stupid...

I have a query that lists the last x transactions for a participant. In some cases, this may be 1 transaction. In others, 50 transactions, and in still others, 0 transactions.

In my access report, I am using a subreport to list the last 10 transactions. Obviously, in some cases, the query contains fewer than 10 transactions, and in some cases, it contains more than 10.

Is there a simple way to limit the number of transactions printed in the report to 10?

Thank you so much...

 

[dhookom]

Did you try either of the suggestions replacing "5" with "10"?

Duane
Hook'D on Access
MS Access MVP