Running Total on current form
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.
Thanks all.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 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
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
<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
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
ASKER
Don't know what I would do without you guys. This site has been such a thrill. Thank you very much.
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