Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Total a subform - with Function (2002)

    (Edited by HansV to provide link to post. Use <!t>[post#=444058]<!/t> to create a link; see <!help=19>Help 19<!/help> for more info.)

    Hello

    I want to create a textbox in the main form, and display the sum of a textbox from the subform similar to <post#=444058>post 444058</post#> (sorry -- I don't know how to create a hyperlink to the message). The difference is that the textbox on the subform is a calculated control using a user-defined Function that references several fields from the subform plus several unbound textboxes from the parent form.

    I created a new, unbound textbox in the Footer of the subform, and set its ControlSource to: = Sum([txtCost1]) , where txtCost1 is the textbox in the subform's Detail section. This resulted in #Error being displayed in the textbox. Then I changed the ControlSource to =Sum(CostPerHour(xxx)), where CostPerHour(xxx) is my user-defined function. This also resulted in #Error being displayed. The CostPerHour function is somewhat complex, and I don't think that I could replicate it in a query.

    FWIW, the full defintion of the ControlSource for textbox that I want to sum is:
    =CostPerHour(Forms!frmRepeatableCosts!txtHr1,Forms !frmRepeatableCosts!txtEstimateAnnualHours,[RepeatInterval],[RepeatCycleIDfk],[PartsOrFixedCost],[labourHours],Forms!frmRepeatableCosts!txtNormalLabour,Forms!fr mRepeatableCosts!txtSpecialLabour,[SpecialistRequired],[DelayBeforeCommence])

    How can I calculate the Sum of all the txtCost1 textboxes on the subform, and return the results to the parent form?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Total a subform - with Function (2002)

    You can only sum the value of expressions involving fields in the record source of the (sub)form. You can't refer to the value of controls in the detail section in such an expression. Can you rewrite the expression in the following form?

    =Sum(CostPerHour([Field1],[Field2],...))

    where Field1, Field2 etc. are fields in the record source of the subform.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total a subform - with Function (2002)

    Thanks for the clarification -- I always get stuck using the Sum() function by referring to the value of controls instead of using the underlying field. Your explanation was spot-on, as usual. I will create new fields in the table to store the results of the function. Not ideal, I know, but it will get past this hurdle, plus allow the results to be crosstab summed in a different part of the application.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Total a subform - with Function (2002)

    Would it be possible to use calculated fields in a query? That would avoid having to store calculated fields in a table. On the other hand, storing them may well improve the speed of the crosstab query.

Posting Permissions

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