sum macro

[elios11583]

wat expression can i use with a set value macro to sum records from a table
when i use the Sum in the expression field i get errors saying that it cant find the form refering to its record value.

 

[elios11583]

i have a form that contains two nested subforms relationships already set and working fine
size1, size 2, qty and total qty are fields from the nested subform.
qty is calculated from size1*size2
total qty will be the sum of "qty".
i need to store these calculated fields qty and total qty in the table.
i managed to use the setvalue macro to calculate qty and store it in the qty field in the table.
now i m trying to sum up qty to get total qty using a macro setvalue action
the expression im using is sum(qty) but im always gettin errors in the expression.
it says cannot parse expression sum
if i use sum(size1*size2) it says cannot find size1 or cannot find the nested suform .
wat to do plz?

 

[elios11583]

no the records are in the one subform.
i used the Dsum function its working but i cannot save the value in the main form table although im referencing to this field in the table.
not a problem i can save it in the subfrom table.
one more problem the form subform are linked with a one to many relationship with ID field.
when i use the macro to show the sum, it s showing for all form records, it s not segragating them per ID. i tried to set the criteria so that "[Order ID]=Forms![mainform]![Order ID]"), but its giving an error because of the reference to the field in the main form.
how to set it up plz
thank you in advance

 

[elios11583]

thank you man for ur time.
in fact the main and subform are linked together and everything is fine concerning this matter.but when it comes to the macro, it s calculating the sum and showing it for all IDs.
lets say ID=1, qty=3 ID=2, qty=2
ID=1, qty=2 ID=2, qty=2
ID=1, total qty=5 ID=2, total qty=4

instead when i start inputing the ID=2 it sums up to the total qty of ID=1
the total qty will be total qty(ID=1)+ qty(ID=2)=5+2=7
the macro to set total qty is on double click of total qty field on the subform.
any suggestions?

 

[elios11583]

yeah i read it, the criteria must set that the sum should be performed grouped by ID.that is sum the qties that have same id together.but it s not applying here.dont know why