Select Top Values from a form

[Rene1024]

Hi,

I have a query that outputs the specified top values.
I would like to change the quantity of records returned from a form.

For example if a text box in the forms says 10 the top ten values would be returned if I change the value of the text box to 25 then the top 25 values would be returned.

How can I accomplish this?

Thanks for your help.


Rgds.

Rene

 

[CautionMP]

Use the after update event on the text box to change the record source for the form.

Sub text1_afterupdate()
Dim sttrSQL as String
strSQL = "SELECT TOP " & text1.value & " * FROM Table;"
Me.RecordSource = strSQL
Me.Requery
End Sub

 

[Rene1024]

Thanks for the quick reply CautionMP.

I screwed up and didn't expalined myself correctly.

On the query properties there's a Top Values property, which can be set to All, 5, 25, 100 etc... or a user specified value.
I would like to change that value without having to go into the query design, instead I would like to make changes to that value from the form.

Is that possible?

Thanks again for your help.

Happy New Year.

Rene

 

[CautionMP]

There is no way to specify the TOP Value property for a form.
The code above will change the RecordSource for the form to satisfy your requirement, this code will not change the underlying query that the form is bound to;
i.e. the form is bound to a query that returns the top 10 values, a user then changes the field on the form to return the top 25 values. The next user to open the form would see the top 10 values becasue that is the default on the bound query.
The trick to using this type of on the fly SQL creation for a form is to make sure that when the form is closed that it is not saved, that way the RecordSource property is not permanently altered.

 

[Rene1024]

Ok, Thanks.

Have a great year.

Rene