Add data to a table from an equation, how? 3

[egims]

I have created a form where several dollar amount figures are entered. Finally, I have used a math equation to add all these amounts and produce a sum.

Is it possible to for the sum to somehow be placed into a field of the table?

Thanks

 

[lespaul]

yes.


[
I assume you also want to know how to do it, even though you didn't ask. Without knowing anymore than I do, you can write a query:

UPDATE tblName SET fldName = Forms!frmName!ctrlName

HTH

Leslie
]

 

[willir]

egims

If you are just calculating sums for three fields, another approach would be to create an unbound text field.

Make the ControlSource[tt]
=[Your1stField] + [Your2ndField] + [Your3rdField]...
[/tt]

Hi Leslie Finished Naked empire last week, and my son got a hold of it, and has read twice since then. Too bad we have to wait so long between tomes.

 

[Dophia]

I am having a similar problem and have tried Willir's suggestion to create an unbound text field....

"Make the ControlSource =[Your1stField] + [Your2ndField]+ [Your3rdField]".

I created a new unbound text field in my form and it shows the data, but how do I get it to update the table with this unbound text field?

Help on this would be greatly appreciated.
Thanks, Dophia

 

[dhookom]

Could anyone provide a good justification for storing a value that can easily be calculated from existing fields? This is normally not good practice.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

Dophia and egims

The third rule of normalization is not to store calculated values.

http://www.abcdataworks.com/developers.htm

or

http://r937.com/relational.html

The reason is that calculated values can always be re-calculated, and more importantly, they can become a "maintenance" item.

However, there are times when you use some common sense. For example, you have a General Ledger system where you want to see monthly balances, and the monthly balances are based on 10,000x of transactions. A) after closing / finalizing a month, it is highly unlikely monthly transactions need to be recaluclated; B) Some time down the road, you may need to free up space and deleted old transaction.

It makes sense to store monthly transactions because a) the numbers will not change; b) you can / may need to get rid of the detail records but still maintain some of the historic picture.

...Moving on

Instead of the using the ControlSource to depict the calculated fields...[tt]
=[Your1stField] + [Your2ndField] + [Your3rdField]...[/tt]

You use "Event Procedures".

Assumptions
- CalcValue is the name of the cacluated field
- Your1stField, Your2ndField... name of data fields

For "AfterUpdate" event for each Your???Field, you use some simple coding...
[tt]
Me.CalcValue = Nz(Me.Your1stField, 0) + Nz(Your2ndField, 0) ....[/tt]

The Nz() function accommodates null / empty values where it substitutes 0 for the null value - otherwise, you would get an error.

You can also use the same formula for the BeforeUpdate record event procedure.

BTW, since you re-caculate the value several times on the form - AfterUpdate field event for each appropriate field, and for the BeforeUpdate record event, you can use a centralized approach by calling a central routine...

Private Sub ReCalcMySum()

Me.CalcValue = Nz(Me.Your1stField, 0) + Nz(Your2ndField, 0)

End Sub

Then for each of the appropriate event procedures,

ReCalcMySum

...Like I said, real simple.

...Moving on
I suspect you will need a bit of info in how to create an Event Procedure.

Have the form open in design mode. Make sure the "Properties" window is open ("From the menu, "View" -> "Properties")

Select one of the fields that the calculated value will be based on. The select the "Event" tab on the Properties window, then select the field, "AfterUpdate". Pick [Event Procedure] from the pick list. You will see a command button to the right with "..." - click the command button. This will open up the coding window, and generate a bit of code...

Private Sub Your1stField_AfterUpdate()

End Sub

Enter the ReCalcMySum in between the Private Sub... and ...End Sub

Then after the "End Sub" line, enter Private Sub MyReCalcSum() and hit the <Enter> key. Type in your calculation formula.

You call this subroutine anywhere from within the Form.

On last thing. To create an event procedure for the entire form, select the "square" in the top left corner where the vertical ruler meets the horizontal ruler.

Richard

 

[dhookom]

willir,
I have read some good posts from you in the past but this is one of the best, most complete explanations I have read in these fora.

I hope others appreciate your time.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

You are too kind Duane

 

[Dophia]

Willir:

Yes, thank you very much for the infomation that you posted.

It has given me insight and an understanding in another facet of this program.....one which regularly mystifies me!! I am an accountant trying to use a program that has frustrated and me so much that I want to scream!! ..... So, I really appreciate the time you took to explain the solution.

Thanks again, Dophia

 

[midastouch]

Willir

Excellent description of what to do.. i knew it had to be possible some way!

Many thanks

Stu