Changing total on one row in Continous Form
in [Forms]
|
Prev: DLookup with mulitple criteria - error message
Next: Send Appointments from Access 2007 to someone else's Outlook Calen
From: John W. Vinson on 8 Apr 2010 12:18 On Thu, 8 Apr 2010 04:40:01 -0700, Cathy <Cathy(a)discussions.microsoft.com> wrote: > >John, > >The continous form shows one record (row) per section. The total is a >field, per section that needs approval (flag) before it can be updated. I've >tried creating an if statement ( if approval = "Y" then sum(a+b)), but it >keeps updating all records... not just the record that I am on. > >What can I do to have it only update the record that I am on? I'd suggest basing the subform on a Query, and doing the calculation in a calculated field *in the query*, rather than in an unbound textbox on the form. You're seeing the calculation on all the records because the calculation isn't actually being updated anywhere - it's just *DISPLAYED* on the form. And since the textbox is unbound, there's really only one textbox, displayed many times; when you change its value, all of the instances of the textbox show the change. Just edit the form's recordsource query and add a new value in a vacant Field cell such as: RowTotal: IIF([Approval] = "Y", [A] + [B], Null) and display this field on the form. It will be calculated per-row, not repeated. -- John W. Vinson [MVP]
From: Cathy on 8 Apr 2010 15:30 John, Somehow you aren't understanding what I'm trying to acomplish. I must not be doing a very good job at describing it... I need to display the records from a table. The user wants to update a field on the table, and when s/he does, the total on that record needs to update. Only that one record, not all records of a continous form. The form can't be based on a query, because the user won't be able to update the table. Unlike most tables, the business rules on this table is that the user must approve the request before it is totaled. Unapproved records are not supposed to be totaled. Once a total has been completed the total should never be recalculate. This is a control the user wants. I recognize that none of this is best practice. It has been explained to the users, and they insist that this is what they want. So, how can I put a total in a total field on only the one record on a continuous form? Thank you again, "John W. Vinson" wrote: > On Thu, 8 Apr 2010 04:40:01 -0700, Cathy <Cathy(a)discussions.microsoft.com> > wrote: > > > > >John, > > > >The continous form shows one record (row) per section. The total is a > >field, per section that needs approval (flag) before it can be updated. I've > >tried creating an if statement ( if approval = "Y" then sum(a+b)), but it > >keeps updating all records... not just the record that I am on. > > > >What can I do to have it only update the record that I am on? > > I'd suggest basing the subform on a Query, and doing the calculation in a > calculated field *in the query*, rather than in an unbound textbox on the > form. You're seeing the calculation on all the records because the calculation > isn't actually being updated anywhere - it's just *DISPLAYED* on the form. And > since the textbox is unbound, there's really only one textbox, displayed many > times; when you change its value, all of the instances of the textbox show the > change. > > Just edit the form's recordsource query and add a new value in a vacant Field > cell such as: > > RowTotal: IIF([Approval] = "Y", [A] + [B], Null) > > and display this field on the form. It will be calculated per-row, not > repeated. > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 8 Apr 2010 16:30
On Thu, 8 Apr 2010 12:30:01 -0700, Cathy <Cathy(a)discussions.microsoft.com> wrote: >John, > >Somehow you aren't understanding what I'm trying to acomplish. I must not >be doing a very good job at describing it... > >I need to display the records from a table. The user wants to update a >field on the table, and when s/he does, the total on that record needs to >update. Only that one record, not all records of a continous form. The form >can't be based on a query, because the user won't be able to update the >table. Most queries ARE updateable. If you're assuming that basing the form on a Query means that is cannot be updated, well, your assumption is simply incorrect. Try it. > Unlike most tables, the business rules on this table is that the user >must approve the request before it is totaled. Unapproved records are not >supposed to be totaled. Once a total has been completed the total should >never be recalculate. This is a control the user wants. Is the Total a *stored field* in the table or calculated dynamically? If it is a stored field, what provision do you have for data anomalies, such as having A = 2, B = 3 and RowTotal = 2412? >I recognize that none of this is best practice. It has been explained to >the users, and they insist that this is what they want. > >So, how can I put a total in a total field on only the one record on a >continuous form? I'll be able to answer that if you can post the *actual* structure of your table (fieldnames and datatypes) and indicate how the total is being calculated. -- John W. Vinson [MVP] |