Subtract from one record and add to another in access

[shanea.kr]

In my database I have a list of organizations with a certain goal. If that organization can not hit that goal we have to "reallocate" the left over goal to another organization. As it stands now I have the following fields, Goal (which should stay stagnant), change (to either subtract from one organization and add to another) and New. What I would like to do is have a way to have a way to calculate if there is a change to maybe have a drop drown that when the change is subtracted I can add that to "new" of another organization.

[June7]

Saving aggregate data is usually a bad idea. Best practice would be to enter 'transactions', one to reduce an organization goal and another to increase an organization goal. Queries would calculate sum of transactions to derive the net change in goal. This will give you an auditable trail for the changes. Otherwise, if something goes wrong, how will you fix? Do you have paper backup?

However, if you really want to 'adjust' the Change field, can use VBA code to accomplish. Have a combobox to select organization to reallocate to the an UPDATE sql action could modify the record in table.

[shanea.kr]

Would I do the Combobox and sql action in a form, query?

[June7]

I would use VBA to execute SQL. Real trick is figuring out what event to put the code in - button Click, combobox AfterUpdate?

CurrentDb.Execute "UPDATE tablename SET Change = Change - " & Me.textbox & " WHERE ID = " & Me.combobox

This assumes value from combobox is a number ID for the selected organization.