Soupbone79
asked on
Help with total from subform to form and report
I have a Invoice database that I need put a Sum of Price from a subform and put on form and also on a report for printing the same thing on form. Here is what I started with Invoiceold and Invoice is the new one. Please look at what I am doing so you understand my thinking.
Invoice.zip
Invoice.zip
In your report, add a text box and use this formula as the control source:
=Format(DSum("Price","Euip ment Table","InvoiceID= " & [InvoiceID]),"$0,000.00")
=Format(DSum("Price","Euip
ASKER
that works. but it gives error on new record until put something in. Then it doesn't show total until you close form and reopen it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok the code for auto update did not work did I do something wrong? I did change the Dsum To Sum. Here is my changes in file
Invoicenew-.zip
Invoicenew-.zip
DSum will be too slow. It's much faster to recalculate from within the subform than waiting for the main form to discover that something has changed.
/gustav
/gustav
ASKER
Sorry I have got it work right in subform show in the main form yet. I got it work in report no problem when I print. when I go to add new record it shows #error until some data is imputed the it is blank until I close form then reopen it. If I click my command button to print (lunching Invoice report) it shows total. But when I come back to form no total until I close form and reopen it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you use DSum, you will have to requery the field on the main form for any change of the amount in the subform. Sum may work but with a delay. Been there, done that. It is just to slow for busy users.
/gustav
/gustav
Here is a sample using a non VBA approach. For the Invoice Form, the totals are obtained from the text box you placed in the subform. For the report, another price textbox was added and set to running total over group (Visible=False)
Invoice-.mdb
Invoice-.mdb
ASKER
I look at yours no sure where you are going with that, sorry. Look at mine now (in the form and other places) and see what it does when you add new record and before and after input of data and also click print command button. Go ahead and try all command buttons.
Invoice--new2.zip
Invoice--new2.zip
ASKER
sorry forget that one try this one.
Invoice-new-3.zip
Invoice-new-3.zip
ASKER
Thank you gustav ! once again you blow me away. so the code was
Private Sub Form_Current()
Call Me.Euipment_Table_subform. Form.Calcu lateTotal
End Sub
was that the fix on the form?
Private Sub Form_Current()
Call Me.Euipment_Table_subform.
End Sub
was that the fix on the form?
I just copied and pasted the code i posted previously, and adjusted to the name of your amount-field (Price).
And then I added the call from the main form which I had forgot. It sets the value when switching invoice ID.
/gustav
And then I added the call from the main form which I had forgot. It sets the value when switching invoice ID.
/gustav
ASKER
was that on the subform?
ASKER
how di you get it to go to the unbound?
ASKER
sorry bad typing how did you get it on the unbound txt box on the form?
ASKER
was this it on the subform
Private Sub Form_AfterDelConfirm(Statu s As Integer)
Call CalculateTotal
End Sub
Private Sub Form_AfterUpdate()
Call CalculateTotal
End Sub
Public Sub CalculateTotal()
Dim rst As DAO.Recordset
Dim LineSum As Currency
LineSum = 0
Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
LineSum = LineSum + Nz(rst!Price.Value, 0)
rst.MoveNext
Loop
End If
rst.Close
Me.Parent!txtTotal.Value = LineSum
Set rst = Nothing
End Sub
Private Sub Form_AfterDelConfirm(Statu
Call CalculateTotal
End Sub
Private Sub Form_AfterUpdate()
Call CalculateTotal
End Sub
Public Sub CalculateTotal()
Dim rst As DAO.Recordset
Dim LineSum As Currency
LineSum = 0
Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
LineSum = LineSum + Nz(rst!Price.Value, 0)
rst.MoveNext
Loop
End If
rst.Close
Me.Parent!txtTotal.Value = LineSum
Set rst = Nothing
End Sub
No, that's the code I added.
From here:
https://www.experts-exchange.com/questions/28571831/Help-with-total-from-subform-to-form-and-report.html?anchorAnswerId=40473341#a40473341
/gustav
From here:
https://www.experts-exchange.com/questions/28571831/Help-with-total-from-subform-to-form-and-report.html?anchorAnswerId=40473341#a40473341
/gustav
ASKER
Well gustav thank you again! Maybe someday I will learn enough that wont have bother you all.
You are welcome!
/gustav
/gustav
=DSum("Price","Euipment Table","InvoiceID= " & [InvoiceID])
Flyster