Forms & Multiple Tables

[ShuttleDIK]

Hi.

I have a form which contains data from 2 tables.

As info is entered into my Transactions table (through the form) - I want to adjust the balance field in my Accounts table by the transaction amount.

My problem is: The equation and references I put in through the expression builder don't allow the update and return an error.

How do I perform the calculations on the accounts table from the form?

Thanks for your help!!

 

[Rich]

You should not store a calculated balance in a table use a query instead.

 

[ShuttleDIK]

Why? I already have a column for the data I need - I just want the 'current balance' field to change.

I did end up having to do a query tho. It worked out ok - just wasn't my first choice....

No big. Thanks for your help, tho. ;-]

 

[Rich]

A "stored balance" has no place in a relational database which is what access is.

 

[ShuttleDIK]

Ok - now you just arent' making sense to me.
Why wouldn't I want to refer to balances in a database?!

Not only a running balance, but the opening balance as well.

Your short answers bring up more questions than they solve... lol

Thanks,tho. :]

 

[Chris RR]

Why wouldn't you? Well, the short answer is that it is asking for trouble.

A balance is a picture at a point in time. If you have all of the transactions, you should be able to recreate any balance, at any time.

If you store the balance, and any little tiny piece of data gets changed (on purpose or accidentally) your balance must change or it is wrong.

So, in a perfect world, balances get CALCULATED, not saved.

In an imperfect world, you need to be very, very careful with them. They need to be in the right place in your database schema, you need to protect them from accidents & you need to audit them.

 

[ShuttleDIK]

Thanks,

Had it covered. The transactions are stored in a separate table. The Current Balance is always the Current Balance. I can always re-create any balance from any day.

The only thing I was trying to do was update the current balance to reflect just that. If I placed that # in a field that moved around with each transaction, I'd be chasing it like crazy.

Maybe I'm just not explaining myself correctly. I did solve the problem, btw. And it was with a query. I still don't see why it has to be that way - but I'm flexible.

Thanks again. ;-]