The results of a formula in a form to show in a table

J

Jerrry D

In a form named "Orders" I have a text box named "Total" with this formula in
it =[SubtotalB]-[Prepayment] . How do I see the results of this formula in a
table named "Orders"?

Thanks for any suggestions.
 
A

Arvin Meyer [MVP]

Typically, you do not store the results of a calculation in a table. It
breaks 3NF. There are certain circumstances when it is permissible:

1. History needs to be maintained and all the elements of the calculation
are not available. Example, the results of a sale where the sales tax
history is not stored, or the results of a paycheck when the hourly wage
history is not stored.
2. There is excessive amount of time or cpu cycles involved in processing.
(i.e. you shouldn'y need to wait a minute or 2 to see a small report.)

To accomplish what you need, the easiest method is to bind the textbox named
Total to the field of that name in the table, then use some code like this
in the after update event of each of the contributing textboxes:

Private Sub SubtotalB_AfterUpdate()
If Len(Me.[Prepayment] & vbNullString) > 0 Then
Me.Total = [SubtotalB]-[Prepayment]
End If
End Sub

and:

Private Sub Prepayment_AfterUpdate()
If Len(Me.[SubtotalB] & vbNullString) > 0 Then
Me.Total = [SubtotalB]-[Prepayment]
End If
End Sub

Now, as soon as there is a value in both textboxes, Total will have a value
that will be stored in the table. Remember, do not do this if you can
recalculate every time you open your form.
 
G

Graham Mandeno

You don't.

You should not store the results of calculations - especially fairly trivial
ones.

Instead, create a query, "qryOrders", based on the Orders table. Include in
it all the fields from the orders table and an additional calculated field:
Total: [SubtotalB]-[Prepayment]

You will then see the total in this new field. You can then base your forms
and reports on this query and display this Total field in a bound textbox.

Incidentally, you should not use tables to *view* data (nor queries either,
usually). This is what forms and reports are for.
 
J

Jerrry D

Jerrry D said:
In a form named "Orders" I have a text box named "Total" with this formula in
it =[SubtotalB]-[Prepayment] . How do I see the results of this formula in a
table named "Orders"?

Thanks for any suggestions.
Click to expand...

Thank you for the information. I will use a query as suggested.
 
Top