Moving a value from 1 table to another

Question

What's the best way to do the following using access 2007?

I want to move a value from 1 table to another. If table1 has the value 1000, I would like to move some of that value to table2. Lets say I want to move 50 to table2 from table1. The end result should be that table1 is left with 950, and table2 is left with 50 if it started with 0, i.e. 50 has been moved from table1 to table2.

I can't seem to do this with access 2007 sql.

I have the following so far which will be based on a form later, but for now I have hard coded the 50 value, but it gives me a syntax error:

insert into table2
(value)
select 50
from table1 where id = 1;
update table1
set value = (value - 50)
where id = 1;

The insert, select and update work fine when separated, but when combined linke this, they don't work.

How do I get around this issue?

Answer 1

Using VBA you can do the following (on a button click for example):

Private Sub Button1_OnClick()
    Dim s as String

    s = " insert into table2 (value) select 50 from table1 where id = 1; "
    CurrentDb.Execute s
    s = " update table1 set value = (value - 50) where id = 1;  "
    CurrentDb.Execute s
End Sub