locked
Child record rollup sum to parent field. RRS feed

  • Question

  • Yet another dilemma.  Using Access 2007 I have a datasheet subform bound to a table that has a child table with a

    one-to-many relationship.  As I enter the child records in a datasheet view subform, how can I rollup the sum of the TotRmRate of the child records into the AVTotal textbox control in the parent subform?

     

    Any suggestions are greatly appreciated.

     

    If you know how I can attach a screen shot pleases advise.

    Thanks

    • Edited by DriveEV Monday, September 27, 2010 7:29 PM forgot text
    Monday, September 27, 2010 7:26 PM

Answers

  • "h2fcell" wrote in message
    news:72fa2ff9-3a7f-461d-9acd-2f467d19641a@communitybridge.codeplex.com...
    > Yet another dilemma.  Using Access 2007 I have a datasheet subform bound
    > to a table that has a child table with a
    >
    > one-to-many relationship.  As I enter the child records in a datasheet
    > view subform, how can I rollup the sum of the TotRmRate of the child
    > records into the AVTotal textbox control in the parent subform?
     Alternatively, you can put a text box named (for example) "txtSumTotRmRate"
    in the form footer of the datasheet subform, set its ControlSource to
        =Sum([TotRmRate])
     
    and set the ControlSource of AVTotal (on the main form) to
        =[YourSubformName].Form!txtSumTotRmRate
     --
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
     
    (please reply to the newsgroup)
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by DriveEV Wednesday, September 29, 2010 12:50 PM
    Monday, September 27, 2010 9:45 PM

All replies

  • You can use the AfterUpdate event of the subform to update the total into the parent textbox.

    Code will be something like this

    Me.Parent.AVTotal = Nz(Me.TotRmRate,0)

     


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    • Marked as answer by DriveEV Wednesday, September 29, 2010 12:50 PM
    • Unmarked as answer by DriveEV Wednesday, September 29, 2010 12:50 PM
    Monday, September 27, 2010 9:24 PM
  • "h2fcell" wrote in message
    news:72fa2ff9-3a7f-461d-9acd-2f467d19641a@communitybridge.codeplex.com...
    > Yet another dilemma.  Using Access 2007 I have a datasheet subform bound
    > to a table that has a child table with a
    >
    > one-to-many relationship.  As I enter the child records in a datasheet
    > view subform, how can I rollup the sum of the TotRmRate of the child
    > records into the AVTotal textbox control in the parent subform?
     Alternatively, you can put a text box named (for example) "txtSumTotRmRate"
    in the form footer of the datasheet subform, set its ControlSource to
        =Sum([TotRmRate])
     
    and set the ControlSource of AVTotal (on the main form) to
        =[YourSubformName].Form!txtSumTotRmRate
     --
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
     
    (please reply to the newsgroup)
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by DriveEV Wednesday, September 29, 2010 12:50 PM
    Monday, September 27, 2010 9:45 PM