Microsoft Access - Help on storing a calculated field from form in table

Asked By L Williams on 26-Jun-12 03:50 PM

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.

Here is a copy of the file with all of the data removed for confidentiality reasons. http://www.eggheadcafe.com/fileupload/1717688819_Nonconformance dBase no data.zip

wally eye replied to L Williams on 26-Jun-12 07:12 PM

A couple tricks to get you there:

Rename your existing TotalNCCCost to TotalNCCCostCalc, and create a new TotalNCCCost control.  Hide the TotalNCCCost control (I normally change the background color to red, to highlight the fact that it is hidden).  Then, create a Form_BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Me.TotalNCCCost = Me.TotalNCCCostCalc

End Sub

I know you don't have time to work with this before you go live, but it is not a good model to have all your fields spelled out in this manner.  Normally, I would recommend having a couple tables, say tblSupportPersonnel:

SptPrsID  AutoNumber
SptPrsDesc  Text - Description of support personnel
SptPrsRate  Double

and tblNCCPersonnel:

SptPrsID Long - Foreign Key from tblSupportPersonnel
Qty  Long
Hrs  Long
TotHrs  Long
Rate  Double
TotalAmt   Double

Or something like that.  When you want to add a new support personnel, you would just add an entry to the tblSupportPersonnel.  Unfortunately, it does take a lot more work up front, and is a bit more complicated.  The benefits over time will add up, though.

L Williams replied to wally eye on 26-Jun-12 08:32 PM
I was beginning to ffear something like that. #1 -- Gee, it would have been a lot simpler to leave this bugger in Excel to begin with. and #2 - There really ought to be a way to tell Access, "Hey, that field there; put it over there."

Thanks for your help.
Pat Hartman replied to L Williams on 28-Jun-12 08:24 PM
I agree.  There is definitely a problem with your schema.  In spite of that, there is no reason to store a calculated value.  Modify the query you use as the RecordSource of the form to do the calculations.
Start by Removing the calculated columns from the table.

Select ..., [LineOpTtl]*[LineOpRate] As LineOpCost, [LineLdrTtl]*[LineLdrRate] As LineLdrCost, ....
From ...
Once you've fixed the query, you can bind LineOpCost, LineLdrCost, etc to controls.  When the other values are changed, Access will calculate these.
Export the query rather than the table.