Need results of calculated field to appear in table

[ldumas777]

This question concerns a calculated field on frmNCC. At the request of the dbase users, they wanted this form as part in Access instead of in Excel where it belongs. So I slaved over the myriad calculations and came up with a beautiful, perfectly functioning form... UNTIL... I remembered that I need to be have that total field [TotalNCCCost] in tblNCC because it needs to be downloaded into a spreadsheet.



Assumptions and attempts: I assume I need to create a field in tblNCC called TotalNCCCost and make that the control source for this field. And I tried that. I then moved the calculation into the AfterUpdate of the form. This caused conflict with the Me.Recalc expressions. I tried deleting the Me.Recalcs and the form ceased to function.

I am at a loss and I am under a serious time crunch. They want to go live with this puppy next week. I would greatly appreciate any help you have to offer. I am an Access novice -- especially when it comes to VBA -- so please be as explicit as possible with your advice. Thank you.

Nonconformance dBase no data.zip
I've attached a copy of the file without data for confidentiality reasons.

[pbaldy]

I don't see the need to save the calculation, as you can simply export a query to Excel that has a calculated field. If you still want to save it:

http://allenbrowne.com/casu-14.html

Noting the second section and using the after update event of the controls, not the form.

[ldumas777]

I don't see the need to save the calculation, as you can simply export a query to Excel that has a calculated field. If you still want to save it:

http://allenbrowne.com/casu-14.html

Noting the second section and using the after update event of the controls, not the form.

Except that the calculated field I want to save is based on other calculated fields that are themselved not stored in the query. You wouldn't think it would be so blanged hard to get one little field to update.

It's been suggested that after each of my Me.Recalc statements I add in tblNCC[TotalNCCCost] = frmNCC[TotalNCCCost]. I'm going to take a deep breath a try. Then after that, I'll probably find a dark corner to weep.

[pbaldy]

The db reflects "spreadsheet thinking". In a normalized relational database, all the stuff in your "Support Personnel" area would be in a related table, and that would be a subform. The related table would have fields for the qty, hours, rate plus one for the type (line operator, line leader, etc). There would be a record in that table for each type used for a project, and the table would be related to the main table by the ID field. There would also be a "Types" table listing all the available types. With that design, nothing really changes when you get a new type like "Widget operators", you just add a record to the Types table. In your design, everything has to be modified.

Then a simple Sum function on a form gets you your total.