Version 1.1                   First Published 27 July 2022                 Approx 0.6 MB (zipped)


The idea for this article was based in a request by AccessForums.net member Deepak Gupta in this forum thread from January 2018:
      How to Control the Number of Entries

Deepak asked for help in limiting the number of records allowed in a subform of a simple Billing database. Deepak wrote:

I have tblBill & tblBillDetails (Child Table). I am using frmBill & frmBillDetail(Sub form) for user to input data. Form frmBillDetail is showed in datasheet view.
I want to control that each entry of tblBill, can have maximum of 10 records for tblBillDetails.

As well as providing specific assistance for Deepak's database, I created an example app to demonstrate how this can be done with minimal code.

The app includes a form and subform based on related tables tblSale and tblSaleDetails with SaleID as the PK and FK fields respectively
The table relationship is an OUTER join so extra records can be added up to the record limit

Below record limit - Additional records can be added

At record limit - No more records allowed

NOTE: Click either image to view a larger version

The code limiting the records is in the Form_Current event of the subform

CODE:

Private Sub Form_Current()

      If Me.Dirty Then Me.Dirty = False

      If Nz(Me.SaleID,0) <> 0 Then
            Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) < Nz(DLookup("MaxRecords", "tblRecordLimit"), 0)
      End If

End Sub

The record limit value is stored in a table tblRecordLimit
In the example app, the record limit can be set on the main form - currently this is 5.
Alternatively just set your own fixed value (e.g. 10), remove the table & form controls and alter the above code to:

CODE:

Private Sub Form_Current()

      If Me.Dirty Then Me.Dirty = False

      If Nz(Me.SaleID,0) <> 0 Then
            Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) < 10
      End If

End Sub

NOTE:
Whichever approach is used, it is ESSENTIAL that end users are NOT allowed direct access to the tables otherwise they will still be able to add additional records



I have created a short video demonstrating how this works

The video is available on my YouTube channel at https://youtu.be/L1chhLzfNDI or you can click below:

       



Click to download:   Limit Records Example     (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 27 July 2022

Return to Example Databases Page

Return to Top