By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,909 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,909 IT Pros & Developers. It's quick & easy.

Limit the number of new records a user can enter

P: 2
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
Jun 8 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim Msg As String
  3.  
  4. Msg = "You are at the Maximum Allowable Number of Records in this Sub-Form. The "
  5. Msg = Msg & "Save Operation will be cancelled!"
  6.  
  7. If DCount("*", "tblWorkEmployment", "[PayrollNumber]=" & Me.Parent![PayrollNumber]) = 5 Then
  8.   MsgBox Msg, vbCritical, "Maximum Number of Entries"
  9.     Me.Undo
  10. End If
  11. End Sub
  12.  
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.
Jun 8 '07 #2

P: 2
Thank you for your help!!

Yes it is. In the BeforeUpdate() Event of the Sub-Form, place the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim Msg As String
  3.  
  4. Msg = "You are at the Maximum Allowable Number of Records in this Sub-Form. The "
  5. Msg = Msg & "Save Operation will be cancelled!"
  6.  
  7. If DCount("*", "tblWorkEmployment", "[PayrollNumber]=" & Me.Parent![PayrollNumber]) = 5 Then
  8.   MsgBox Msg, vbCritical, "Maximum Number of Entries"
  9.     Me.Undo
  10. End If
  11. End Sub
  12.  
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.
Jun 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.