MS Access – Limit the Number of Records Input in a Form

Have you ever wanted to limit the number of records that could be input into a table through a form? Simply input the following Form Current Event!

Private Sub Form_Current()
    Dim intMaxNumRecs as Integer

    intMaxNumRecs = 5 'Max Number of Records to Allow

    If Me.NewRecord Then
        With Me.RecordsetClone
            If .RecordCount > 0 Then
                .MoveLast:  .MoveFirst
                If .RecordCount >=  intMaxNumRecs Then
                    MsgBox "Can't add more than " &  intMaxNumRecs & " records in the demo database!"
                    .MoveLast
                    Me.Bookmark = .Bookmark
                End If
            End If
        End With
    End If
End Sub

13 responses on “MS Access – Limit the Number of Records Input in a Form”

    1. Daniel Pineault Post author

      Then you will need to perform a DLookup() or reference the Form value in some manner instead of a hardcoded value. So you might ended up doing something like:

      intMaxNumRecs = DLookUp(…)

      or

      intMaxNumRecs = Forms![YourFormName].Form.YourControlName

  3. Mark

    Hello, I am interested is having a MS Access DB that we can have students record their sport selections for school on a term by term basis. Students submit their 3 preferences and the sports organiser should be able to place limits that each sport can accommodate, such as Tennis = 20, Futsal = 30 etc. Can you suggest how this can be done given students would record their first 3 choices (preferences) and Access can manage that and produce lists for each sport offered based on the limits put in place for the sport numbers.

  4. Raghubir Singh

    This is great help for me. Thanku very much Mr. Daniel for your great knowledge.

    Thanks
    Raghu

  5. Jeff

    Another method I like to use for referencing data in other areas of you database is to use the TempVars method
    eg TempVars(“variable name”).

  6. Martin

    A form creates a reference code for 5 records using the above code but after 5 I want to reset the count and restart again for another 5 records with a new reference code.

    How can I reset the count of 5 back to 1 and re-start the process?

    Cheers.

    1. Daniel Pineault Post author

      This function is to limit the total number of records in a form, not to create ‘batches’. My first thought for that I’d be looking at using the before update/insert/current events to check the DCount() for the current reference number.

  7. Tanveer

    This is great information!
    I need lock record by date. Suppose i want to give appointment 4 people a day for interview. There is need in form to lock that should not more than 4 people for a day. Is this possible?

    1. Daniel Pineault Post author

      It depends on how your form is setup. If your form is setup by day, then you need only set intMaxNumRecs = 4.

      Otherwise, I think you are looking writing some custom code to perform a DCount() based on the Date you are entering in the record to see if you already have 4 entries or not.

  8. Simon Kanalnga

    This code works to avoid addition of records only after trying to punch in the 6th record. However, the copy paste action does not restrict to only 5 records, more than 5 records are still pasted on form in datasheet view. Please assist on this issue.