Running Balance on Form

[mdg]

I have 2 things that I would like my form to do if anyone has a solution that would be great. My database tracks daily activity at work and one of the forms has 3 fields for tracking postage used throughout the day and keeps a running balance as the postage is being used. So the form displays how much money I started the day with, the amount I am using today and the end balance for the day. The fields are: StartingBalance, PostageUsed and EndingBalance. Right now I am manually entering the numbers. What I want to be able to do is copy the 'EndingBalance' from the previous record to the 'StartingBalance' of the new record. In addition I want to be able to have the 'PostageUsed' field be able to keep a running sum as the day goes along. I have tried using another unbound field to do this but the math isn't working. I am trying to make it where when I enter a dollar amount in the unbound field it will automatically updates the PostageUsed field which then auto calculates the EndingBalance field (the EndingBalance field already works ok). Thanks for any advice...

 

[CJ_London]

What other fields do you have in your table? If you have an autonumber field or a timestamp field it can be done by looking up the previous balance.

Need to know what you have to provide the solution

 

[mdg]

Thanks for your response. I just got back to my computer and saw your post. There is a time stamp (date field) that is the primary key and only one record per day can be completed, so the last day (last record) comes up first when the form is opened. I attached a screen shot of the portion of the form I am referring to. The' Postage:' area keeps the balance for postage throughout the day. The 'Balance' field from the last recored is copied and is auto filled into the Starting Balance field in a new record when 'New' is selected. RIght now I have that working somewhat. I followed a link on this site yesterday and found the code. When I press 'NEW' (I installed the command line here and it seems to work
Private Sub Command89_Click()
DoCmd.GoToRecord , , acLast
Const cQuote = """"
Me!StartingBalance.DefaultValue = cQuote & Me!total.Value & cQuote
DoCmd.GoToRecord , , acNext
End Sub

it goes to the last record, copies the 'Balance' Field and and places that value into the Starting Balance field. For now this will work for me. There's probably a better way as well.

However, the other issue I really wanted to be able to do was to use the unbound field 'ADD' (near the top) and sort of use that field to add in postage(or subtract) amounts as the day progresses, and have this number (in the 'ADD' field) be applied to Postage Used. In other words, the Postage Used field would be a running sum of sorts. The 'Balance' is already working in the qurery and reflects the difference between Starting Balance and Postage Used. I hope this helps my explanation. Thank you again for any insight.

 

[CJ_London]

All you need to do is in the after update event of your unbound ADD control is put the following (check spelling!):

Postage=Postage+ADD

To trigger the after update event you need to click off the control

If you enter an incorrect value, you will need to then enter a correcting figure.

What may be better is to actually put the code above in the on click event of a new button or the double click event of the ADD control

 

[mdg]

Thanks CJ_London., I will give it a try. I have to do something else right now but will try it later and let you know how it works. I was trying something like that earlier and was adding up funny for some reason.

All you need to do is in the after update event of your unbound ADD control is put the following (check spelling!):

Postage=Postage+ADD

To trigger the after update event you need to click off the control

If you enter an incorrect value, you will need to then enter a correcting figure.

What may be better is to actually put the code above in the on click event of a new button or the double click event of the ADD control

 

[mdg]

All you need to do is in the after update event of your unbound ADD control is put the following (check spelling!):

Postage=Postage+ADD

To trigger the after update event you need to click off the control

If you enter an incorrect value, you will need to then enter a correcting figure.

What may be better is to actually put the code above in the on click event of a new button or the double click event of the ADD control

I forgot to thank you for your reply to my posts. I did in fact use your suggestion and it works just fine. I just now got back to this project and noticed that the postage was still not working, then remembered our exchange a couple of weeks ago. I looked up my post and did what you said and all is well now. Thanks so much again.