Saving a Calculated Values from a Subform

  • Thread starter Thread starter Joy M
  • Start date Start date
J

Joy M

Hi -

I have a form with a subform. The form is mainly identification. The daily
expenses are entered in the subform. Taxes are involved (which could
change), so I want to save 2 values calculated from the subform: Net Expense
Claimed and Total Taxes, in the bound fields of the parent record (i.e. the
form).

So far I have succeeded in saving these values correctly to unbound
textboxes, TempNet and TempTax on the form.

I would like to save these values into fields in the parent record, so that
INE_Net = TempNet and INE_Tax = TempTax, when I close the form.
INE_Net and INE_Tax are the bound text boxes connected to the fields in the
parent record.

Form : frmInstructorExpense
Subform : frmInstructorExpenseSUBF

So far so good, but I don't know which event to use to save the calculated
values in the bound fields. That is my question. What if I just close the
form after entering all the info in the subform, and do not need to add
anything else to the form. In which event, do I save my numbers into the
parent fields???

Thanks so much,

Joy
 
What you also can do is use the afterupdate event of the textbox(es) which
are used on the subform.
Whenever a value is updated and so also the calculationfields are update you
can store the new value in the bound fields.

e.g.

private sub EntryField_AfterUpdate()
INE_Net=TempNet
INE_Tax=TempTax
end sub
 
If the taxes change, won't the calculated amounts also change? There
generally is nothing to be gained from storing calculated values. They can
be produced on the fly any time you need them. True, you can store the
calculated value, and have it overwritten if it changes, but to what end?
 
Hi -

Your idea didn't work....maybe I have to backup a step...

There is query in the subform which calculates the Net_Total and Tax_Total
per day (1 child record)

In the footer of the subform, I set the control sources of unbound textboxes
frmInstructorExpenseSUBF.Form!NetExpSubtotal = Sum [Net_Total]
frmInstructorExpenseSUBF.Form!TaxExpSubtotal = Sum [Tax_Total]

As invisible unbound text boxes on the form, I set the control sources of
TempNet = frmInstructorExpenseSUBF.Form!NetExpSubtotal
TempTax = frmInstructorExpenseSUBF.Form!HSTExpSubtotal

It works perfectly to here.

Problem is - I put a msgbox in TempNet_AfterUpdate (also one in
TempTax_AfterUpdate) but it didn't appear before I closed the form...

I need some event to read the values in TempNet and TempTax (so I can save
them in the parent record) before I close the form.

Thanks so much for your help --

Joy
 
Hi Bruce,

Yes, I have read all the postings about this idea of not storing calculated
values and recalculating them on the fly.

The tax rate can be changed by the administrator at the beginning of the
program, but it is not likely to change.
If it did change, I would have my expenses correctly split by net claim and
tax charged and that is what I want.
(This table can then be used as input for other reports.)

It is historical data (like extended price) which is dependent on the day
the expenses occurred.

Thanks for your ideas,

Joy
 
On which form are the 2 fields where you want to store the values?
on the parent form or on the subform
these 2 fields need to be bound to the according fields

so let us say that the event is triggered form the subform and the fields
are on the parent form then you should can the code into

parent.INE_Net= frmInstructorExpenseSUBF.Form!NetExpSubtotal
parent.INE_Tax= frmInstructorExpenseSUBF.Form!HSTExpSubtotal
 
Hi Raoul -

The 2 fields where I want to store the values are located on the form (not
on the subform). That is, the Temp fields and the fields bound to the
parent are
both on the parent form.

I have good values in the Temp fields. I just want to save them in the
bound fields before I close the form.

Seems like - all the calculations are done in the Control Source property,
so I am not getting another event triggered on the parent form, before
closing it.

Thanks for your ideas,

Joy


JaRa said:
On which form are the 2 fields where you want to store the values?
on the parent form or on the subform
these 2 fields need to be bound to the according fields

so let us say that the event is triggered form the subform and the fields
are on the parent form then you should can the code into

parent.INE_Net= frmInstructorExpenseSUBF.Form!NetExpSubtotal
parent.INE_Tax= frmInstructorExpenseSUBF.Form!HSTExpSubtotal

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Joy M said:
Hi -

Your idea didn't work....maybe I have to backup a step...

There is query in the subform which calculates the Net_Total and
Tax_Total
per day (1 child record)

In the footer of the subform, I set the control sources of unbound
textboxes
frmInstructorExpenseSUBF.Form!NetExpSubtotal = Sum [Net_Total]
frmInstructorExpenseSUBF.Form!TaxExpSubtotal = Sum [Tax_Total]

As invisible unbound text boxes on the form, I set the control sources of
TempNet = frmInstructorExpenseSUBF.Form!NetExpSubtotal
TempTax = frmInstructorExpenseSUBF.Form!HSTExpSubtotal

It works perfectly to here.

Problem is - I put a msgbox in TempNet_AfterUpdate (also one in
TempTax_AfterUpdate) but it didn't appear before I closed the form...

I need some event to read the values in TempNet and TempTax (so I can
save
them in the parent record) before I close the form.

Thanks so much for your help --

Joy
Click to expand...
Click to expand...
 
It seems to me then that the thing you most need to store is the tax rate and
other information that could change later. If somebody pulls out a pencil
and paper and performs the calculation, will it match the calculation result
you have stored? If not, it could be confusing. If so, what is gained by
storing it?
With extended price you are storing quantity and price, and calculating
extended price from those numbers. I had assumed that in similar fashion the
tax rate in effect on a particular day would be the operative number.
 
If you must store the calculated values, perform the calculation in an
unbound text box and set another event (such as the After Update event of the
controls that provide the variables for the calculation) to copy that value
to another text box that is bound to the appropriate table field:

Me.txtCalculation = Me.txtCalculatedStoredValue

You can hide the bound text box after you verify that it works properly.

Joy M said:
Hi Raoul -

The 2 fields where I want to store the values are located on the form (not
on the subform). That is, the Temp fields and the fields bound to the
parent are
both on the parent form.

I have good values in the Temp fields. I just want to save them in the
bound fields before I close the form.

Seems like - all the calculations are done in the Control Source property,
so I am not getting another event triggered on the parent form, before
closing it.

Thanks for your ideas,

Joy


JaRa said:
On which form are the 2 fields where you want to store the values?
on the parent form or on the subform
these 2 fields need to be bound to the according fields

so let us say that the event is triggered form the subform and the fields
are on the parent form then you should can the code into

parent.INE_Net= frmInstructorExpenseSUBF.Form!NetExpSubtotal
parent.INE_Tax= frmInstructorExpenseSUBF.Form!HSTExpSubtotal

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Joy M said:
Hi -

Your idea didn't work....maybe I have to backup a step...

There is query in the subform which calculates the Net_Total and
Tax_Total
per day (1 child record)

In the footer of the subform, I set the control sources of unbound
textboxes
frmInstructorExpenseSUBF.Form!NetExpSubtotal = Sum [Net_Total]
frmInstructorExpenseSUBF.Form!TaxExpSubtotal = Sum [Tax_Total]

As invisible unbound text boxes on the form, I set the control sources of
TempNet = frmInstructorExpenseSUBF.Form!NetExpSubtotal
TempTax = frmInstructorExpenseSUBF.Form!HSTExpSubtotal

It works perfectly to here.

Problem is - I put a msgbox in TempNet_AfterUpdate (also one in
TempTax_AfterUpdate) but it didn't appear before I closed the form...

I need some event to read the values in TempNet and TempTax (so I can
save
them in the parent record) before I close the form.

Thanks so much for your help --

Joy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top