Solved Limit the record in subform based of form filed number (1 Viewer)

[theinviter]

Dears;
I need a help,
I have a form named : clinic
Subform form: Delivery_Date
so there a field in clinics form named (Number of delivery) ,
so i want to limit the number of record in subform based on the number in main form.
for Example, if (Number of delivery) = 3 then the user can add only 3 records in subform.

so how can we do this?

 

[CJ_London]

Perhaps some code in the subform form after update event to check number of records in the subform and if it equals 3 or whatever number on the main form changed the subform allow additions property to no which should hide the ‘new record’ row

 

[theinviter]

Perhaps some code in the subform form after update event to check number of records in the subform and if it equals 3 or whatever number on the main form changed the subform allow additions property to no which should hide the ‘new record’ row

can you advise about it

 

[CJ_London]

To check number of records use

me.recordset.count or dcount

To change the setting
Me.allowadditions=false

 

[CJ_London]

Something like this might work in the subform after update event

me.allowadditions=me.recordset.recordcount<parent.txtMaxnum

 

[Pat Hartman]

If you use the AfterUpdate event, you can't prevent them from adding a record if they leave and come back to the form. Which means you need code in at least one more event. There is a better method.

I would use the form's BeforeInsert event. This event runs when the first character is typed. You use dCount() to find out how many records are already there and if the max has already been added, then you cancel the update and use Me.undo to clear the typed character. You don't need to set the AllowUpdates property since your code in the BeforeInsert event prevents it.