Check the subform's field sum before update

[nonlinearly]

Hello, I have a form and a subform. Every time I use the subform to make new records or to update a record I need to check if the sum of subform's field is greater than the value of parent's form field before subform's update.
It seems simple but it doesn't because you can't know the subform's field sum before update the record!!!

 

[Minty]

You should be able to use the SubForms BeforeUpdate event?

What have you tried?

 

[nonlinearly]

Thanks for your reply, as I said in the subform's before update event you don't know the current sum (I mean with the changes you have done). Only after update event you know the sum but then it is already too late!


FYI
I use a textbox in subform's footer that calculate the running sum of the field (=Sum([fieldName])). This value is updated only after the record's update so you can't know the value of sum in the beforeupdate event of the subform

 

[Minty]

But you do know the sum of the other values already saved in the other records so something like (air code obviously) this in the BeforeUpdate Event;

If Me.NewValue + Me.YourCurrentSum > Me.Parent.MAximumAllowed Then
    msgbox "Value exceeds Limit!"   
    Cancel = True
End IF

 

[nonlinearly]

This is true only when you have new record. If you edit an old record fails because the current sum also takes into account the old value so your solution will have a higher value by old value.
I have found the solution. Just thought if there was a more elegant solution:

Me.NewValue  + DSum("fieldForSum", "TABLE_HAS_THE_FIELD", "id<>" & Me.id)

Thank you for your response

 

[Minty]

NewValue possibly wasn't the brightest name for me to choose...

Me.YourIndividualValueField.NewValue might have been better....

That should work in the SubForms before update event

 

[nonlinearly]

yes you are right I corrected...

Me.fieldForSum + DSum("fieldForSum", "SUBFORM_TABLE", "id<>" & Me.id)

 

[Mark_]

Or you can reference your total in the footer.
Use your controls .OldValue to back out the original amount and add in the new amount when you do your check before you allow them to save.