Data from calculated fields in form not getting into table..

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

Guest

After my recent experience of asking for help, I always come here first now...
In my form I have fields that are calculated automatically depending on
selections from drop downs in other parts of the form. These auto-calc
fields are not getting to the table. Any suggestions?
 
After my recent experience of asking for help, I always come here first now...
In my form I have fields that are calculated automatically depending on
selections from drop downs in other parts of the form. These auto-calc
fields are not getting to the table. Any suggestions?
Click to expand...

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]
 
I know it can be frustrating to ask how to do something and instead be told
not to do it......

JV's reply is absolutely right and he is one of the consistently expert
MVPers that monitors this site. The only exception to his guideline which
really is universal in DB design is, for me, the final sales amount in
transaction tracking situations - which while is almost always a calculated
value when it first occurs - must also absolutely positively be stored in a
table for legal and other long term record keeping reasons.....for the same
reasons he mentions - if the components value changes (such as tax rate,
etc...) that past sales amount must not change.

The default assumption is that this is not your situation. Your value not
being recorded in a table must mean that it is not bound to the table...did
you add it free form rather than via the field list? yet in any case one does
NOT encourage you to store any calculated value....
 
Thank You for responding, I was thinking it wasn't possible, not that it's
just a default, the data is exported to an excel spreadsheet, which then is
saved for posterity. To alleviate an over abundance of data, I would then
like to make a new table monthly. So yes, I would like the data that is
derived to go into the table.
 
well - for your data to be stored in a Table, your field on your form must
come from the 'field list'.....

the 'field list' is an icon on your menu tool bar - and it
represents/contains all the fields in the table that your form is based on...
 
Thank You for responding, I was thinking it wasn't possible, not that it's
just a default, the data is exported to an excel spreadsheet, which then is
saved for posterity. To alleviate an over abundance of data, I would then
like to make a new table monthly. So yes, I would like the data that is
derived to go into the table.
Click to expand...

Ok... it can be done; it requires a bit of VBA code.

One way is to use the Form's BeforeUpdate event. Each calculated field
should be on the form twice - once unbound with the calculated
expression as the control source, and a second one bound to the table
field. In the Form's BeforeUpdate event use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBoundControl1 = Me!txtCalcControl1
Me!txtBoundControl2 = Me!txtCalcControl2
End Sub

using your own control names.

John W. Vinson[MVP]
 
I think I am missing something. The VBA code either isn't working, or I'm not
doing it right. What do I use as the control source, the equation for the
field, or the field name? Do I put the equation in the property before
update, then do the VBA code? I'm not exactly sure how to do this. Thank
you for your patience, in working with me, I can't be a bit obtuse sometimes.
 
I think I am missing something. The VBA code either isn't working, or I'm not
doing it right. What do I use as the control source, the equation for the
field, or the field name? Do I put the equation in the property before
update, then do the VBA code? I'm not exactly sure how to do this. Thank
you for your patience, in working with me, I can't be a bit obtuse sometimes.
Click to expand...

The code should be in the Form's BeforeUpdate event. You need *two*
textboxes for each field where you want to store calculated data: one,
which I'm calling txtCalcControl1, would have your calculation
expression as its Control Source. The second, txtBoundControl1, would
have the table fieldname as its Control Source. This latter control
can have its Visible property set to False if you don't need to see
the same information twice; its only purpose is to get the data stored
into the table.

Possibly unneeded instructions: to insert the code, open the Form in
design view, and view its Properties. Select the Form's BeforeUpdate
property; click the ... icon by it; select Code Builder. Copy and
paste the code, making appropriate changes to the control names. Use
Debug... Compile <my database> to compile it, and correct any VBA
errors. Save the Form.

Hope this works for you - if not post back with the actual Control
Sources of the controls, a listing of your code, and the error message
or problem that you're seeing.

John W. Vinson[MVP]
 
Ok, I understand what we are doing, taking the derived date and putting it
directly into another field that can get transferred to the table. (Right?).
I did what you said to do, I put the equation int the Me!txtCalc, and field
that is supposed to put it in the table on the Me!txtBound. But the table
field came up $0.00. The equation is:=([Accomodation_Type_Fee]*[Number of
Days])+[Events_table_Fee]+[Hottub_Fee]+[Weekend_Fee] which is on the data
control source of the Field, Total Fee's. The field that I am using to port
the data to the table is "calc", the data control source also has the
equation in it.
 
Ok, I understand what we are doing, taking the derived date and putting it
directly into another field that can get transferred to the table. (Right?).
I did what you said to do, I put the equation int the Me!txtCalc, and field
that is supposed to put it in the table on the Me!txtBound. But the table
field came up $0.00. The equation is:=([Accomodation_Type_Fee]*[Number of
Days])+[Events_table_Fee]+[Hottub_Fee]+[Weekend_Fee] which is on the data
control source of the Field, Total Fee's. The field that I am using to port
the data to the table is "calc", the data control source also has the
equation in it.
Click to expand...

Please post the code in your Form's BeforeUpdate event. (You *did*
write or copy the VBA code, I hope...?)

John W. Vinson[MVP]
 
Here is the vba :
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!txtcalc = Me!txt = ([Accomodation_Type_Fee] * [Number of
Days])+[Events_table_Fee] + [Hottub_Fee] + [Weekend_Fee]
End Sub

Do to the fact it's not working, I assume I made some elementary mistake...lol
 
Here is the vba :
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!txtcalc = Me!txt = ([Accomodation_Type_Fee] * [Number of
Days])+[Events_table_Fee] + [Hottub_Fee] + [Weekend_Fee]
End Sub

Do to the fact it's not working, I assume I made some elementary mistake...lol
Click to expand...

Yes, you did. This does not in any respect whatsoever resemble what I
suggested, and I have no idea how you came up with it.

Set the Control Source property of the control named txtCalc to

= ([Accomodation_Type_Fee] * [Number of Days]) + [Events_table_Fee] +
[Hottub_Fee] + [Weekend_Fee]

or, if any of these controls might be empty, to

= (NZ([Accomodation_Type_Fee]) * NZ([Number of Days],1)) +
NZ([Events_table_Fee]) + NZ([Hottub_Fee]) + NZ([Weekend_Fee])

This will convert all null Fees to $0.00 and a null Number of Days to
one day.

Set the Control Source property of the textbox named txt (if that's
indeed what it's named) to the name of the field in which you wish
this total to be stored. I don't know what that name is, hopefully you
do.

Then use

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txt = Me!txtcalc
End Sub

to copy the *calculated* value from the textbox named txtCalc, into
the bound control named txt, from which it will be stored into the
table.

John W. Vinson[MVP]
 
Ok, I have 2 fields for the combined total. 1 is [Total] which has the
equation in it, the other is [calc] which is the one (I believe) that will be
used to put the Total in the table. I tried what you said, and the error
that came up unable to find field [calc].
I have as the control source on field [Total] the equation, the control
source on field [calc] is set to field [Total]. The VBA code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![calc] = Me![Total]
End Sub

Thank You for your infinite patience.
 
Ok, I have 2 fields for the combined total.
Click to expand...

No. You don't.

You have two FORM CONTROLS for the combined total. Fields exist in
tables; Controls exist on forms. It's easy to confuse them - but
don't!
1 is [Total] which has the
equation in it, the other is [calc] which is the one (I believe) that will be
used to put the Total in the table. I tried what you said, and the error
that came up unable to find field [calc].
Click to expand...

My *intention* - perhaps not clearly expressed - was to have two
Textbox controls; the one I called txtCalcControl1 was the unbound
control name, with an expression as its Control Source. The textbox
named txtBoundControl1 was to be bound to some field in the Table, and
that field must be part of the form's recordsource. You've chosen to
assign your own names to these controls, which was my intent... but
that's what *I* posted. I did not post anything suggesting that you
have controls named Total or calc - those are *your* idea.
I have as the control source on field [Total] the equation, the control
source on field [calc] is set to field [Total]. The VBA code is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![calc] = Me![Total]
End Sub
Click to expand...

Is there a Textbox on the form with a Name property of

calc

If there is what is its Control Source?
What is the name of the table field into which you wish to store the
value calculated in the Total textbox?

What you need is:

- A Field (currency datatype I'd guess) in your Table into which you
wish to store the total value
- That Field must be included in the Form's Recordsource query
- You need a Textbox (named calc, or MyCalculatedFieldRelay, or
whatever name you like) with this field as its Control Source property
- The code should set that textbox to Me!Total

John W. Vinson[MVP]
 
Is there a Textbox on the form with a Name property of

calc yes

If there is what is its Control Source?
Click to expand...
The Total control on the form. (which has for it's contol the euqation)
What is the name of the table field into which you wish to store the
value calculated in the Total textbox?
Click to expand...

Total, though that is the source of the Total contol on the form, do I need
to make another one?
 
I got it to work. Thank You so much for your time on this, it really has
been invaluable. Also I learned alot, and can apply this to other derived
fields I need to be stored.

Peace Sean
 
The Total control on the form. (which has for it's contol the euqation)


Total, though that is the source of the Total contol on the form, do I need
to make another one?
Click to expand...

These are TWO CONTRADICTORY STATEMENTS.

The first says that the Control Source of the control named Total is
an expression.

The second says that the Control Source of the control named Total is
[Total].

You can't have it both ways.

ah... ok, see that you got it sorted ! Good, and sorry about getting
so testy!

John W. Vinson[MVP]
 
Back
Top