none
Fix calculating result accroding to coresponding record RRS feed

  • Question

  • Hi All. I create one subform with record source from one selected query to sum same items in table. On this subform I create one textbox name is Delta to calculate base on Sum of quantity. The problem is the result of Textbox is not followed according to responding each record. it only is right for first record. the second record is right when i click mouse on it.

    Here is picture:

    And the second result only change when click on it

    The rule of calculation in VBA code like this: Me.Delta = Iff([BoxType] = "KLT",480, 360) - [SumOfQuantity]

    For above instance, i set is 480. How to display the right result according to each record. Thank you in advance



    • Edited by Nghi Trinh Thursday, May 31, 2018 3:50 AM Insert pic
    Thursday, May 31, 2018 3:46 AM

Answers

  • Don't you simply need to pull, not push the value into the control by setting the ControlSource property of the Delta control to:

    = Iff([BoxType] = "KLT",480, 360) - [SumOfQuantity]

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Friday, June 1, 2018 1:23 AM
    Thursday, May 31, 2018 10:28 PM

All replies

  • Your Delta field is unbound.

    An unbound field, in a continuous form style, is a single common field for the entire record set - not per record.  This is why the behavior seems strange.

    Use a field that is bound to the record set.

    Thursday, May 31, 2018 11:35 AM
  • Dear Sir

    But the sum is calculated from one query not from table. How I can use a field that is bound to the record set. The result only is right after we have sum of quantity from query. 

    Thursday, May 31, 2018 3:34 PM
  • hmmmm well in your syntax example you provided it was for a form:

    Me.Delta = Iff([BoxType] = "KLT",480, 360) - [SumOfQuantity]

    the me.delta is a form control syntax

    if the calculation is in a query then one would use a calculated field syntax i.e.

    Delta: Iff([BoxType] = "KLT",480, 360) - [SumOfQuantity]

    then the calculation, Delta, exists in the record set

    Thursday, May 31, 2018 3:39 PM
  • I already try the calculation in query as your mention. the result is not my expectation. It can not sum. Just single row. do you other solution
    Thursday, May 31, 2018 4:13 PM
  • as you note; the standard query calculated field is within the row.

    If you seek a running sum; one should consider using a report object - as this ability is more to a report's feature set.  You can bing this topic on a Running Sum in a Report.

    otherwise one must go into more advanced query design involving a Module:

    http://support.microsoft.com/?kbid=205183

    if you do not seek a running sum; then I am confused and

    would ask that you provide a clear example of the math

    Thursday, May 31, 2018 5:11 PM
  • Don't you simply need to pull, not push the value into the control by setting the ControlSource property of the Delta control to:

    = Iff([BoxType] = "KLT",480, 360) - [SumOfQuantity]

    Ken Sheridan, Stafford, England

    • Marked as answer by Nghi Trinh Friday, June 1, 2018 1:23 AM
    Thursday, May 31, 2018 10:28 PM
  • Well done. Thank you so much. it is right simply. Just add above formula in Delta's controlSource. 
    Friday, June 1, 2018 1:27 AM