Solved

Form Values on to a Report

Posted on 2011-02-24
3
269 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:hcp27
  • 2
3 Comments
 
LVL 39

Expert Comment

by:als315
ID: 34972969
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
 
LVL 39

Expert Comment

by:als315
ID: 34972976
"filed" = field
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 250 total points
ID: 34973478
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now