Help with Displaying a Calculation on the Table

[jetsbakabak]

Hello!

One of the Controls on my "Purchase Order Form" is the "Total:" field.
It gets its data from a calculation coming from the "Purchase Order
Form_subform". My question is, how do I display the calculated result
from the form to the Table "LedgerEntries"? (Keep in mind that the text
box on the "Purchase Order Form" that is set for "Total" is already
bound to the "LedgerEntries" table but it is not displaying the
calculated result on the Table)

Any help would be tremendously appreciated.

Jet

 

[fredg]

Hello!

One of the Controls on my "Purchase Order Form" is the "Total:" field.
It gets its data from a calculation coming from the "Purchase Order
Form_subform". My question is, how do I display the calculated result
from the form to the Table "LedgerEntries"? (Keep in mind that the text
box on the "Purchase Order Form" that is set for "Total" is already
bound to the "LedgerEntries" table but it is not displaying the
calculated result on the Table)

Any help would be tremendously appreciated.

Jet

Access is not a spreadsheet.
You don't save the calculated total in a table because there is no
need to save it. Storing calculated data is redundant.
As long as your table stores the individual data used in the
calculation, anytime you need the total simply re-calculate it, in a
query, or on a form or report.

 

[John Vinson]

My question is, how do I display the calculated result
from the form to the Table "LedgerEntries"?

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]

 

[jetsbakabak]

Thank you for the very quick response Fred.

 

[jetsbakabak]

Thank you for the response John.

Putting aside best practices and practicalities, is there a way to
accomplish what I am thinking of? If there is, can you show me how?

Thanks.

Jet

 

[John Vinson]

Thank you for the response John.

Putting aside best practices and practicalities, is there a way to
accomplish what I am thinking of? If there is, can you show me how?

Well... that's a bit like showing someone how to shift into reverse at
high forward speed... you can DO it, but you really SHOULDN'T!

If you want to violate, not only best practices, but the security and
accuracy of the data in your database, recognize the drawbacks, etc.,
then you can use the Subform's AfterUpdate event to "push" the total
into a parent form textbox:

Private Sub Form_AfterUpdate()
Me.Parent.controlname = Me.totalcontrol
End Sub

Where controlname is a textbox on the parent into which you wish to
put the sum, and totalcontrol is the name of the textbox on the
subform which contains the Sum.

John W. Vinson[MVP]

 

[jetsbakabak]

Thank you John. I think we have a slight misunderstanding of what it is
that I am trying to accomplish.

I already have the Subtotal coming from the Subform displayed on my
main Form under the Fieldname "Total". What I am asking is, how can I
display the same value (coming from the subform) to a table? In other
words, it's the same value or figure but displayed on both the main
form and also to a field on a table.

Any help would be appreciated.

Jet

 

[John Vinson]

I already have the Subtotal coming from the Subform displayed on my
main Form under the Fieldname "Total". What I am asking is, how can I
display the same value (coming from the subform) to a table? In other
words, it's the same value or figure but displayed on both the main
form and also to a field on a table.

Please reread my posts in this thread.

Yes, you CAN put the sum into a Table.

But No, you should NOT do so. IT IS A BAD IDEA AND BAD DESIGN.

I've explained why in my previous messages, and have also explained
how to do it if you really want to do so anyway.


John W. Vinson[MVP]

 

[fredg]

Thank you John. I think we have a slight misunderstanding of what it is
that I am trying to accomplish.

I already have the Subtotal coming from the Subform displayed on my
main Form under the Fieldname "Total". What I am asking is, how can I
display the same value (coming from the subform) to a table? In other
words, it's the same value or figure but displayed on both the main
form and also to a field on a table.

Any help would be appreciated.

Jet

John and I were both trying to help you, and I don't believe there is
any misunderstanding on our part.
Tables are for storing data and there is no need for you to store
calculated data.
Whenever you need that Total again, recalculate it.

 

[jetsbakabak]

John and Fred...Thanks a lot...I took both of your advice and I
proceeded to creat queries and reports to generate the output that I
need.

Thanks for spending the time.