Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calc Total from subform (2000)

    I have attached a screen shot of my form (frmWeek1) I am working with. At the bottom, I want to add up all the integers entered in the subform (frmEntrySub). For example, the Monday colum consists of txtMR, txtMO, txtMR, txtMO, ... I want all the txtMR in the subform to be added together and displayed at the bottom of the parent form in the textbox (txtMRSum). I know that I don't want to store the sum in the table. I tried to have a invisible textbox on the subform calculate the Sum of all the txtMR textboxes, then have that textbox displayed on the parent form. However, since the subform is set to continuous, it messed up the layout. Any suggestions are greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Calc Total from subform (2000)

    I don't understand why you can't just put the totals in the subform's footer?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    You should add textboxes in the footer of the subform to calculate the totals. Control source would be =Sum(txtMR).
    In the main form use as control source for the text boxes :
    = SubFormControlName.Form!NameOfTotalTexbox
    Francois

  4. #4
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    Thanks guys, I totally forgot about the footer. I will try that.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    I tried what you said, but I get #Error in the text box on both forms. I think it is because some of the txtMR boxes have null in them. Will that mess up the Sum(txtMR) calculation? Not all txtMR boxes will have a value. Sorry I forgot to tell you that.

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

    Re: Calc Total from subform (2000)

    I assume that the text box txtMR is bound to a field, say MR. You should use the name of this field in the control source of the text box in the report footer of the subform instead of txtMR:

    =Sum([MR])

    (substitute the correct field name). Similar for the other text boxes.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    Try
    =Sum(Nz(txtMR,0))
    Francois

  8. #8
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    Now this is weird...
    If I open the subform, I take the NZ part off the control and it works. I get a message saying the method you tried to invoke on an object faild, but it still displays a total in the textbox. Then when I open the main form, I get #error in the textbox. Then if I modify the control source of txtMRSum (while in the main form) adding the NZ part, I get the same message but it works. But if I close the form and reopen it, I get #error in the textboxes again. Any ideas?

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

    Re: Calc Total from subform (2000)

    Could you post a stripped-down copy of the database? That would enable Loungers to look at the problem directly. See <post#=401925>post 401925</post#> for instructions.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    Hans,

    Never seen this. I had just the same behavior of <!profile=threeieng11774>threeieng11774<!/profile>.
    Deleting and recreate the textbox in the footer section and the problem was gone. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Francois

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

    Re: Calc Total from subform (2000)

    The same has worked for me in slightly different circumstances. Apparently, sometimes Access gets so confused about a control that deleting and recreating it is the only solution.

  12. #12
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calc Total from subform (2000)

    I deleted the control and added it and now it works consistently. The only problem is it doesn't perform the sum right after I leave the text box. It is almost like I have to give the focus to three or four other controls before the sum will update. Is there something I can do for this or will I have to live it?

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

    Re: Calc Total from subform (2000)

    You can recalculate the subform in the After Update event of each of the text boxes txtMR etc.:

    Private Sub txtMR_AfterUpdate()
    Me.Recalc
    End Sub

    This should force Access to update the totals whenever a value is edited.

Posting Permissions

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