CODEWITHSUNDEEP
ms-access
Jose
Jose

Reputation: 31

How to limit the amount of records in a subform in Microsoft Access

I created a database in Microsoft however my subforms show all the records. How can I limit my form to e.g. show 5 in each page.

Thanks, Mario

Upvotes: 1

Views: 8480

Answers (3)

David-W-Fenton
David-W-Fenton

Reputation: 23067

Assuming the subform's records are related to the parent form's records, you'd set the LinkMaster/LinkChild properties of the subform control to the expression that links the two.

If your subform is a datasheet or continuous form, you can then size the subform control to display five records. Scrollbars will appear when there are more records than can be displayed in the first five rows.

Upvotes: 0

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

You don't give much information, but here are a few ways:

  • One common way to limit the number of records is to make the user create a filter first and then click a "Search" button to return only a limited number of records.
    I posted an example of this recently.

  • If you use a datasheet or a continuous form, Access will only return enough records to display it on the page and a bit more. It will not load all records if it doesn't need to.

  • Having said that, if your query is complex or using custom functions or your table fields are not properly indexed, Access will need to run it first, and to do that, it may need to load all records, which may be slow. In that case, you can try first to see if you can optimise your table indexes and query to make it run faster.

  • If all fails, you try to implement paging: see question Microsoft Access and paging large datasets for an example.

Upvotes: 1

Raj More
Raj More

Reputation: 48016

Instead of setting the RecordSource property to the table, set it to use a query and in the query do SELECT TOP 5 col1, col2, col3 from table.

Now you will have to ReQuery the subform every time your base form record changes.

Upvotes: 0

Related Questions