Saving a calculated field back to a table

[libby32]

I have a calculated field in a form and I want to save the calculated data back into a table to be able to use later in other forms. Is this possible??

 

[FoFa]

Yes, but why?
Let me just add you maybe better served creating a function that does the calculation, and on each form you need this just reference the function. That way your caluclation is always consistant and the code is in one place.

 

[libby32]

Thanks for your suggesstion, I'm just a beginner so I wouldn't even know where to start to do that...Anything easier??

 

[Rich]

Use a query to calculate the values, base your form and any others that need to use the values returned on that query

 

[FoFa]

It's not usually that hard. What is your calculation now?
Here is an easy way. Create a query that returns a key fields, and the calculated field. Than create a new module, and in it type FUNCTION MyCalcFld1(ChkKey as string)
MyCalcFld1 = DLookup("CalcFieldNameFromQuery","QueryName","QueryKeyFldName = '" & ChkKey & "'")
END FUNCTION

If your key is a number (long) instead of a string:
FUNCTION MyCalcFld1(ChkKey as long)
MyCalcFld1 = DLookup("CalcFieldNameFromQuery","QueryName","QueryKeyFldName = " & str(ChkKey))
END FUNCTION

Than on your form use the source like =MyCalcFld1(CurrentKey)
Where CurrentKey is the key for the row/record you want

 

[Laffs]

hey!
an easy way to do this is to use th "Me" command
for example here is one of my calculations that calcs 6 fields and totals them to one field:
Private Sub jet_AfterUpdate()
Dim CutCalc As Double
'cut changes TFA value after all filled & allows blank jets
If Not IsNull(Me!jet) = True Then
CutCalc = Me!jet
Else
CutCalc = 0
End If
If Not IsNull(Me![jet2] + CutCalc) Then
Me![TFA] = Me![jet2] + jet + [jet3] + [jet4] + [jet5] + [jet6]
Else
Me![TFA] = Null
End If
End Sub

The only bitch is that this way you must put this in each field you want to calculate.
get it???

 

[mjm01]

I have a similar query to the orginal on one this thread, the responses haven't he;ped me too much so I'll be specific (excuse the rambling ...)

I'm trying to create an inventory management database that performs the usual inventory management functions. I kicked off my using the approtiate template wizard and that's got me about 60% to 70% of the way there.

I've since modified the forms etc and re-wrote the menus.

Big issue I have is ...

The InventoryTransactions subform calculates the UnitsonHand against a particular Product by creating a calculated field with the ControlSource properties of:

=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

This is then included within the Products main form to display the current stock count ... lovely !!

BUT ... I need to be able to use that calculated value to use in other queries, reports etc to allow be do do things such as:

- determine items where UnitsOnHand is less than re-order level
- determine value of stock holdings by part number etc by multiplying UnitsOnHand by UnitPrice etc

Therefore I'd like to either:

- recreate the above calculation in query
- allow a quary to use the value of the calculation on the form

When trying the former, I keep getting the error:

"You tried to execute a query that does not include the specified expression "[ANY FIELD YOU LIKE]" as part of the aggregate function".

Any help?

 

[Balgrath]

A programmer in another forum gave me this advise and i following it I have found that my DBase is much smaller as the calculations are done "on the fly" -

"Storing calculated values in a table violates the Third Normal Form (3NF), which means it's generally a bad thing to do. First, it wastes space in your database and on your hard drive. Second, it will generally make anything realted to that table slower, especially updates. Third, and this is most important, you will have to design your user interface so that if either the start date or the end date changes, the duration is automatically updated. If all you had to do was worry about changes made from your form, it would be enough extra work, but a user can enter data through tables and queries, too. Since Access (technically, the Jet Database Engine) does not support triggers, like more robust database engines do, you would have to make it impossible for the user to access the tables and queries directly, which means implementing user level security. Believe me, all this is much more trouble than simply calculating the value for Duration 'on the fly' in your queries, forms and reports.

So, I recommend NOT trying to store this calculated value in the table, unless you have a very good reason for it, and are prepared for a lot of extra work. "


Cheers

Balgrath