Auto updating a total

Dreamcatcher

Registered User.
Local time
Today, 14:52
Joined
Jul 15, 2008
Messages
19
Probably an easy solution but i'm a bit of a newbie and just need a little code help.

Basically, i have a form SFrm_Unders_Overs which controls a finance fund for each salesperson. The form allows you to enter a Fund_Adjustment figure and i just want the Fund_Value control to auto update the total fund amount after each adjustment.

I.e Adjustment1 = -$5 Fund_Value = -$5, Adjustment2 = $30 Fund_Value = £25 etc

I've currently entered After Update code against Adjustment_Value -
Private Sub Adjustment_Value_AfterUpdate()
If Me!Adjustment_Value <> 0 Then
Me!Value = Me!Value + Me!Adjustment_Value
End If
End Sub

Currently this is just updating the Fund_Value with the latest Adjustment_Value amount and not accumulating the adjustments for a running total in the Fund_Value.

Any help would be appreciated.

Regards
 

Attachments

Hi,
i've just checked your database and the code works fine unless i'm missing something.

In Consultants Records in the UNDERS / OVERS Fund you type the adjustment in the Adjustment_Value field and the Under/Overs Fund gets updated. If this isn't right what should it be doing?

Regards
JD
 
Probably didnt explain too well.
The Under/Overs Fund needs to be a running total showing how much is in the fund as a total.

Ie. Fund_Value is currently £100
You come along and put a new record in entering an Adjustment_Value of £50. The Fund_Value should then change to £150.

Regards
 
OK i see what you mean.

What you want to do is just add a new record on a new line with a new adjustment amount. The trouble is Access can't just look at the previous value. A quick way would be to Press CTRL+APOSTROPHE (') in the Unders/Overs field to get the previous amount then put in your new adjustment value.

If this is not acceptable you will need some clever code.
 
What if the actual Fund_Value was stored in another table (tblConsultants) and the adjustments table just held the adjustments?
Could i then do an after update prodcedure once the adjustment_value was entered to update the field in tblconsultants?

If so, what code would i require to do this?
 
You may be able to use a DSum to help with your problem - do a search for DSum or running total and see where that takes you.

Regards
JD
 

Users who are viewing this thread

Back
Top Bottom