Running Total on current form

napsternova
napsternova used Ask the Experts™
on
I was wondering if this was possible.  I have a form where I enter info about an item.  In this form there is a section for multiple Square Footages.  SquareFootage1, SquareFootage2, SquareFootage3, SquareFootage4.  I also have a field for total Square Footage so if I don't know the individual Square Footages I can just enter the total.  My Table has fields for all these as well.  I was wondering if it was possible to have my Total Square Footage text box update as I enter data into the individual fields on my form.  OK, but now what if I know only 2 of the individual fields but also know the Total?  Can I enter the total manually?  If it can be done great if not, well thats the way it is.
Thanks all.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In the AfterUpdate event of each of the individual sqarefootage controls call a routine to assign the total to the total field (adjust this to match your control names):

Sub SumTotalF2()
   me.txtTotF2 = me.txtF1 +me.txtF2 +me.txtF3 + me.txtF4
End Sub

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>

Just FYI.

You may want to consider doing this calulation in a query.

This way the Query can be used as the source for the Form and the Calculated value will be in the Form by default.
Then there is no need for code at all.

The other advantage is that the query can be used as the source for a Report as well, without the need to recreate the calculation.

I say "No Points wanted" because knowing Mike, he would have mentioned it anyway.

;-)

Sample attached:

JeffCoachman
2003-1--2009-07-21.mdb
Jeff,
Thought of it, but I read the request as also needing to be able to input a total directly, as an alternative to the sum of the parts.
Cheers,
Mike
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

napsternovaDeveloper

Author

Commented:
The only problem with your samle Jeff is the fact that the user cannot edit the totals field because it is based on an expression.  Quite frankly I think it is kinda dumb to have a running total and have the ability to manually over ride the total.  I was just asked if it was doable.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
Mike,
<Thought of it, but I read the request as also needing to be able to input a total directly>
Knowing your reputation, that's what I thought.
;-)
That's why I did not request any points, I knew you were on top of this.
;-)

napsternova,
<Quite frankly I think it is kinda dumb to have a running total and have the ability to manually over ride the total.  I was just asked if it was doable.>
Seems that way to me too.
This is why Mike "Correctly" answered the question as asked.
;-)

But to be fair to whoever requested this,  ...sometimes the total may be known, just not the individual parts.
In this case an editable field is a perfectly resonable request.
However, you can't have it both ways...
IMHO, in either case, I would us a two Fields:
1. A "Working Total", that can be edited.
2. A "Calculated Total", that is based on the sum of the textboxes.

;-)

Oh, ...and one more thing, if you go with Mikes post, you should wrap the values in the NZ() Function, to avoid errors if a value is left blank.
me.txtTotF2 = nz(me.txtF1) +nz(me.txtF2) +nz(me.txtF3) + nz(me.txtF4)

;-)

Jeff
napsternovaDeveloper

Author

Commented:
Mike,
Sorry but I don't quite follow.  For every box I need to add an Event Proceedure?  Then in VB Code I have;
Private Sub Unit1_AfterUpdate()

End Sub
etc, etc Unit2, Unit3

What would my totals field code be then?  I guess I am not exactly sure what this means.
<In the AfterUpdate event of each of the individual sqarefootage controls
   this I get, LOL
<call a routine to assign the total to the total field
   this is what I don't quite understand
Sorry not to be clear. One routine to calculate total square footage:
Sub SumTotalSquareFootage()
   me.txtTotalF2 = nz(me.txtUnit1) +nz(me.txtUnit2) +nz(me.txtUnit3) + nz(me.txtUnit4)
End Sub
Then call it from each of the AfterUpdate routines:
Private Sub Unit1_AfterUpdate()
     SumTotalSquareFootage
End Sub

Jeff was right to suggest using nz() - and deserves a points share for his input ;-)
Hope this helps,
Mike
Oh, nd that was assuming that the controls were called txtTotalF2, txtUnit1, etc
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>
Mike, there is nothing in my post that you would have not suggested.
I mean it's not like NZ() is some custom function I created.
;-)
I have enough points.
(There will be other opportunities to steal points from you later anyway...)
;-)

Jeff
napsternovaDeveloper

Author

Commented:
Don't know what I would do without you guys.  This site has been such a thrill.  Thank you very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial