Storing a total

B

Bunky

I realize it is not the 'right' way to do this but because of a time crunch,
I need to store a total that is calculated on a form into a table. It goes
against my grain but when the bosses tell you. . . . So, to buy some time,
can anyone tell me how to do this?

Thanks,
 
R

RBear3

Why would you need to do that? Anything that you can do with a table can
also be done with a query. Just do the calculation in a query.
 
S

SusanV

Why do they insist on this? This is a VERY bad idea, because if any of the
factors involved in the calculation change, the stored value will be wrong.
That's why totals should be done on the fly in reports and forms... have you
explained this to them?
 
B

Bunky

Susan,

Yes, I have explained this to them. The problem seems to be a poor design
for tables that they do not want me to take the time to correct and convert
the data over at this time. They are aware of the hazards but want to go the
'quick' way. I know it could bite them in the end but . . .
 
S

SusanV

Meh, stupid bosses <grin>

You can make another filed in an existing table, or a new table. I would not
bind the totals control you are doing the calculation in to the field -
rather use an update query to populate the new field... perhaps via a
button? There's no GOOD way to do this...

Very ugly, very bad.
 
B

Bunky

Yeah - it is the Peter principle I fear.

Unfortunately, I must plead stupid. I do not understand how to get it to
work. I have added a new field to the existing table but obviously the
control source is where I would normally do my calculations and I do not know
how to tell Access that I want the value of the calculation put into the new
field. Ideas?
 
S

SusanV

Well, you can do it automagically, using the after update event calculated
box to update the bound box (me.boundbox = me.calculatedbox) or simply use
the bound box for the calculation...
 
J

John W. Vinson

Unfortunately, I must plead stupid. I do not understand how to get it to
work. I have added a new field to the existing table but obviously the
control source is where I would normally do my calculations and I do not know
how to tell Access that I want the value of the calculation put into the new
field. Ideas?
Click to expand...

You need to "push" the calculated value into a control bound to the
(redundant) table field. Let's say you have a textbox txtCalc with the
calculated expression as its control source, and another textbox txtBound
bound to the table field. You can use the Form's BeforeUpdate event to move
the data:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<do any validation checking first, e.g. make sure that all the components of
the calculation are present>
<if all is well then...>
Me!txtBound = Me!txtCalc
End Sub

Susan's suggestion is in the right direction - but unfortunately a calculated
field's AfterUpdate event will not fire; only controls updated by the user do
so.

John W. Vinson [MVP]
 
B

Bunky

John / Susan,

I have placed the code on both the bound source and then tried the textbox
in the BeforeUpdate event and it does not show any value still either way I
go. I am sorry to be a pain (and believe me I imagine you are growing to not
like my problems).
 
S

SusanV

Hi Bunky,

As per John's instruction, you want to place the code in the FORM's Before
Update event (click the little square in the upper left of the form to get
to it's property sheet) rather than any of the actual controls' events...
 
B

Bunky

Ahhhhh . I see. I tried that and it is now interfering with some other code
I have on the a combo box AfterUpdate. I will try to figure it out without
bothering you.

Thanks to both for the info.
Sometimes this position can be very trying! Thanks again!
 
B

Bunky

Susan / John

I put the code as you said on the Form as follows.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!Foundationskill = Me!Text582
End Sub

If I am reading everything correctly that was guiding me by John, this is
exactly what he was saying but it still won't push the calculated amount.
The first field is in the table and the second field is a textbox where
calculations reside. Ideas?
 
J

John W. Vinson

Susan / John

I put the code as you said on the Form as follows.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!Foundationskill = Me!Text582
End Sub

If I am reading everything correctly that was guiding me by John, this is
exactly what he was saying but it still won't push the calculated amount.
The first field is in the table and the second field is a textbox where
calculations reside. Ideas?
Click to expand...

Which textbox is which? What is the Control Source of Foundationskill (and why
do you want to kill foundations?) and of Text582? What do the two textboxes
show after you have entered data into the form? Is there a Subform involved?

John W. Vinson [MVP]
 
B

Bunky

Foundationskill is the field in the Table and it's control source is named
the same. (I'm not really killing foundations. It stands for foundation
skills) LOL. Text582 is the calculation area and its control source is
states =([text316]*0.5)

Text582 shows the correct totals as I enter data. There is only one form
and no subforms. There are a ton of Option Groups on the form if it matters.
 
J

John W. Vinson

Susan / John

I put the code as you said on the Form as follows.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!Foundationskill = Me!Text582
End Sub

If I am reading everything correctly that was guiding me by John, this is
exactly what he was saying but it still won't push the calculated amount.
The first field is in the table and the second field is a textbox where
calculations reside. Ideas?
Click to expand...

Doublecheck to see if FoundationSkill is in fact not being updated. You won't
see it on the form - the BeforeUpdate event fires after the user has left the
record, and any changes it makes will be invisible until you navigate back to
the record.

Take a look in the Table, or use the form to navigate back to this record.

Note that this code will ONLY update FoundationSkill if you actually make some
change on the record. If you want to fill the values of existing records with
the appropriate sum, you'll need an Update Query.

John W. Vinson [MVP]
 
B

Bunky

I went back to the form today and entered an entire new record. Then I went
out of the form entirely and went to the table - just zeros. I put a 'Save'
button on the form and entered a change to the record and punched the 'Save'
button - table had zeros. I will keep trying things that I can think of but
I am running out of options. How would you like to take a trip to Indy? LOL
The speedway is nice this time of year.
 
B

Bunky

John, Susan

I thank you both for all of your help. I finally found the problem. The
Data Type on the Table definition was incorrect and always changing the data
to '0'.

Again, Thank you for ALL YOUR HELP. If you ever get to Indy, you have my
email.

Kent
 
J

John W. Vinson

I thank you both for all of your help. I finally found the problem. The
Data Type on the Table definition was incorrect and always changing the data
to '0'.
Click to expand...

ahhhh.... classic problem: default Number type is Long Integer, and you try to
store a fraction.

Glad you got it working!

John W. Vinson [MVP]
 
Top