"TOP 5" report from Hell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sorry if you thought this was gags about the top5 worst reports.

I need to develop a report that conatins only the 'top 5' entities by an
expentiture factor. My problem is that I cannot do this is the underlying
query. The way in which this app is built requires that the report have a
generic record source and be opened by passing a where condition in the
docmd.open report call. This is due to the design of the reporting interface.
The client manages multiple contracts from this app and all reports must be
available to all contracts. Users pick the report they want from a list which
controls the list of available filter fields. The user nominates the criteria
then hits print/preview/etc and the report is opened with the programatically
assembled where clause passed in. There is no option to modify this structure
as it would require the redesign of over 100 reports

Is their anyway I can tell the report (rather than the underlying dataset)
to only display a designated number or records??


Cheers,
Mr. Smith
 
You can show the top 5 by overall report or by group fairly easily. Add a
text box to the detail section of your report"
Name: txtCount
Control Source: =1
Running Sum: Over all (or) Over Group
Visible: No

Then add code to your report like:

Option Compare Database
Option Explicit
Dim intLimitTo As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount > intLimitTo
End Sub

Private Sub Report_Open(Cancel As Integer)
intLimitTo = InputBox("Enter the number of products per category",
"Enter Number", 5)
End Sub
 
Thanks Duane

I've been developing in Access since V2 and never realeased that Cancel
could be used to control record flow in that fashion. Never even considered
it.

Thanks very much, that worked perfectly

Mr. Smith,
 
Back
Top