Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: SUM records of a subform field

  1. #1
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29

    SUM records of a subform field

    I've created a subform on my mainform by just dragging fields onto it in layout view so it looks like I have a datasheet in the middle of my form.

    I opened the subform in Design View and added a textbox called txtTotalOwing to the Form Footer with the control source set to =Sum([txtAmountOwing]) where txtAmountOwing is the field in the subform that I want to total. In the subform's design view, this field appears as a single textbox.

    I intend to display this Total on my mainform, but before referencing it, I check to see if it works. When I look at the subform in form view, the txtTotalOwing box says #Error.

    I'm not sure what's wrong. I followed the procedure right off of a help forum, and I've discovered other forums with similar suggestions. I don't know where I went wrong.

    I've never worked with subforms before, which is why I described what everything looks and how I made it I'm not even sure if I made the form correctly.

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    252

    secretary -

    Is this field [txtAmountOwing] a calculated field in the subform?

    If so, see -

    http://support.microsoft.com/default.aspx?scid=kb%3Ben%2Dus%3B199355

    Otherwise, check spelling and/or check for missing references in the VBE, under tools > references, see if any are marked as MISSING.
    Hope this helps.

    Jim

  3. #3
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    Hi Jim!

    [txtAmountOwing] is not a calculated field as far as I can tell. It's control source is just a table field [Amount Owing] that does not depend on other fields.

    I've checked and rechecked spelling.

    I've looked in the VBE and I have the following selected:

    - VBA
    - Microsoft Access Object Library
    - OLE Automation
    - Microsoft Office database engine Object Library

    Not sure what might be "missing"...

  4. #4
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    252

    secretary -

    In that case, try -

    =Sum([AmountOwing])

    as the control source.

    Jim

  5. #5
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    If you mean referencing the table [Amount Owing] directly (there is no [AmountOwing] control/field), I can't do that because it contains subform records for every main form record, which means I would sum up all the debt for all the accounts. I just want the total debt for each separate record of the main form.

  6. #6
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    252

    secretary -

    No, I was referring to the field in your subform.

    In your first post, you stated:

    "I've created a subform on my mainform by just dragging fields onto it in layout view so it looks like I have a datasheet in the middle of my form."

    "I opened the subform in Design View and added a textbox called txtTotalOwing to the Form Footer with the control source set to =Sum([txtAmountOwing]) where txtAmountOwing is the field in the subform that I want to total. In the subform's design view, this field appears as a single textbox."

    My thought was, if you dragged and dropped the field "AmountOwing" to the subform from the recordsource and didn't change its name, then the control source of the "txtTotalOwing" field should be =Sum([AmountOwing]), instead of =Sum([txtAmountOwing]).

    Jim

  7. #7
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    Oh I see.

    Yes the subform fields had at one point the same names as the table fields because of how I built the subform, but I changed them.

    [txtAmountOwing] is now the name of the textbox I want to sum up.

    :/

  8. #8
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    252

    secretary -

    So, I assume this -

    =Sum([txtAmountOwing]), in the control source of the "txtTotalOwing", still shows #Error?

    And, the control "txtTotalOwing" is in the form footer of the SUBFORM?
    And, the control "txtAmountOwing" is not a calculated field?
    And, in the control's properties, the Name is txtAmountOwing and the control source is AmountOwing?

    Let me know if all of the above are correct?

    Thanks,

    Jim

  9. #9
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    Yup.
    That's all true.

  10. #10
    rpeare is online now VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,253
    on your main form if you have a text box with the following it should reflect the total of the subform


    =forms!MainFormName.SubFormName!txtTotalOwing

    Where mainformname and subformname are the names of your parent (main) form and child form (subform) respectively.

  11. #11
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    =[Forms]![LevyEntry].[subLevies]![txtTotalOwing]

    It certainly reflects the total of the subform.
    And that total is #Error XD

  12. #12
    rpeare is online now VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,253
    you usually see that message when you're trying to perform a mathematical function on a text field or your expression (formula) is invalid. Any chance you could post a sample database with enough fake data to re-create the error.

  13. #13
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    At the moment, I think my issue with the SUM may have something to do with the fact that I can't properly link the subform to the main form.

    The controls on the main form (frmLevies) aren't even picking up data from the MembersQuery properly, and that is nothing but a simple use of the ControlSource property, and I have NO idea how I could have POSSIBLY gotten that wrong.
    Attached Files Attached Files

  14. #14
    rpeare is online now VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,253
    Here's your database back.

    You had the control sources of your fields as a formula rather than as an actual field from the query you were using as a data source for the form.

    You also were not summing the subform amount field correctly.
    Attached Files Attached Files

  15. #15
    secretary is offline Novice
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    29
    Thanks. That helped a lot. I see where I went wrong.

    However, it's still not summing the entire Amount Owing field; I'm only getting the value of the first record in the SUM control.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-15-2011, 12:58 PM
  2. Replies: 7
    Last Post: 01-12-2011, 06:59 AM
  3. Replies: 9
    Last Post: 12-15-2010, 11:44 AM
  4. Replies: 1
    Last Post: 11-13-2010, 10:57 AM
  5. Replies: 3
    Last Post: 11-05-2010, 02:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums