Limit the number of new records a user can enter

[VWoody12]

Access 97: I have form that is has a sub-form embeded in it, I would like to limit the number of records that the user can enter into the sub-form to 5 entries. Is this possible? If so, how?
Thanks
Woody

[ADezii]

Access 97: I have form that is has a sub-form embeded in it, I would like to limit the number of records that the user can enter into the sub-form to 5 entries. Is this possible? If so, how?
Thanks
Woody

Yes it is. In the BeforeUpdate() Event of the Sub-Form, place the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String
 
Msg = "You are at the Maximum Allowable Number of Records in this Sub-Form. The "
Msg = Msg & "Save Operation will be cancelled!"
 
If DCount("*", "tblWorkEmployment", "[PayrollNumber]=" & Me.Parent![PayrollNumber]) = 5 Then
  MsgBox Msg, vbCritical, "Maximum Number of Entries"
    Me.Undo
End If
End Sub
 

WHERE:

1. tblWorkEmployment is the RecordSource for your Sub-Form.
2. [PayrollNumber] is the Linked Child Field in the Sub-Form.
3. Me.Parent![PayrollNumber] refers to the Master Linked Field (1 to MANY to the Child Linked Field in the Sub-Form).
4. Any other questions, feel free to ask.

[VWoody12]

Thank you for your help!!

Yes it is. In the BeforeUpdate() Event of the Sub-Form, place the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String
 
Msg = "You are at the Maximum Allowable Number of Records in this Sub-Form. The "
Msg = Msg & "Save Operation will be cancelled!"
 
If DCount("*", "tblWorkEmployment", "[PayrollNumber]=" & Me.Parent![PayrollNumber]) = 5 Then
  MsgBox Msg, vbCritical, "Maximum Number of Entries"
    Me.Undo
End If
End Sub
 

WHERE:

1. tblWorkEmployment is the RecordSource for your Sub-Form.
2. [PayrollNumber] is the Linked Child Field in the Sub-Form.
3. Me.Parent![PayrollNumber] refers to the Master Linked Field (1 to MANY to the Child Linked Field in the Sub-Form).
4. Any other questions, feel free to ask.