Writing a Calculation Back to a Table

[slaich]

Hello,

I have a pretty simple database. Two tables, 1 form. On the form, the User enters 6 pieces of data. Three other fields are calculated from this data on the form (the formula is the Control Source, the Data Type is Number).

I thought this setup would write the results back into my table, but it doesn't.

Any help would be appreciated.

 

[Mike555]

What specifically do you want to save back to your table?

 

[jebry]

Hi!

There is no need, normally, to write such results back to the table since they can be repeated on any form or report or in any query. That is why Access doesn't do this automatically. Unless there is a strong business need to keep the data instead of calculating it each time, I would go with the majority and not save it. That said, if you still want to know how to save the calculations back to the table, just post again and we can provide some code (VBA is your only option for this).

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[slaich]

Sorry. To clarify, I'd like to save the three calculated fields back to the table.

I'd like to save the results because the formulas are somewhat complicated and it will be easier for me and others to just be able to grab the results in the table.

Thanks.

 

[Mike555]

1. Create the 3 additional fields in your table to hold the calculated values if you haven't done so already.

2. Create an UPDATE QUERY which updates the appropriate table field names based on the values on the form. Here's a quick example of what it may look like...

UPDATE TableName
SET CalculatedField1 = [Forms]![FormName]![CalculationTextbox1]
WHERE [i]'Where clause goes here'[/i]

3. You'll then need a way to execute the query from the form once the calculations have occured. This can be accomplished via a command button which users press after the calculation or an AfterUpdate event of the 3rd and final textbox containing a calculated value. In either case you may have code that looks like this...

Docmd.SetWarnings FALSE
Docmd.OpenQuery [i]"qryUpdateCalculatedFields[/i]
Docmd.SetWarnings FALSE

HTH

 

[Mike555]

Oops...I made a small typo in the previous post. The code used on your form to execute your query may look like this...

Docmd.SetWarnings FALSE
Docmd.OpenQuery "qryUpdateCalculatedFields
Docmd.SetWarnings [b]TRUE[/b]