Calculated control updates according to last calculation data

[qshaz]

Hello everyone

I need to do this on my database...My main table is tblReturns, and I have a form called Returns. Now inside that I have a field called %MTD which is the percentage return a user will enter every month. I have placed a caculated control on the form that is =100*(1+[%MTD])
Now I need to a)Store tis value in my table in the cumreturns field (this I think I can manage)
b)But then I need this field to change its calculation next month. SO next month it would not be 100*(1+[%MTD]) but whatever was the cum return for the last month*+[%MTD]) and so on for every month. This is easy enough to set in Excel by cell references for the last cell...but how do I do this in Access? Help!

Thanks!

 

[TerpFan2001]

I think the easiest thing to do would be to have a table of investments that had a related table of monthly returns. For each record in the Investment Table, you would have a field for Initial Investment and a field for Current Balance.

When you create a new record in the Investment Table (i.e. Investment A, IBM Stock, etc.) set the Initial Investment = to the Current Investment (from your example you would set both fields = to $100. Each month when you calculate your monthly return, use the amount from the Current Balance field to run your calculation.

Set your form up to have three text boxes (txtCurrentBal, txtMonthlyReturn and txtNewBal). txtCurrentBal will get its value from the table. txtMonthlyReturn will be input by the user. txtNewBal will be calculated (txtCurrentBal * 1 + txtMonthlyReturn)

Place 2 command buttons on your form (cmdCalculate and cmdUpdate). Click cmdCalculate to calculate the value for txtNewBal. Click cmdUpdate to change the value for txtCurrentBal to the value of txtNewBal. i.e. me.txtCurrentBal = me.txtNewBal.

 

[qshaz]

TerpFan2001, Thanks a lot! Thats doing exactly what I need! Now, how can I automate all this? I do have one funds table, and one returns table. So when someone enters the first return for a particular fund, you are right, its current bal should default as 100. So basically, if a particular fund has no return entered yet, then someone clicks on enter returns, then automiatically this should be = 100 for the current balance. Is there anyway to do this, so that we arent always entering 100 everytime we have to add a return for a fund for the first time? And second, so that someone doesnt click the update button before the balance button (Esp if he doenst click it for a previous record which would mess up all the balances!), can I automate the whole thing without buttons? So that as soon as someone enters a new returns, it updates the current balance, and calculates the new balance? Thanks so much for your help again!
Shazia

 

[qshaz]

Well obviously, setting the default value as 100 answers my first question. LOL. Sorry about that one! Still need help on automatating the second process, though! Thanks again!

 

[TerpFan2001]

Yes, you can place same code in the "After Update" event for the text box.

I can think of several issues you will have to watch out for:

1. Make sure all the values are correct before the update takes place. (i.e. No null values have been entered, User added 800% return instead of 8%

2. Return for the month has not been updated previously

Hope this helps

 

[qshaz]

Yes, I did already put the code for cmdCalculate into the after update property, and of course that works perfectly, giving me the right new balance everytime. But when I goto a new record, I will still need the cmdupdate button right? To set the current balance value each time to the newbalance value?

Thanks a lot, you *have* helped me a great deal!

 

[qshaz]

Hello

I need help again (figures!)
This is what I did:
I kept the after update code as above. Then in the on current property, I put this:

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.Recordset.Clone

If rs.EOF Or Not Me.NewRecord Then
' don't do anything if there's no records or it is not a new record
Else
With rs
.MoveNext
Me.Cumreturn = Me.txtNewBal

End With

End If
End Sub

And it works perfectlly. Until I close the form, and then open it up again (which is naturally what will happen...no one will enter all the months in one sitting!)...because newbal is now empty, then so is cumreturn! Any suggestions??

Rgds
Shazia