Update main table from subform of main mainform afterupdate

[jgitaunjoroge]

Hallo All,

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. eg. column customer.paid should be updated when column customertrans.a mount is updated. So please how do i go about this.

Thanks in advance.

Joseph G. Njoroge

[zmbd]

Customer and Customertrans. Customer should be updated when user updates customertrans through subform. eg. column customer.paid should be updated when column customertrans.a mount is updated

Lot's of information and very little to go on here.

The first question I have concerns your reasoning for updating your tbl_customer based on the many side of the relationship? By query, one should be able to find the entry in the tbl_customertra ns by customer to determine if the customer has paid. No entry, no payment.

You might find our Insights article> Database Normalization and Table Structures to be a good resource for your database design.

[jgitaunjoroge]

Thanks ZMBD,
My database have all the above. My problem is how to do the following. in customer table i have a record of this nature.

Customer

ACCNO	ACCNAME	DESCRIPTION	CONTRACT	WORKDONE	INVOICED	WIP	PAID	BALANCE
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

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

relationship am using accno
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.

Thanks,

[zmbd]

jgitaunjoroge:
Sorry for the delay.
Been a very busy day and now of course, it's late where I am; thus a quick post and a question:

Is this your actual data or a condensed version?

Also, from the Customertrans data table
Is it correct that you want:

sum([amount]) ==
    sum(-200000,-400000,400000,
    -500000,-648600,248600)

= -1100000

If both are true, then normalization is your friend here
and I highly advise using a totals query to generate your values when needed instead of storing them; however, if you insist:

The domain functions DSUM() (yuck) in an UPDATE query will also work to update the Customer table - can be slow on large recordsets.

You will most likely need a bit of VBA to run the action query either as a stored query or from within the code.