How to limit the # of records in a from

[emsadoon]

I want to limit the # of records being entered in a form according to a limit value which has to come from a query. I greatly appreciate it if you help me how to code this.

 

[RuralGuy]

A lot would depend on what view of the form you are using. Is it a Form/SubForm arrangement?

 

[emsadoon]

Well. I have an order form(grandparent) that has a subform called "packing slip"(parent). Once I double click on a row of the packing slip subform, a seperate form called product form(child) will pop up. So, order has multiple packing slips, and packig slip has multiple products. I want to limit the total number of products (childeren) being added to be less than or equal to the number of product requested that is defined in a field in order form (grandparent). I know it sounds crazy!!!!!! My thought on that so far is to create a query that counts all the products in the entire packing slips related to an order, and somehow define a code that limits the product allocation to be less than or equal to the the number of product requested in the order form.

 

[RuralGuy]

Does MainForm (grandparent) have multiple products on it?

 

[emsadoon]

No, The main form has multiple packing slips ID on it. Products has their own form.

 

[RuralGuy]

So where in your system does it know how many of each product was ordered?

 

[emsadoon]

There is a field on my Order form that has the # of product requested.

 

[RuralGuy]

I think I'm getting a little lost here. Your GrandParent Form *is* the OrderForm, right? This form displays on a SubForm the Packing slips where this product has been shipped to this Customer, right?

 

[JHB]

... My thought on that so far is to create a query that counts all the products in the entire packing slips related to an order, and somehow define a code that limits the product allocation to be less than or equal to the the number of product requested in the order form.

Once you've got the query to run, you can set the form's property "Allow Addition" to "False" from code, for limitation.

 

[emsadoon]

I found the following code from one of the replies in this website. I just want the "RecordCount" to be dynamically changed according to my query value. Please let me know if this is possible.

Private Sub Form_Current()
If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 5 Then
MsgBox "You have reached 5 records", vbCritical, "Limit Reached"
Me.Form.AllowAdditions = False
Else
Me.Form.AllowAdditions = True
End If
End Sub

 

[RuralGuy]

Limiting the records is pretty simple. Determining what number to which to limit it to is the question.

 

[JHB]

I found the following code from one of the replies in this website. I just want the "RecordCount" to be dynamically changed according to my query value. Please let me know if this is possible.

Private Sub Form_Current()
If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 5 Then
MsgBox "You have reached 5 records", vbCritical, "Limit Reached"
Me.Form.AllowAdditions = False
Else
Me.Form.AllowAdditions = True
End If
End Sub

I would not use RecordsetClone, because you can't trust it, I would use Me.Recordset.RecordCount.