Timesheet-bind an unbound text box expression to a table

[Billiam]

A form based on t-TimeSheet has an unbound textbox with an expression to
calculate time worked for that entry. I would like to bind that value to the
underlying form table...but i have read it is wrong to store a calculated
value. I do need to have a historical record of a person's timesheet with
totals. What is the best way to do this?

Simply as a report summary based on a query, say, q_PayPeriodHoursperEmployee?

Thanks for any help!

Billiam

 

[Frank H]

It sounds like you've got the right idea.
Use a calculated field in the query A to get the time worked.
A report would allow you to display each record, and the totals.
And / or, if you need to use those values further, you can use query B with
query A as it's recordsource, to generate totals.

 

[Billiam]

I think most of us newbies think everything needs to be in a table...mayber I
am getting a little wiser...Thanks, Frank, for the help!

Billiam

 

[Arvin Meyer [MVP]]

You really do not need to store the calculated result since you are also
storing all the elements of the calculation. While you can store it, true
relational database normalization rules say "no". If you are not storing the
components of the calculation, and you do need history, it is just fine to
store the result.

 

[Billiam]

Thanks for replying, Arvin. How would I store the value from the unbound
textbox expression? How would I store the calculation...in a table of
calculations?

Thanks,

Billiam

 

[Arvin Meyer [MVP]]

No, if you must store it, and I suggest that you don't, store it in the same
table with the elements of the calculation, so for instance in an
OrderDetails table, you'd store Price, Quantity, and Total. Under some
circumstances, you do not need to store price and quantity, for instance in
a home purchase. Only the final price may need to be stored.

Now as far as putting it in the table, do not unbind the textbox. Instead
bind it to the total field, and use the AfterUpdate of each of the element
fields. It might look something like this:

Private Sub txtPrice_AfterUpdate()
If Len(Me.txtQty & vbNullString) > 0 Then
Me.txtTotal = Me.txtPrice * Me.txtQty
End If
End Sub

Do something similar for txtQty. Understand, of course that if anyone has
access to an updateable queries or your tables, they can create havoc with
the data, so you must insure that can't happen.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Disclaimer: Any code or opinions are offered here as is. Some of that
code has been well tested for number of years. Some of it is untested
"aircode" typed directly into the post. Some may be code from other
authors. Some of the products recommended have been purchased and
used by the author. Others have been furnished by their manufacturers.
Still others have not been personally tested, but have been
recommended by others whom this author respects.

 

[Billiam]

Thank you for the great explanation, Arvin!

Billiam