New Balance from Previous Balance

[Michelle]

I have already sent that message but the formula doesn't
work. Would anyone help me on this? I have a form where
there is an Actual, Previous Balance and a New Balance. I
want to program the command button to transfer the New
Balance of the previous record to the Previous balance on
the next record, this will allowed me to see how much is
still available in the budget. Because it is the same
form I am pretty confused about this dilemma. Thank you
very much to take the time to help me on this.

Michelle

 

[Guest]

-----Original Message-----
I have already sent that message but the formula doesn't
work. Would anyone help me on this? I have a form where
there is an Actual, Previous Balance and a New Balance. I
want to program the command button to transfer the New
Balance of the previous record to the Previous balance on
the next record, this will allowed me to see how much is
still available in the budget. Because it is the same
form I am pretty confused about this dilemma. Thank you
very much to take the time to help me on this.

Michelle
.

 

[Jonathan Parminter]

-----Original Message-----
I have already sent that message but the formula doesn't
work. Would anyone help me on this? I have a form where
there is an Actual, Previous Balance and a New Balance. I
want to program the command button to transfer the New
Balance of the previous record to the Previous balance on
the next record, this will allowed me to see how much is
still available in the budget. Because it is the same
form I am pretty confused about this dilemma. Thank you
very much to take the time to help me on this.

Michelle
.

Michelle,
I'm not actually convinced that you need a button to
achieve this.
Until a record is saved it doesn't exist. So you can then
use a DSum to calculate the balance of previous records
and display the result in the previous balance field.

You could make this more interesting by including a
criteria in the DSum to only calculate records with a
primary key less than the current record. Then as you
navigate from record to record you can monitor changes in
balances.

Luck
Jonathan

 

[Guest]

Good Morning Jonathan,
This is a great idea. Because I use Access only part-
time, I do not possess the knowledge that you have,
therefore I am struggling to figure out how to enter that
formula that you are suggesting. Do you think you can
help me on this.

Here's my field.
I have Previous Balance on the first record that is let
say $10,000.00 which once all the expense (let say
$5,000.00) are deducted from will bring me a New Balance
($5,000.00). However, on the second record the Previous
Balance is being the New Balance of the previous record
and so forth.

How the formula should be written and should I write it
directly on the field or in the code section.

Thank you so much for all your patience. This project is
very important and people are waiting for it to work to
start using it.

Michelle

 

[PC Datasheet]

Michelle,

I am in business to provide customers with a resource for help with Access,
Excel and Word applications. If you would be interested in paid assistance where
I actually work on your database for you, contact me at the email address below.
My fees are very reasonable.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Jonathan Parminter]

Hi Michelle,
You can set a control source to a calculation, however
using the code procedure allows you to write calculations
that are easier to read and so maintain. It also allows
for error handling.
The following example assumes text controls with the
names:-
txtActual - bound to table field, and is total expenses
txtPrevious
txtNew
txtRecordID - bound to primary key field
also that the primary key field is an autonumber

Include in the Form_OnCurrent() event these calculations

*** code starts ***
dim curAmount as current
curAmount =DSum("[Actual],"tablename","[PrimaryKey]<" &
txtRecordID)

txtPrevious=curAmount

txtNew=curAmount-txtActual

*** code ends ***

Luck
Jonathan