Check the subform's field sum before update (1 Viewer)

nonlinearly

Registered User.
Local time
Today, 06:49
Joined
Jun 13, 2012
Messages
21
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

AWF VIP
Local time
Today, 13:49
Joined
Jul 26, 2013
Messages
10,355
You should be able to use the SubForms BeforeUpdate event?

What have you tried?
 

nonlinearly

Registered User.
Local time
Today, 06:49
Joined
Jun 13, 2012
Messages
21
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
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:49
Joined
Jul 26, 2013
Messages
10,355
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;
Code:
If Me.NewValue + Me.YourCurrentSum > Me.Parent.MAximumAllowed Then
    msgbox "Value exceeds Limit!"   
    Cancel = True
End IF
 

nonlinearly

Registered User.
Local time
Today, 06:49
Joined
Jun 13, 2012
Messages
21
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:
Code:
Me.NewValue  + DSum("fieldForSum", "TABLE_HAS_THE_FIELD", "id<>" & Me.id)
Thank you for your response
 
Last edited:

Minty

AWF VIP
Local time
Today, 13:49
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 06:49
Joined
Jun 13, 2012
Messages
21
yes you are right I corrected...
Code:
Me.fieldForSum + DSum("fieldForSum", "SUBFORM_TABLE", "id<>" & Me.id)
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 06:49
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom