Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum of values in subform (Access 2000)

    I have a textbox in the mail form which is used to hold the sum of a column from my subform. For some reason, I cannot get the sum to work. My Textbox controlsource is:
    =Sum([Forms].[FRM_GetProductByKit].[FRM_GetProductByKitSubForm].[Form]![ConCost])

    Any ideas would be great

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Sum of values in subform (Access 2000)

    It appears to me your syntax isn't correct. In particular, the reference should include the subform control on the main form as a part of the expression, and you shouldn't normally need the second [Form]. I would also use the Exclamation character instead of the dot. It would also help to know what does show up in the text box on the main form when you open the form.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    I have tried several combinations, but I keep getting the #error displayed.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum of values in subform (Access 2000)

    What Wendell was suggesting was that you sum the value on the subform, not on the parent form. On the main form, put a control that references the subform control containing the summed value.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    OK, that works great, except that it is alot slower now. Its like it recaculates the sum everytime a product is added to the subform. Is there a way to just calculate the sum after all the products are in the subform

    Thanks a bunch

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

    Re: Sum of values in subform (Access 2000)

    The problem there is how Access can determine when all products have been entered.

    You could put a text box on the main form with a control source of the form =DSum("ConCost", "<table name>", "<link field>=" & <link >) where <table name> must be replaced by the name of the table containing the ConCost field, and <link field> by the name of the field linking the main form and subform. If any of these names contain spaces, enclose them in square brackets.

    The DSum expression won't be updated when you add or modify records in the subform, only when you move to another record on the main form, or when you explicitly do a recalculation.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    That sound cool, but it does not seem to work for me. What I have it this

    =Dsum("ConCost","FRM_GetProductByKitSubform", "") but it says it cannot find the subform name. I have checked the name several times. Also, there is no linking between the main form and the subform.

    Any ideas

    Thanks

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

    Re: Sum of values in subform (Access 2000)

    You shouldn't use the name of a form, but of the table that holds the ConCost field. If the subform is not linked to the main form, you can omit the third argument to DSum: =DSum("ConCost", "[name of the table]")

  9. #9
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    The Concost field is not from a table. The subform is based on a query, and the Conconst is the calculation of 2 fields. Is the Dsum still possable. I know I could use using a seperate query to sum everything, but the way the Access rounds, (bankers rounding) I cannot do that. It thows my caulations off by a few cents. I have to use my rounding function instead.

    Any ideas?

    Thanks

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

    Re: Sum of values in subform (Access 2000)

    If ConCost is calculated on the subform, you will have to mimic the calculation in the DSum expression:

    =DSum("expression to calculate ConCost", "name of query")

    or use your custom rounding function in the query to make ConCost a field in the query.

  11. #11
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    Can you reference a function in a querydef? In my case, the function is called "MyRound(Arg1)"

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

    Re: Sum of values in subform (Access 2000)

    Yes, you can use custom functions in queries. Instead of dragging a field from the field list, enter the expression directly into the Field: box in an empty column, in the following form:

    ConCost: MyRound(...)

    Replace the dots ... by the argument you want to call MyRound with. ConCost becomes the column header.

  13. #13
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    Awesome, thanks a bunch!

  14. #14
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    ACtaully Hans, when I use my function in the QueryDef, it is increditably slow. My function basically does arithmetic rounding

    Any ideas?

    Public Function MyRound(dblNumber As Double)

    Dim varRoundAmount As Double
    Dim dblTemp As Double
    Dim lngTemp As Long
    Dim RoundTemp As Double

    varRoundAmount = 0.01
    dblTemp = dblNumber / varRoundAmount
    lngTemp = CLng(dblTemp)

    If lngTemp = dblTemp Then
    MyRound = dblNumber
    Else
    RoundTemp = (dblTemp - lngTemp)
    If RoundTemp < 0.49999 Then
    'If IsMissing(varUp) Then
    ' round down
    dblTemp = lngTemp
    Else
    ' round up
    dblTemp = lngTemp + 1
    End If
    MyRound = dblTemp * varRoundAmount
    End If
    End Function

  15. #15
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum of values in subform (Access 2000)

    OK, I know is definitly crappy function that slows it down to a halt. Basically, if I also want 2 decimal places in my value and if it is something like 12.125, then I want to round it up (12.13). The current round function in Access would round that to 12.12

    I really need a solution for this [img]/forums/images/smilies/sad.gif[/img]

    Thanks

Page 1 of 3 123 LastLast

Posting Permissions

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