storing calculated fields on a table 4

[kaniz]

I have a form with a calculated field. Under the control source of one the fields of the forms I have a formula. Can the results I see on the form be stored on a the table. Maybe this is not the best design but my boss wants to see the results on the form and the table. any suggestions, help please.

thank you

 

[Rohdem]

Could you create a field in the table to store the data and set it to the control source of a text box, then make the default value of the text box your calculation?
Mike Rohde
rohdem@marshallengines.com

 

[Calator]

Rohdem,
I am not sure, but I think this may only work when record is FIRST inserted as the DEFAULT property only applies to that situation; it may be required that you also code for the situations where the record is UPDATED(changed) using the appropriate event

 

[kaniz]

How do you set it the control source of a text box. I am sorry I am confuse.

 

[Calator]

kaniz,

I believe Rodhem is saying that in the Properties window of the text control you need to enter the following:

Name: txtbox control name (the name of the control whatever you wish it to be)
Control Source: name of table field that you created to store the value
Default Value: the expression representing your calculation

I hope that helps

 

[villica]

I have already tried this but it does not store the value int the table
My texbox name is total shift and under the control source I have the following calculation. =DateDiff("n",[beginingshift],[endingshift]). I have also moved and put it under default value but it does not work. I appreciate all your help. Can you think of anything else that I cant try.

 

[Quehay]

Set [tt]ControlSource = Total Shift[/tt]. I presume the that the [tt]Beginningshift[/tt] & [tt]EndingShiftValues[/tt] will be entered rather than defaults.

In the txtEndShift (substitute your name) AfterUpdate event

put your calculation in place (this is cryptic--too show the idea not give copy & paste code as there may be typos/ slight format errors since I'm not in code window):

[tt]
Txt_AfterUpDate()
Dim dtmBegDate as Date, dtmEndDate as Date

'No test for "" since data type is date
If isnull (txtBegshif)]or isnull (txtEndShift) then
MsgBox "Enter Beg & End Dates, etc"
Goto ExitLabel
End if

dtmBegDate = CDate(txtBegShift)
dtmEndDate = Cdate(txtEndShift
txtTotalShift = Cstr(DateDiff("n",dtmBegDate, dtmEndDate).

End Sub [/tt]

 

[Calator]

Vilica, you are right. Try this:
- add 2 text boxes to form:
1st text box: it will perfor calculation and display the result
Control Name: txtCalc
Source: = your calculation expression

2nd text box: is the control associated with the table field
Control Name: txtTblField
Source: the name of the table field
Visible: No (hide the field) you can leave Yes while testing

and: in the BeforeUpdate event of the Form use CodeBuilder and enter:
txtTblField = txtCalc 'copy value from calculated control

Hopefully this updates the field for both inserts and changes to the table record

 

[Rohdem]

Looks like Calator and Quehay pretty much have this one licked!!
Mike Rohde
rohdem@marshallengines.com

 

[scking]

I may be a late entry into this discussion but your original statement that calculated fields should not be stored was right on the money. The other treads to the discussion may be interesting but unnecessary. You only need a query with a column added that calls a function to display the calculated field from the query. This will allow anyone to view the calculated columns as a table but the calculations will be performed as the query is opened and the information will not take up any space or bend any of the rules of database normalization.

Steve King