Changing total on one row in Continous Form

[Cathy]

In Access 2007, I have a continous form, and I only want to update the row
total of the record I am on. How do I update only the one row total, and not
all row totals?

Thank you,

 

[John W. Vinson]

In Access 2007, I have a continous form, and I only want to update the row
total of the record I am on. How do I update only the one row total, and not
all row totals?

Thank you,

What is a "row total"? That's not an Access construct; it would seem to be
something you've added. And if it's a calculated field based on values in the
record, what do you mean by wanting to update it? Do you mean that you want to
have 2 + 2 equal to 4, except sometimes you want to have it equal to 13
instead???

 

[Cathy]

John,

I know this sounds unusual. Yes it is a created total on the row. They
want the amount to update only if approved. They do not want all rows to
update. Just the one they are approving, when they are approving it.

How do I go about doing this?

Thank you,
Cathy

 

[John W. Vinson]

John,

I know this sounds unusual. Yes it is a created total on the row. They
want the amount to update only if approved. They do not want all rows to
update. Just the one they are approving, when they are approving it.

How do I go about doing this?

I don't know, because I still have no idea what the "total row" is or how
you're calculating the total, nor what the "update" might involve. More info
please? What's the control source of the "created total" control?

 

[Cathy]

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?

Thank you again,

Cathy

 

[John W. Vinson]

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] + , Null)

and display this field on the form. It will be calculated per-row, not
repeated.

 

[Cathy]

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,

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] + , Null)

and display this field on the form. It will be calculated per-row, not
repeated.

 

[John W. Vinson]

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.