Limit Number of Record Entries

Every once in a while you may want to restrict how many records can be entered. Maybe because of space limitations on a Report or perhaps you want to limit CD rentals to 4 per Customer or book a specific amount people per room. Whatever the reason here’s some code to make it happen… 

What I used to use (and it worked)…

Private Sub Form_BeforeInsert(Cancel As Integer)

   If DCount("ICS", "qryIncidentCrimes") = 4 Then
       MsgBox "Only 3 Charges allowed per report, if you have additional charges you must start a new report!", vbExclamation
       Me!cboICS.Undo
       DoCmd.RunCommand acCmdUndo
       DoCmd.GoToRecord , , acPrevious
       Forms![frmIncidentInformation]![txtTimeOfOccurrence].SetFocus 'Here I set focus back to the Main Form
   End If 

End Sub

But then I found a more efficient way… 

In a Subform
Private Sub Form_BeforeInsert(Cancel As Integer) 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox "Only 4 sizes allowed per sheet!", vbExclamation + vbOKOnly, "FF&E Size"
       DoCmd.GoToRecord , , acPrevious
   End If

End Sub 

In a Main Form 
Private Sub Form_Current() 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox "Only 4 sizes allowed per sheet!", vbExclamation + vbOKOnly, "FF&E Size"
       DoCmd.GoToRecord , , acPrevious
   End If 

End Sub