Updating the record with the result of an equation

[peterlee516]

Is there any way to update the actual record with the result from an equation like these?

1)=DLookUp("[SumOfAllocationAmount]","qryInvoicedPB","[ProjectID]=[Forms]![tblProjects]!ProjectID")

2)=[BudgetPB]-[InvoicedPB]

3)=[qryInvitations subform]!TotalAllocations


Thanks for reading!

 

[rainman89]

sure is, but we need more information on what you are trying to do

 

[peterlee516]

Great!

I basically have a main form with event information

ProjectName
Budgeted
Invoiced
etc..

The invoiced and many other fields have these calculated fields because they are summaries of other tables/queries.

When a field has this equation it's more for view only purposes and the result is not recorded in the actual tables but only calculated as you open the form or change records.

I want to be able to hard code them to the record.

The end goal is to pull these caculated fields into a report.

 

[rainman89]

I want to be able to hard code them to the record.

The end goal is to pull these caculated fields into a report.

you can pull them from the form into the report, you dont have to save them.

rule of thumb is to not save calculations in a table

 

[peterlee516]

Really? I've never done that before.

How do you do that?

Thanks!

 

[rainman89]

in the report you create a text box and put

=forms!frmname.yourcontrolname

as the controlsource

mind you the form must be open in order for this to work.

 

[peterlee516]

ah.. so there is a catch.

Why is it bad practice to record the result of an equation into the table?

For example I have a field named "Total Invoiced" in the Project Table. The control source of the field is "=DLookUp("[SumOfAllocationAmount]","qryInvoicedPB","[ProjectID]=[Forms]![tblProjects]!ProjectID")

I don't see why it's a problem?
"

 

[rainman89]

its more of a guideline. ie size etc...

there are exceptions to storing calculations

 

[peterlee516]

If all the reports that include calculated fields need to have their respective forms open, I have to find another solution because I won't be able to do that.

Can you please elaborate on how I can do it by storing the results of the calculations?