Limit number of 1to many records

[zrobinso]

I have a form and subform. The subform is to a many relationship table. How can I limit the input on the subform to only 3 records.

Subform is a continuous linked form.

 

[lhite]

Instead of basing the subform on the table, create a query of the table. In the query design view go to the query's properties and find the TOP VALUES property. If you click its combo box it will show 5, 10, 25, 100, 5%, 25%, and ALL. You can type your own number there instead, so type a 3. As the name implies, it will show the Top 3 values as they appear in the query, so change the sort order as needed to get the most recent dates, the largest dollar amount, or whatever. Then set the criteria for the field that links the main and subforms together to
[forms].[frmMainFormName].[LinkingField].
Finally, place the subform on the main form and test it out.

 

[JeremyNYC]

lhite,

This will limit the output to three items, not the input--that is, you'll be able to add as many records as you want and your form will only show the top three. I think.

Z,

The quick way to do this is with some code on the Before Insert event of the subform. You'd just have to make a recordset that matches the data on the subform and reject the addition if there are already three records.

Slicker than this would be to use code in both the after update of the subform and the on current of the main form. This code would do the same thing, except instead of rejecting an insert of a record the user has already created, it would set the subform's allowadditions to false.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[zrobinso]

Thanks, I did the first option for now. It works good. I will research the other option. Thanks