need to store calculation (1 Viewer)

[JCross]

i know you're not supposed to, but i need to store a calculation from my subform in a table. the subform is based on a query, and the main form is based on a table. the calculated field is

=Sum(([Units]*[LastCost])/[Yield])

which adds up all of the lines in the subform.

where do i put this and how? i tried to do an update query on the afterupdate of the calculated field and nothing happened. when i run the update manually, it works though.....it was an update put the calculation into the table field.

thank you!!

jennifer

 

[cliff7376]

i'm new at this but it worked if you ran the update query alone? what was the code for running the update query on the after update?

 

[JCross]

i took the SQL from the update query and did

docmd.runsql "sqlstatement"

on the afterupdate of the calculated control.

it does NOTHING!but if i go in and manually run the update query, it works fine.

any ideas?


[This message has been edited by JCross (edited 03-19-2002).]

 

[cliff7376]

if your query is in access you need to use docmd.openquery "name of query". but if you are using SQL i don't know. What i would do in that sitation was to make an update query in access using your SQL database. But again i am new at this. I am sure there are better ways.

 

[JCross]

What event should I put the docmd on? and do I just use

DoCmd.OpenQuery "qryPrepLastCost"

and that's it?

thanks for your help

jennifer

 

[cliff7376]

i guess after update. depends when you want it to perform that task.

 

[JCross]

ok. your docmd works - but i can't find the right event. i just put a break in the code to see if it run, and even though the control is calculting correctly there is never an afterupdate event.

any ideas on events for calculted controls?

Jennifer

 

[cliff7376]

maybe onexit? i'm not very good at that stuff. sorry

 

[JCross]

i just can't figure it out! now the docmd runs if i have a break on it, but it doesn't run if i don't. ARGH!

but thank you SO much for your help. i couldn't have gotten this far......

jennifer

 

[Rich]

Trying to store a calculated field in this way will cause you many problems, if you insist then add a command button which requires user intervention to force the calculation to be saved and then locks the relevant fields to ensure the integrity of your calculation.
HTH

 

[JCross]

i noticed the problems

i realized i don't need to store the result of the control so much as the calculation. how can i do this?? i can come up with the right calc. in a query....but the query is based on another query that is not updateable and so i don't think i can use it for an update?

how and when can i run SOMETHING to store this calculation? how would you run an update query to store a calculation? i only know how to update to the value of a field....

thanks

Jennifer

[This message has been edited by JCross (edited 03-19-2002).]

 

[russi]

Hi. This is what I do when I am required to store a calculation due to regulations.

I have a text box on the form that performs the calculation. Then, on the After Update property, I make the value of a NOT visible field that exists in my table be equal to this calculated field. (ie, calculated field is AgeCalc and the invisible field that exists in the table is AgeAtApplication)

Works great.

Hope this helps you.

Russ

Russ

 

[JCross]

Thanks Russ - I'm going to give it a try....

Jennifer

 

[ltiner]

I am dealing with the save issues and I like Russ's solution, but I can't get it to work.

In which control do you set the after update event. I tried it in the textbox that does the calculation, but it doesn't seem to "fire". I put it in the before update event of the form and it worked, but I don't really like it there (not sure why)
Come to think of it, why does there have to be a not-visible field on the form?

Larry