Form Values on to a Report

Good monring,

I am sure this has a simple answer but here we go.

I have built a form that has a Field Name: Total. The field name total is the dollar amount from a sub form called items. Inside the Sub form there are additional field names (Description, Qty, Unit, Unit Cost, and Total). First question I have, how could I make this sub form calculate values, Total=(Qty*Unit Price) and store that value inside the Subform.item.total field name AND still use  the footer of the sub form to calculate the total for me. When I do this, it will not let me take the SUM of the total column in my sub form. Any suggestions?

Currently, I have the sub form set up so it takes the total of the user entered values instead of taking the (qty*unit cost) formula into perspective. When I do that, the total shows up correctly inside the main form but the total does not actual get placed as a value inside the table. So when I pull up a report on that specific main form record, no value shows up in the total box of the report. Anyone know how I can make it so that value stays in that record ASWELL as shows up in any report? Any help would be much appreciated.


Thanks!
 Report TotalsTotals.png
hcp27Asked:
Who is Participating?
 
Eric ShermanAccountant/DeveloperCommented:
hcp27  ...

Always store just the Product ID, Unit Price and Quantity in your Order Details table.  Then calculate the Total or Extended Price (Unit Price x Quantity) when needed for Forms or Reports, etc.

I always use a Query as a Record Source for the Orders table data entry form that includes the Extended Price calculated field as shown below ...

ExtendedPrice: [UnitPrice]*[Quantity]

Then on your form's footer you can  Sum([ExtendedPrice]).  On reports you can pull the ExtendedPrice field as well.


ET
0
 
als315Commented:
You can get value for field in report from your form.
Select filed in report designer, press Build in data field, select your form and field on it.
0
 
als315Commented:
"filed" = field
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.