We help IT Professionals succeed at work.

Help with total from subform to form and report

Soupbone79
Soupbone79 asked
on
234 Views
Last Modified: 2014-12-02
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Try using this DSum formula in the total text box (Text6) on your Invoice Form:

=DSum("Price","Euipment Table","InvoiceID= " & [InvoiceID])

Flyster
CERTIFIED EXPERT

Commented:
In your report, add a text box and use this formula as the control source:

=Format(DSum("Price","Euipment Table","InvoiceID= " & [InvoiceID]),"$0,000.00")

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Your form has been modified to work.

/gustav
Invoice-works-.zip

Author

Commented:
sorry forget that one try this one.
Invoice-new-3.zip

Author

Commented:
Thank you gustav ! once again you blow me away. so the code was
Private Sub Form_Current()

    Call Me.Euipment_Table_subform.Form.CalculateTotal
   
End Sub

was that the fix on the form?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
was that on the subform?

Author

Commented:
how di you get it to go to the unbound?

Author

Commented:
sorry bad typing how did you get it on the unbound txt box on the form?

Author

Commented:
was this it on the subform

Private Sub Form_AfterDelConfirm(Status 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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

Author

Commented:
Well gustav thank you again! Maybe someday I will learn enough that wont have bother you all.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.