How To Calculate A Subtotal In A Continuous Subform

[mastromb]

I have been trying to find a solution for the same issue. I followed your suggestions here Maximus and got the same result as dr_destructo, #error. The one possible difference I have compared to your example maximus is the my main form reference to the subform text box is located in a tabbed control not in the main form footer. Would that cause the error?

[mastromb]

maximus, I took you example and modified it to reference a query and have the subform on a tab control similar to my database and got every thing to work the way it is suppose to. However when I tryied to apply the same coding to my database I get the same"#Error" message in the main form subtotal text box. These seamingly simple problems can be so frustration when you can't figure out how to fix them.

[maximus]

I see you are online just zip your database and let me see.

[maximus]

zip your database let me see convert save it as access 2000 mdb

[dr_destructo]

I just figured out my issue.
In my subform, I have 3 text boxes(qty, price, total) and 1 combobox(item). [Qty] is entered manually. The combo selection then filled the [price]. Then the [total] was calculated [qty]*[price]. The [subtotal] wasn't being calculated because the value of [price] wasn't stored, so that gave me a 0.00 subtotal. Thanks for all your help though, i do appreciate it.

[dr_destructo]

So, my next questions is going to be:
Is there a way to subtotal a filled textbox? I've tried =Sum([qty]*([item].[column](2))) but that's giving me #error in the [subtotal]. I guess worse case we have to input the prices manually, but I was looking to make it more streamlined than that.

[maximus]

I have done the following things for you:
in Client form:
1) Auto populate Price and Total Fields
2) Sum of Total in the order Subform.
3) Reflecting the same Balance in the Main client Form

I have used Dlookup to populate price. Check AfterUpdate Event of Quantity Field in Order Subform.


in Client1 form:
1) Auto populate Price and Total Fields
2) Sum of Total in the order Subform.
3) Reflecting the same Balance in the Main client Form

I have used column property that you were trying to do to populate price. Check AfterUpdate Event of Quantity Field in Order Subform.


Code used:
Order Form:

AfterUpdate Quantity Field:

Private Sub Quantity_AfterUpdate()
Dim curPrice As Currency
curPrice = IIf(IsNull(DLookup("[Product_price]", "product_price", "[product_id]=" & Me.product_id)), 0, DLookup("[Product_price]", "product_price", "[product_id]=" & Me.product_id))
Me.Price = curPrice
Me.Total = Me.Quantity * Me.Price
End Sub


Code used:
Order1 Form:
Private Sub Quantity_AfterUpdate()
Me.Price = Me.product_id.Column(2)
Me.Total = Me.Quantity * Me.product_id.Column(2)
End Sub

in both the cases if you change the price it will not effect any entries that you had made before the change.

hope this solves your problem.if does mark this thread solved.

[dr_destructo]

You're a rockstar! Many Thanks.

[maximus]

Please mark this thread solved. Go to Thread tools and select Solved.
To learn How follow this link:
https://www.accessforums.net/forum-s...lved-1828.html

[babyface_38]

Hi, I am having trouble with calculating a grand total in a continuous form... i put in this calculated field =(([Carpet Total])+([Extra Total])) this is adding two fields together to find a sub total and its all the subtotals of the different record i want the grand total of and it only seems to calculate the first rows total and not the other rows in the form.. and i want to calculate new record as they are inputted any help? Thanks

[maximus]

well the calculative Field that you have added =(([Carpet Total])+([Extra Total])) will only give you the total of a Single Row.

Add a Text Box to the Footer of the form with the Following expression:
=Sum([Carpet Total]) + Sum([Extra Total])

I have attached a sample db for reference. if any more problems please let me know.