* Saving Calculated Control Source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have setup a field on a form to do a calculation in the control source of
a field. Now I need to have that data saved to the correct field in the DB.
Could you help please.

Thanks
 
If you menage to create the calculation in the form, you can always take this
formula as another field in a query, and display that desire value.
It is not recomended to save a calculated field, you'll have to maintain it,
and it cause problem (user update the table through the table and not through
the form).
So best way will be to display this field in a query, and it will always
display the right value.

Select TableName.* , [Calculation] as ExtraField From TableName
 
Hi Ofer

Actually I am forcing the users to use the form to update the table, they
only see the form. So no problems there. I do need the form to save that
data. I'd appreciate help with that.

Thanks
--
Alex


Ofer said:
If you menage to create the calculation in the form, you can always take this
formula as another field in a query, and display that desire value.
It is not recomended to save a calculated field, you'll have to maintain it,
and it cause problem (user update the table through the table and not through
the form).
So best way will be to display this field in a query, and it will always
display the right value.

Select TableName.* , [Calculation] as ExtraField From TableName
--
I hope that helped
Good luck


Alex said:
Hi

I have setup a field on a form to do a calculation in the control source of
a field. Now I need to have that data saved to the correct field in the DB.
Could you help please.

Thanks
Click to expand...
Click to expand...
 
If the you want to save the value in the table that the form is bounded to,
then create a field in the form, not visible, bound it to that field.
On the before update event of the form, you can write the code
Me.FieldName = Me.CalculatedFieldName

If the field that you want to update, is in a seprate table from the form,
then you can use update query, to help you with this I need to know more
details how do you know which record you are updating (the key that join the
two tables), and the field type

--
I hope that helped
Good luck


Alex said:
Hi Ofer

Actually I am forcing the users to use the form to update the table, they
only see the form. So no problems there. I do need the form to save that
data. I'd appreciate help with that.

Thanks
--
Alex


Ofer said:
If you menage to create the calculation in the form, you can always take this
formula as another field in a query, and display that desire value.
It is not recomended to save a calculated field, you'll have to maintain it,
and it cause problem (user update the table through the table and not through
the form).
So best way will be to display this field in a query, and it will always
display the right value.

Select TableName.* , [Calculation] as ExtraField From TableName
--
I hope that helped
Good luck


Alex said:
Hi

I have setup a field on a form to do a calculation in the control source of
a field. Now I need to have that data saved to the correct field in the DB.
Could you help please.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Alex said:
Hi Ofer

Actually I am forcing the users to use the form to update the table,
they only see the form. So no problems there. I do need the form to
save that data. I'd appreciate help with that.
Click to expand...

Actually you don't.

When you work in Excel and you want cell [C1] to contain the product of the
values in [A1] and [B1] do you put the expression =[A1]*[B1] into [C1] or do
you create a macro that does the calculation and stores the result into
[C1]?

Put your expression into a query based on the table and then use the query
instead of the table. It will "feel" the same as a saved value in the table
(honest).
 
Alex,
You don't really need it, you just want it. As Ofer stated, it violates all
kinds of database design and normalization rules to carry a calculated value
in a database. The only exception to that is if the value will be calculated
one time when a new record is created, then that value will always be valid.
The usual example of why not to carry calculated values is a person's age.
This is a value that changes periodically. Once a person has a birthday
after the data is entered, the value in the field is now incorrect.
If you worked in a large IT shop and presented that design to a DBA, he
would have unkind things to say to you.

Alex said:
Hi Ofer

Actually I am forcing the users to use the form to update the table, they
only see the form. So no problems there. I do need the form to save that
data. I'd appreciate help with that.

Thanks
--
Alex


Ofer said:
If you menage to create the calculation in the form, you can always take this
formula as another field in a query, and display that desire value.
It is not recomended to save a calculated field, you'll have to maintain it,
and it cause problem (user update the table through the table and not through
the form).
So best way will be to display this field in a query, and it will always
display the right value.

Select TableName.* , [Calculation] as ExtraField From TableName
--
I hope that helped
Good luck


Alex said:
Hi

I have setup a field on a form to do a calculation in the control source of
a field. Now I need to have that data saved to the correct field in the DB.
Could you help please.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
But doesn't it depend on the nature of the value being collected and the
justification for storing the value (eg a person's age versus an account
balance)? A prime example would be a table that stores account balance
history by date for audit purposes. The same information could be
determined by the underlying detail records (debits/credits), but if a
debit or credit is changed, added or deleted there might not be other
proof that the information was altered.

David H
Alex,
You don't really need it, you just want it. As Ofer stated, it violates all
kinds of database design and normalization rules to carry a calculated value
in a database. The only exception to that is if the value will be calculated
one time when a new record is created, then that value will always be valid.
The usual example of why not to carry calculated values is a person's age.
This is a value that changes periodically. Once a person has a birthday
after the data is entered, the value in the field is now incorrect.
If you worked in a large IT shop and presented that design to a DBA, he
would have unkind things to say to you.

:

Hi Ofer

Actually I am forcing the users to use the form to update the table, they
only see the form. So no problems there. I do need the form to save that
data. I'd appreciate help with that.

Thanks
--
Alex


:

If you menage to create the calculation in the form, you can always take this
formula as another field in a query, and display that desire value.
It is not recomended to save a calculated field, you'll have to maintain it,
and it cause problem (user update the table through the table and not through
the form).
So best way will be to display this field in a query, and it will always
display the right value.

Select TableName.* , [Calculation] as ExtraField From TableName
--
I hope that helped
Good luck


:


Hi

I have setup a field on a form to do a calculation in the control source of
a field. Now I need to have that data saved to the correct field in the DB.
Could you help please.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
A prime example would be a table that stores account balance
history by date for audit purposes. The same information could be
determined by the underlying detail records (debits/credits),
Click to expand...

In that case storing the total is not a good idea because it risks
disagreeing with the source data: this is the reason for not storing
dependent data.
but if a
debit or credit is changed, added or deleted there might not be other
proof that the information was altered.
Click to expand...

In this situation, the total cannot be recalculated from the source data so
it's not dependent.

B Wishes


Tim F
 
You missed the comment about the AUDIT purposes. The whole reason behind
storing the value (balance by date) would be to validate that the
underlying detail records have not been altered or modified which might
indicate fraud or a system issue. Given the requirments of SOX, an
internal control like that is entirely probable.
 
OK guys

Thanks for all the info. Now although I understand your points here is the
deal:

Form1 contains field AccruedVacation, VacationTaken, VacationLeft (all
disabled on the form)
Subform1 contains DaysTaken and the dates. also contains "invisible" field
TotalVacTaken that calculates the # of days taken (through expression
=IIf(Sum([TotalVacTaken])<>" ",Sum([ToalVacTaken]),0))

To enter Vacation you enter in subform1. once that is done the calc is done
and is distributed into VacationTaken and VacationLeft. (through expression
control Source=[EmployeeVacations subform].[Form]![TotVacTaken])

As you can see the fields are dynamic depending on the vacation taken.

Now how can I get this done so that the info calculated is saved into
VacationTaken and VacationLeft?

Any programming ideas are appreciated

thanks
 
Back
Top