Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    SUM on a subform (XP)

    I have a form to display information about our employees (students) and the courses they have completed in our learning labs. The form has two tabs: The first displays general information about the student; the second displays a subform with the student's course completions. The subform gets its data from a query (qryStudentCourses) and includes a field (Credit) for the clock-hour credit the student gets for a given course.

    I'm trying to add a calculated text box to the second tab, just below the subform, to display the sum of the hours the student has earned. First I added an unbound text box to the tab; then I used the Expression Builder to build the following expression:

    =Sum([qryStudentCourses]![Credit])

    That is, I want to total the hours from the Credit field of the qryStudentCourses query. Even though I used the Expression Builder, the text box displays "#Error" in the text box. What am I overlooking?

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

    Re: SUM on a subform (XP)

    Hi Lucas,

    You can't refer to a query (or table) that way. Instead, put a text box in the header or footer of the subform, with control source =Sum([Credit]). This text box may be hidden. Name it (for example) txtSumCredit.

    Set the control source of the text box on the main form to =[SubformControl]![txtSumCredit] where SubformControl is the name of the subform as a control on the main form.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    Hello, Hans, and thanks for the quick reply.

    Something still ain't right. I used your example and made the control source =Sum([Credit]), but I still get "#Error." I've done this kind of thing before and it worked

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

    Re: SUM on a subform (XP)

    Lucas,

    Putting the text box with txtSumCredit with control source =Sum([Credit]) anywhere on the main form won't work, since Credit is not part of the main form, You should create it on the subform. If the subform doesn't have a form header or footer, put it in the detail section. Set its Visible property to No, so that it will be hidden - the user doesn't need to see it. You can still refer to txtSumCredit on the main form, even if it is invisible.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    Hans,

    I failed to mention that my subform displays as a datasheet, and datasheets don't show a header or footer, eh? I've added a screen shot of the form so you can see what I'm trying to do. The datasheet serves my purpose perfectly, but...any suggestions?
    Attached Files Attached Files

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

    Re: SUM on a subform (XP)

    Please read my previous reply.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    Hello again, Hans: The light came on about a day after I read your last response, and after many distractions I've had opportunity to work on this again. I took your advice and added an unbound text box (txtSumCredit) to the footer of the subform. Then

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

    Re: SUM on a subform (XP)

    Lucas,

    You shouldn't use Forms!fsubStudentCourses. This notation only works for forms that are open as main form. A subform is not open in that sense.

    In the notation =[fsubStudentCourses]![txtSumCredit], you must use the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To check this, open the main form in design view, then click once on the subform. Take care not to click a second time on the subform, because you will select something in the subform then. If you click a second time by accident, click on the main form outside the subform, then click the subform again. The name of the subform as a control will be in the caption of the Properties window, and in the Object dropdown list below it, and also in the Object dropdown list in the Formatting toolbar. This is the name you must use.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    OK...when I click once on the subform, the name that appears in all three places you mentioned is "Child52." So I re-wrote the control source for the unbound text box in question to read as follows:
    =[Child52]![txtSumCredit]

    and, as I live and breathe, I got #Error again! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

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

    Re: SUM on a subform (XP)

    Try =[Child52].[Form]![txtSumCredit] and if that doesn't work, could you post a stripped down version of your database? You know the routine: remove all non-relevant database objects, remove all but a few records, modify data to protect the guilty, compact and zip.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    OK, Hans, I tried it...same results. Here's a stripped-n-zipped copy of my database. Thanks a million for looking at this!
    Attached Files Attached Files

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

    Re: SUM on a subform (XP)

    Lucas,

    The Source Object of your subform control is NOT the fsubStudentCourses form, but the qryStudentcourses query. This query, of course, does not contain a text box named txtSumCredit, hence the error.

    Solution: change the Source Object property of the subform control to fsubStudentCourses.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: SUM on a subform (XP)

    Yea! It worketh! Bedankt! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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