I wish to copy, or store, calculated form field values in a table

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

Guest

Instead of using a table value to fill a form field value, I wish to use a
calculated Form field to create a value in a table.
 
As posted all the time, you should almost never store a calculated value.
It is slower to read the value from disk than to calculate it. And, it
leads to problems. What if one of the dependant fields changes? How will
you ensure that all the calculated values get updated?

For more details, read the hundreds of previous posts on the topic.
 
The answer is - Don't do it.
It is bad database design to store calculated values in table fields. Just
calculate the value when you need to see it. On a form, you can use the
Current event to present the calculated value for existing records. Where
you do the calculation for new records depends on how you navigate through
your form.

What I usually do is write a function or sub that does the calculation, then
call it from the current event and from the After Update event of the
controls involved in the calculation. I check for Null values before I do
the calculation because all the data necessary to perform the calculation may
not be available. In the example below, Total Price is Order Qty * Units
Ordered:

Sub CalcTotPrice()
If IsNull(Me.txtOrderQty) Or IsNull(Me.txtUnitsOrdered) Then
Me.txtTotPrice = 0
Else
Me.txtTotPrice = Me.txtOrderQty * Me.txtUnitsOrdered)
End IF
End Sub
 
Klatuu,
Since I am new to Access let me explain what I am attempting to do:
I have a maintenance program that provides the need for a report be
generated for various machines. This maintenance is done based upon certian
criteria such as machine type, use, and calculatedform date installed. I have
all of the scheduled dates required displaying correctly on the form, but
cannot print a report such as "what service is due this week" to provide
maintenance personnel. It would be easy if all intervals were the same or
based upon less than 4 variable criteria. Using calculated form field, I have
all of the vrious criteria taken into account but cannot sort or print a
report based upon this calcualted field for the entire table.

I hope this makes sense.

Daveh
 
I understand your problem; however, you are incorrect. You can do all the
things you want based on the value in the control on the form without saving
it to a table. This is a good opportunity to learn some more tricks in
Access. You can reference the value on the form from the report. The same
would be true for any queries or sorting you want to do. The basic syntax to
reference the value is:
=Forms!YourFormName!YourTextBoxName
How you use it will depend on where you use it.
If you can provide a specific problem, perhaps we can show you how to solve
it and it will give you some insight to move forward.
 
Thanks for the input. I got it working by redoing everything that was done to
the calculated Form field within a query, creating calculating fields. Once
it was in a query, it was simple to create the report. In other programs this
was done directly, but I think I have a better grip of the flow of Access now.
Thanks again,
Daveh
 
Glad I could help. I think you will do well with Access. You will accept
input from others and use it to your advantage. You don't know how many
times posters will doggedly defend a bad design and insist they are doing the
right thing.
 
Back
Top