When/how to save calculation result in table?

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hi,

I have a calculation that requires several tables and logical operations. I
would like to save the result in a table so that this does not have to be
calculated for simple summaries.

I would like to save it during the data input/edit form.

What event should I trigger on (before update? after update? ...), or how
should this be done?

Need to place the destination field in the form query?
Need to place a testbox for the destination field on the form?

Table "Orders" will contain the destination field "TotalSale"

"TotalSale" is calculated from various OrderDetail tables (several of these
since different products require very different inputs with dif lists,
etc...


Thanks
 
Brian said:
I have a calculation that requires several tables and logical operations. I
would like to save the result in a table so that this does not have to be
calculated for simple summaries.

I would like to save it during the data input/edit form.

What event should I trigger on (before update? after update? ...), or how
should this be done?
Click to expand...

Use the Form's BeforeUpdate event.

Need to place the destination field in the form query?
Click to expand...

Yes, it's definitely easier to have it in the query.

Need to place a testbox for the destination field on the form?
Click to expand...

It's not required in a form:
Me.TotalSale = thecalculatedvalue

But it certainly could have one
Me.txtTotalSale = thecalculatedvalue
 
Hi,

I have a calculation that requires several tables and logical operations. I
would like to save the result in a table so that this does not have to be
calculated for simple summaries.

I would like to save it during the data input/edit form.

What event should I trigger on (before update? after update? ...), or how
should this be done?
Click to expand...

Well, most of the purists around here would argue that it should NOT
be done.... but (in the spirit of my resume, which includes a line
'judicious denormalization, only when necessary') I've been known to
do this occasionally. Be aware that the number stored in this table
WILL BECOME INCORRECT if any of the underlying tables or fields
change, with no reliable way other than recalculating it to determine
that it *is* wrong!

The Form's BeforeUpdate event would be the right place for this. You
could have a textbox with the calculation expression as its Control
Source, and a separate textbox bound to the target field; just set the
bound control to the unbound control's value in BeforeUpdate.
 
Thanks Marsh,

I did try that and it partially worked,

However, since the detail quantity changes are on subforms on several tabed
pages, it appears that I need to act on the beforeupdate event of each
subform as well.

The form automatiaclly updates the grand total unbound text box on the main
form - I wanted to use the change in the value in value of this to drive it
(because is always updates appropriately, but it appears that none of the
events on an unbound box ever activate.)

Using the form's before update event also gave me the following error when I
run one of my other codes to change the recordset to another record:
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit.

So, my solution for now will simply be to only update these totals when the
invoice is printed, because that is the only instance that it matters the
most to create the record.

Regards,

Brian
 
Brian said:
Thanks Marsh,

I did try that and it partially worked,

However, since the detail quantity changes are on subforms on several tabed
pages, it appears that I need to act on the beforeupdate event of each
subform as well.

The form automatiaclly updates the grand total unbound text box on the main
form - I wanted to use the change in the value in value of this to drive it
(because is always updates appropriately, but it appears that none of the
events on an unbound box ever activate.)

Using the form's before update event also gave me the following error when I
run one of my other codes to change the recordset to another record:
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit.

So, my solution for now will simply be to only update these totals when the
invoice is printed, because that is the only instance that it matters the
most to create the record.
Click to expand...


The main form's BeforeUpdate event necomes meaningless when
the calculations are based on values in subforms, it fires
as soon as you click any where in any subform, which is way
too soon for your purposes.

In this kind of situation, I usually wait until the user
does something (such as click a print button) to save the
unchangable "official" total. I then use the fact that the
value is saved (or that a print date is set) to prevent the
record from being edited anymore. So, from where I sit, it
sounds lke you're on the right track.
 
Back
Top