Storing running total

I was able to get this to work before, but I can't get it to work anymore for some reason. What I was looking to get done was have a transaction log for banking/account balance purposes. This is for a small organization, so once a value is added, they cannot go back to edit the cost. I have the following setup:
Table 1 (bod_account)

-Account ID

-Account Name
Table 2 (bod_transactions)

-credit

-debit

-balance
I have separate Add Expense and Add Deposit pages. Using the Add Deposit Page, I have the following code:
Before Record Added:

$sql = "SELECT MAX(balance) AS total FROM bod_transactions";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['balance'] = $values['total'] + $data["credit"];

After Record Added

CustomQuery("update bod_account set balance=balance+" . $values["credit"] . " where account_id = 1" );

When a Deposit is added, the After Record Added works just fine by updating the final balance on table 1. What's not working is for the bod_transactions table with the Before Record Added, where it needs to read the last balance, and add to that to derive a new balance for the new deposit. I'm just trying to store the running balance, as using a dynamically created running total won't work between pagination. Can't figure out why this isn't working for some reason on v10.
Also, if Sergey is the one who reads and replies and you were the one who sent the last e-mail about the hacker, I commend you with the patience with GoDaddy. I have my website through godaddy, and after reading your experience, I will be moving it as soon as my contract is up.

Have You tried one of two options.
After Record Added. with the same query and change $data to $values.
OR Before Record Added
$sql = "SELECT MAX(balance) AS total FROM bod_transactions";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['balance'] = $[size="2"]values[/size]["total"] + $values["credit"]; //[size="3"]not data[/size] : it's not a button correct? AND total is an alias and not withstanding a field called total and and an alias called total???

Have You tried one of two options.
After Record Added. with the same query and change $data to $values.
OR Before Record Added
$sql = "SELECT MAX(balance) AS total FROM bod_transactions";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['balance'] = $[size="2"]values[/size]["total"] + $values["credit"]; //[size="3"]not data[/size] : it's not a button correct? AND total is an alias and not withstanding a field called total and and an alias called total???

Hi John,
It seems you've gotten me into the right direction as I had my calls mixed up by having to use

$values

instead of

$data

or vice versa. The total is an alias from the data in the balance column of the database where the value is stored. Since I got that solved, I also found that my sql query of selecting MAX would return the highest number and not the last transaction id balance. Through trial and error, I was able to solve it with the following code:
Adding a deposit/credit

Before Record Added - This adds to a transaction log that stores the running total balance so it can be used with pagination/reports. Values will not update if a record is added, which is why I have the credit value as read only on a edit page.

$sql = "SELECT(balance) AS total FROM bod_transactions ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['balance'] = $data["total"] + $values["credit"];

After Record Added - This is used to update the separate table where just the balance is stored. At any given time, this value should equal the last value of the balance from the code above.

CustomQuery("update bod_account set balance=balance+" . $values["credit"] . " where account_id = 1" );


Adding a expense/debit

Before Record Added

$sql = "SELECT(balance) AS total FROM bod_transactions ORDER BY id DESC LIMIT 1";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$values['balance'] = $data["total"] - $values["debit"];


After Record Added

CustomQuery("update bod_account set balance=balance-" . $values["debit"] . " where account_id = 1" );

Thank you so much for the help John, and getting me on the right track. This can be marked as solved!