Select Top n records

[tamus121]

Hi, hope someone can help.
I have a access report which has a query as the record source. This selects the top 6 records from a table using "SELECT TOP 6". I would like to have a text box on a form for the user to enter a variable amount of records to be listed. I can't get this to work if I change the "6" to the text box name. Is this even possible?
tamus

 

[dhookom]

This will not work as you want it to. You can use some DAO code to update the SQL property of a saved query.

   Currentdb.QUeryDefs("YourQueryName").SQL = "SELECT TOP " _
    & Me.txtTop & " * FROM qselYourQUery ORDER BY 1,2"

Duane
Hook'D on Access
MS Access MVP

 

[tamus121]

Thanks Duane for your reply
Am I correct in thinking that I would need to create a new query and make it the record source of my report. Then use the code to update the new query before I preview the report?
tamus

 

[dhookom]

Yes. I would work from a saved query and use code to update the SQL property. This would need to occur prior to the report opening.

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

Supposing you have Access 2003, you could build the SQL in the calling form, then pass it to the OpenArgs property of the report:

Private Sub CmdButton_Click()
Dim strSQL As String
strSQL="Select Top " & me.txtTop & " From YourQueryName"
DoCmd.OpenReport "ReportName",,,,,strSQL
End Sub


Then set the recordsource in the Open event:

Private Report_Open(Cancel As Integer)
Me.Recordsource=Me.OpenArgs
End Sub


Daniel Vlas
Systems Consultant

 

[tamus121]

Thanks for your replies

I have used the OpenArgs property as suggested by danvlas and it works although I had to place a * before FROM to get it to work. Not sure what it does!
tamus

 

[danvlas]

I forgot to type it

It returns ALL fields from the source. My advice is to avoid using it and replace it with the exact list of the fields you really need.

So... instead of
Select Top 100 * From TableOrQuery

I would use
Select Top 100 [Field1Name], [Field2Name]...,[FieldXName]
From TableOrQuery





Daniel Vlas
Systems Consultant