Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totalling a Subform On a Form (Access 2000)

    I'm trying to figure out how I total up an Applied amount on a Subform, and put the total on the Form itself. I have a Payments form and you enter the payment then go to an Applied subform where you say what amounts you apply to each line of the invoice. Well, I want to be able to add up the amount Applied on the subform and compare it to the amount entered on the form itself to see if it equals. That way someone can't enter a payment of e.g. $50 and only apply an amount of $40. Any ideas? I tried to add a field to the form and use the expression builder to add the sum of the Applied amount (from the subform), but it gives me an error.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    You can use DSum for this. Let's say that the record source of the subform is called TableName (in fact, it can be a table or query), that the field you want to sum is named Amount and that the field that links tha main form and the subform is named PaymentID (a numeric field). Create a text box on the main form with control source

    =DSum("Amount", "TableName", "PaymentID = " & Me.PaymentID)

    This text box won't get updated while you are working in the subform (you would need code to do that), but it will if you move to another record in the main form. If you want the user to be able to force recalculation, put a command button cmdRecalculate on the main form, with On Click code

    Private Sub cmdRecalculate_Click()
    Me.Recalc
    End Sub

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    In the subform, you can add up the amounts applied by putting a text box in the form footer, and setting its control source to:
    =Sum([AmountApplied])

    If you call this control something like txtTotalApplied, you can transfer this total to the main form, by putting another text box on the main form and setting its control source to :
    =[Forms]![PaymentsReceived]![PaymentsAppliedSubform].[Form]![txtTotalApplied]
    You would need to rename parts of this.

    These figures will automatically update as you work in the subform.
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    I tried what you suggested. I even tried what HansV suggested, but I could not get either one to work. All I get is a "Name?". I don't know what I'm doing wrong.

    My subform comes from three different tables. The source is not a query, I just pulled the three tables/query. The subform is made up on three tables/query: tblInvoices, Inv Details With Fee Name (query), tblApplied. I joined InvID from the "tblInvoices" and did a join 1 to InvID in "Inv Details With Fee Name" query. I joined LineNo from the "Inv Details With Fee Name" query with "tblApplied" using a join 1.

    The form itself is comes from "tblPayments". I think the form is linked to the subform by StudID. How can you tell this? You select the student you want from another screen, then you click Payment and get this main form with a subform showing the amount of each line of the invoice plus the amount still outstanding, and the amount of the applied. You type the applied amount in for each line to equal the amount of the payment being made. The amount of the payment is on the form. I want to do a check to see that the total of the applied equals the amount of the payment. Any other suggestions?

    My frmInvoice form works roughly the same way, and the total there works fine. The frmInvoice comes from the tblInvoices table. The subform comes from the tblInvoiceDetails table.

    To get the invoice total on the frmInvoices form I typed in =[frmInvoiceDetails Subform].[Form]![InvoiceSubtotal] for the Control Source.

    I've attached a screenshot of my frmInvoices with the frmInvoiceDetails subform.

    This has really got me stumped!
    Attached Images Attached Images

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    Why don't you post your database zipped.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    I emailed it to you because I couldn't seem to get the file small enough. I managed to get it to 103 k, but no smaller. Thanks.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    Sorry Jen, but I deleted the email you sent.

    Bad habit I guess, would you please send it again.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Totalling a Subform On a Form (Access 2000)

    Try the following for the total on the main form:
    =[Forms]![frmPayments]![Applieds].[Form]![SFSubtotalApplied]
    What you had done was to use the subforms name rather than the control name of the subform on the form, if that makes sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •