Updating a Table from another

[keiath]

Hi guys

I have 3 Tables 'Accruals', 'Prepayments', 'Invoices'

The invoice table is imported from outside data.

The Accruals and Prepayments and added after manually, and have these two on subforms. So that the in-putter can add accruals and prepayments accordingly.

Now once someone adds an accrual or a prepayment it forms part of the overall expenses on the invoice table.

So how do add the data added to the accruals or prepayments table into the invoice table?

In addition to adding the amount to the detail section, you can see there is Columns 'A','B', 'C' ect The in-putter is required to decide what column the invoice belongs as standard its always 'C' except accruals, prepayments they belong in other column.

I have column that does this calculation [Invoice Amount]-[A]-[B]-[D]-[E]-[F], but i have to manually add 0.00 to other columns for it to calculate, how can this be solved?

The in-putter will decide on the subform what column it needs to go to so that part is fine as that will update table and calculate column C

ID	Site Code	Invoice Date	PO	Invoice	Contractor	Invoice Amount	Expense Code	Exp Description	Lease Code	Status	A	B	C	D	E	F
1	0289S	20/09/2013	34660	3410	Dj Commercial Cleaning Ltd	25.23	62		A	Pending
2	0289S	28/09/2013	34606	1141	Platinum Landscapes	240.00	55		A	Authorized on 19/09/2013 by KF, Paid on 24/09/2013	0.00	0.00	240.00	0.00	0.00	0.00
3	0289S	03/09/2013	35311	2021	Clean And Green	167.00	63		B	Authorized on 19/09/2013 by KF, Paid on 24/09/2013
4	0289S	02/08/2013	35311	2015	Clean And Green	174.00	63		A	Authorized on 19/09/2013 by KF, Paid on 24/09/2013
5	0289S	19/08/2013	11111	5271094316Aug13	Southern Electric	189.56	79		A	Authorized on 19/09/2013 by KF, Paid on ?
6	0289S	19/08/2013	34606	1121	Platinum Landscapes	240.00	55		A	Authorized on 20/08/2013 by KF, Paid on 24/09/2013
7	0289S	02/06/2013	35311	2009	Clean And Green	160.00	63		A	Authorized on 29/07/2013 by KF, Paid on 24/09/2013
8	0289S	02/05/2013	35311	2003	Clean And Green	181.00	63		A	Authorized on 29/07/2013 by KF, Paid on 24/09/2013
9	0289S	22/07/2013	34606	1103	Platinum Landscapes	240.00	55		A	Authorized on 29/07/2013 by KF, Paid on 24/09/2013
10	0289S	20/07/2013	34397	26449	Discreet Pest Control	318.00	67		A	Authorized on 29/07/2013 by KF, Paid on 24/09/2013
11	0289S	08/07/2013	11111	850028470732July13	British Gas Business	20.91	79		A	Authorized on 15/07/2013 by KF, Paid on ?
12	0289S	27/06/2013	11111	850028470732June13	British Gas Business	14.12	79		A	Authorized on 01/07/2013 by KF, Paid on 17/07/2013

Thanks in advance

[JeroenMioch]

You can crossover data from one table to another using Union query's.
Or you can use SQL insert.

Like this example :

DoCmd.SetWarnings False
Dim strSQL As String

strSQL = "Insert into Invoice select * from Accrual where [ID] = " & Forms![MyForm]![YourFieldID].Value & ""
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

In above example ALL fields are transferred from one table to the other, with the record that meets the criteria for the ID on my form, But you can transfer some fields offcourse

Abouth the calculation of [Invoice Amount]-[A]-[B]-[D]-[E]-[F] :
Maybe its an idea to set the field default to 0.00 ?

Let me know if ive understood your post correctly.

[keiath]

At the moment the accruals subform is directly from the table, and i cant see a union, if i am understanding correctly i would have to make a query first (use that on subform) then create the union?