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:

'----- start of function code ----- Public Function LimitRecords( _ frm As Access.Form, _ Optional RecLimit As Integer = 1) ' Limit the number of records in the form passed as ' to no more than the number specified by . With frm.RecordsetClone If .RecordCount <> 0 Then .MoveLast frm.AllowAdditions = (.RecordCount < RecLimit) End With End Function '----- end of function code -----

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:

'----- start of code for limited form's module ----- Private Sub Form_Current() LimitRecords Me ' allow at most 1 record ' LimitRecords Me, 5 ' allow at most 5 records End Sub '----- end of code for limited form's module -----

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):

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.,

'----- start of code for parent form's module ----- Private Sub Form_Current() '*** Substitute your subform control name for "sfMySubform" *** LimitRecords Me.sfMySubform.Form ' allow at most 1 record ' LimitRecords Me.sfMySubform.Form, 5 ' allow at most 5 records End Sub '----- end of code for parent form's module -----

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.