Limit the Number of Entries on a Continuous Form

Sometimes business rules mandate that there be no more than some fixed maximum number of records in a table, or related to a particular parent record. The following function can be called from the form (or subform's) Current event to limit the number of records to a specified maximum:

code: click in the frame, Select All, then Paste into your code editor

The function can be placed in a standard module, or in the class module of the form in question if you only want to use it in that form.

You must call the function from the Current event of the form whose records are to be limited. You could have an event procedure like this:

code: click in the frame, Select All, then Paste into your code editor

Or, if you are doing nothing else in the form's Current event, you can call the function directly from the form's OnCurrent property, using a function expression like this (to allow only 1 record):

=LimitRecords([Form], 1)

or this (to allow up to 5 records):

=LimitRecords([Form], 5)

If you're using the form as a subform on another form, you must also call the LimitRecords function in the parent form's Current event; e.g.,

code: click in the frame, Select All, then Paste into your code editor

Again, if you are doing nothing else in the form's Current event, you can call the function directly from the form's OnCurrent property, using a function expression like this:

=LimitRecords([sfMySubform].[Form], 1)

Of course, you must replace "sfMySubform" with the name of your subform control -- the control on the parent form that is displaying the subform.