Solved

Help with total from subform to form and report

Posted on 2014-11-30
22
171 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
0
Comment
Question by:Soupbone79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 4
22 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40472941
Try using this DSum formula in the total text box (Text6) on your Invoice Form:

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

Flyster
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40472944
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")
0
 

Author Comment

by:Soupbone79
ID: 40472974
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 40473341
Call a simple function like this from the AfterUpdate and AfterDelConfirm events of the subform:
Private 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!LineTotal.Value, 0)
            rst.MoveNext
        Loop
    End If
    rst.Close

    Me.Parent!LineSum.Value = LineSum

    Set rst = Nothing

End Sub

Open in new window

Using Sum (in the form as controlsource of a textbox) or DSum is too slow in most cases.

/gustav
0
 

Author Comment

by:Soupbone79
ID: 40473541
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40473571
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
0
 

Author Comment

by:Soupbone79
ID: 40473758
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.
0
 
LVL 22

Accepted Solution

by:
Flyster earned 100 total points
ID: 40473775
Gustav is correct about the speed of DSum. Try this expression in you Invoice Form total text box (Text6)

=[Euipment Table subform].[Form]![Text8]
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40473800
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
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40473906
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
0
 

Author Comment

by:Soupbone79
ID: 40474361
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40474409
Your form has been modified to work.

/gustav
Invoice-works-.zip
0
 

Author Comment

by:Soupbone79
ID: 40474410
sorry forget that one try this one.
Invoice-new-3.zip
0
 

Author Comment

by:Soupbone79
ID: 40474439
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?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40474453
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
0
 

Author Comment

by:Soupbone79
ID: 40474465
was that on the subform?
0
 

Author Comment

by:Soupbone79
ID: 40474469
how di you get it to go to the unbound?
0
 

Author Comment

by:Soupbone79
ID: 40474472
sorry bad typing how did you get it on the unbound txt box on the form?
0
 

Author Comment

by:Soupbone79
ID: 40474480
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40474807
0
 

Author Comment

by:Soupbone79
ID: 40474952
Well gustav thank you again! Maybe someday I will learn enough that wont have bother you all.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40475582
You are welcome!

/gustav
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question