Update main table from subform of main mainform afterupdate

[jgitaunjoroge]

Am experiencing difficulties in updating main table after doing update from subform. i have two tables Customer and Customertrans. Customer should be updated when user updates customertrans through subform. After update of subform (customertrans table) i would like to sum all amount for accno=SCM0134 and update customer.paid column with the sum amount. Am not able to achieve this.



Customer

1. ACCNO ACCNAME DESCRIPTION CONTRACT WORKDONE INVOICED WIP PAID BALANCE
2. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE 649,600.00 400,000.00 -400,000.00 -400,000.00

Customertrans

1. ACCNO ACCNAME DESCRIPT CUMMBAL AMOUNT INVOICED BALANCE
2. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE -200000 2 -200000
3. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE -400000 -200000 -400000
4. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE 400000 0
5. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE -500000 -100000 -500000
6. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE -648600 -148600 -648600
7. SCM0134 PETER MUTUKU SUPPLY OF 2NOS. H/D CONCRETE MIXER CW ENGINE 248600 0

Thanks
Joseph

[NTC]

it is an incorrect design to write the sub table's sum into the main table. the sub table sum is always available as an aggregate for use/display anytime you need - so writing it to the main table is unneeded plus actually introduces potential flaws involving the necessary triggers to be sure an overwrite always occurs when a change occurs. Also were this a large db with many users - one avoids all those writes happening......

having said that; the way to do it is to make an Aggregate Query that creates the correct sum, and then make an Append Query using the Aggregate query - - you must determine when to trigger those, and also to refresh/requery the form.