Restrict Input Rows

[nice_beaver]

Is there a way of restricting the number of rows displayed on a form/subform. I have a table of many entries (unlike a coat of many colours) but the owner wants to restrict the number of rows on view to the latest 3.

Any ideas......please, and thankyou.

 

[MTmace]

The data on the form or subform is being generated by a query. If you used the subform or form wizard to create the form then Access created that query.

You can view it by opening the form properites window (right click on the little box at the top left of the form, then select 'properties' at the bottom of the dialog box)

In the form properies window select the 'record source' field. At the right you will see a box with three dots, click this box and it will show you the query that generates this form.

At the top of this query you will see a drop down box that has the work 'All' in it, this means that all records generated by the query will be displayed. You can use the are to select select from the values in the dropdown box "5, 25, 100, etc".

Or in the SQL view you can add 'TOP XXX' after the word 'Select'. Where 'XXX' equals the number of records you would like to select.

You mentioned that you would like the three most recent "colours", this would be depend on the date the records were entered and you would have to sort the query by the date in desending order.

Hope you can understand all this, I am having the same kind of problem but I would like user to enter how many records should be displayed.

MTmace

 

[nice_beaver]

Many thanks for the prompt answer, but I was being a little funny when I mention the coat of colours(please re-read, as in Jacob! (sorry, my twisted mind at work...))

Back to the original qestion. I need to restrict the number of rows visible to just 3, as opposed to upto 35. I tried your suggestion, but the query is blank.

Once more I would appreciate any support on how I can restrict how many rows are displayed in the form.

Many thanks.

 

[Rich]

Providing there is data in the underlying query you can use the Top method as suggested.

 

[nice_beaver]

OK, this is all very helpful, but when I select the suggested option I get the message requesting whether I want to create a query based on the table. I do not think this is not the option I require, as it will end up having to create a query, or is that what is really being suggested? Sorry for being a little slow on the uptake on this.

Thankyou all for your help.

 

[David R]

You can build a query and specify SELECT TOP 3... and then base your form upon that, or you can click the [...] button at the end of the data source and build a SQL statement there. I honestly don't know if there's an efficiency difference.