Limit report to only show first page

[LiMa]

Hi there, please can anyone tell me, if it is possible, how to limit my access report to to only show the first page.

Many thanks.

 

[vbaInet]

Limit the number of records by altering the Record Source of the report. I'm guessing your report is based on a query or an SQL statement so it would read:

SELECT [COLOR=Red]TOP 20[/COLOR] ... etc

where TOP 20 will return a total of 20 records and nothing more. You can alter the number as you see fit.

 

[LiMa]

Hi, I can't limit the number of records that come from the query because there are calculations on the report based on the whole of the query. i.e. count of records based on certain criteria. I had assumed - perhaps wrongly - there may be some code that will prevent other pages from displaying

 

[vbaInet]

Why can't you limit the records in the query? I have just told you what to do to limit it and what you explained doesn't clarify this./

 

[LiMa]

Sorry if I didn't explain clearly, but, as stated in my previous reply, there are calculations on the report based on the details in the query. So if I limit the query to the top 10 records it will only use those for the calculations on the report e.g. I have a field on a report that counts the number of records that have a certain status, the result of the count is 4677, however, if I limit it to the top 10 results it will only count a maximum of 10 records. I hope this clarifies what I meant.

 

[vbaInet]

So you can put a subreport in the footer to perform and display the calculations and limit the records of the main report.

Or

Drop a textbox in the Detail section and get it to perform a Running Sum Over All > write code in the Format event of the Detail section to hide the section if the value in the textbox is greater than 20, else make the section visible.

Or

Limit the records in the report and use DCount and/or DSum to perform the calculations.

 

[LiMa]

Well, putting a subreport and not allowing it to grow almost works, the only problem is I use SQL to open the report based on selections on my reporting form. I use:
DoCmd.OpenReport strReport, acViewPreview, , strSQL
How can I get it to apply the SQL to the subreport? If I can get it to use the SQL for the sub report, that will be a brilliant solution. Thanks.

 

[vbaInet]

I would have gone for option 2 to be honest.

Use the subreport's Filter and FilterOn properties in code.

 

[LiMa]

Sorry if I sound really stupid, but, I am very new to all this and really appreciate your help. I don't know how to set the filter on the sub report to the SQL from my button click code. Basically the button just sets the SQL string, and then displays the report, but, I don't know how to get the code to also set the Filter of the subform to the SQL?

 

[vbaInet]

So after opening the report, one way to filter the subform is to do this:

<-- Open report code line -->
Me.[COLOR=Red]SubformControlName[/COLOR].Form.Filter = strSQL
Me.[COLOR=Red]SubformControlName[/COLOR].Form.FilterOn = True

amend the red bits.

 

[LiMa]

So on my form where I click to open the report - all the form does is allow me to select which report I want to open and select criteria - I add the following in the preview button routine:

Reports![rptStatusDetailed]![rptStatusDetailed_Sub].Report.Filter = strSQL
Reports![rptStatusDetailed]![rptStatusDetailed_Sub].Report.Filter = True

I receive the error message below on the first line.
Runtime error 2101
The setting you entered isn't valid for this property

 

[vbaInet]

Good catch for changing .Form to .Report

The second line should be FilterOn instead of Filter.

 

[LiMa]

That is a typo I made as I typed on here, in the code it was correct lol I am learning to type so try to type as much as possible instead of copy/pasting

 

[vbaInet]

Good for you

Ok, what does strSQL contain?

 

[LiMa]

The strSQL contains (from the immediate window):

[inID] = "D4 Rec"

But, this can contain loads of different things, as the criteria builder is used for lots of different reports. I am trying to get this to run with the simplest criteria first.

 

[vbaInet]

Upload a stripped down version and I'll have a look.