Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SubTotal/total on Form/subfom (2000)

    I have a Form/subform setup.
    The SUBFORM has: textbox [ExpAmt] in the Details area
    textbox [txtSumOfPage] unbound with Control Source formula: =Sum([ExpAmt])

    When I run the FORM (frmOrders), and cycle thru each Order, the [txtSumOfPage] box shows the SUB TOTAL for that PAGE.
    This works ok.

    Now, what I need is a box that will give me the TOTAL for ALL ORDERS. How do I do this? thanks

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

    Re: SubTotal/total on Form/subfom (2000)

    What you could do is setup a text box with the following:
    =DSum("Amount","yourTablename")
    where Amount is a field in yourTablename, so substitute Amount and yourTablename for your table name and fieldname.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubTotal/total on Form/subfom (2000)

    ok
    I now have the Account Total textbox giving me the total of all ExpAmt items in the subform.

    It works correctly on opening the form, but does not update automatically (like the sub-total box does) when I add or enter new data in a subform Exp Amt.

    I tried a requery statement but I don't have the correct syntax to make it work.

    If you could help me with this, I'd greatly appreciate it.
    I'm attaching a zip copy of the approriate tables, queries and forms

    Thanks
    Paul
    Attached Files Attached Files

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

    Re: SubTotal/total on Form/subfom (2000)

    Pat is offline at the moment, so I'll fill in for him.

    1. Set the Control Source of the Account Total text box to =DSum("ExpAmt","SUB_OBJCODES").
    2. You don't need the TOTAMT field any more, so you can set the Record Source of the main form to OBJCODES, and you can remove the query.
    3. To make the total update when data are added or modified in the ExpAmt text box on the subform, put code in the After Update event of this text box:

    Private Sub ExpAmt_AfterUpdate()
    Me.Parent.Recalc
    End Sub

    This code recalculates all calculated fields on the main form (the parent of the subform.)

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SubTotal/total on Form/subfom (2000)

    Thanks for the help. I have implemented your suggestion and it is working great.

    I'm attaching a revised sample to help with the following questions

    Next step I need help with:
    When I populated the SUB_OBJCODES table, I left out number 52, then added it at the end. Now when I open the sfrmSUB_OBJCODES, the ExpID field is sorted properly, but NOT when I open the frmOBJCODES. I think I need to do a sort procedure on the ExpID field and attach it to the OnLoad event, but I haven't figured out how to code it.

    More complex issue:
    I now want to make the frmOBJCODES into a SUBFORM nested inside a new form called frmACCOUNTS based on the ACCOUNTS table. Eventually I will have 80-100 Accounts. I tried making an intermediate query that joins the two tables, but was rejected for lack of a relationship. Do I need to add the AcntID field to the OBJCODES table? I think this is a many-to-many relationship: Each AcntID will have access to a full set of OBJCODES (30-90) and each ObjCodeID can be used by all Accounts. Not sure how to accomplish setting this up.

    thanks again
    Paul
    Attached Files Attached Files

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

    Re: SubTotal/total on Form/subfom (2000)

    First question: create a query based on SUB_OBJECTCODES. Add all the fields you need, and sort on ExpID (Ascending). Save the query as (say) qrySUB_OBJECTCODES. Set the Record Source property of sfrmSUB_OBJCODES to the name of this query.

    Second question: if one account can have several object codes, and one object code can be used by several accounts, you have a many-to-many relationship indeed. Many-to-many relationships are implemented by creating an intermediary table tblACCOUNT_OBJCODES. This table contains at least two fields: AcntID linked to the ACCOUNTS table, and ObjCodeID linked to the OBJCODES table. Each record in this table will be a unique combination of an AcntID and an ObjCodeID. The primary key of the intermediary table is on the combination of these fields. If you need to store other information that is specific to the combination of AcntID and ObjCodeID, you can add fields to tblACCOUNT_OBJCODES.
    Attached Images Attached Images
    • File Type: png x.png (5.4 KB, 0 views)

Posting Permissions

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