Unanswered: Using a Calclated Total From a Form in a Report
I have a NewHouseMainF form that calculates orders for Builder/Subdivision/House Type. Each Builder/Subdivision has several House Types. I need a proposal form that will break down each Builder/Subdivision and show only a total figure for every House Type. (I also have a NewHouseFromR2 report that performs the same calculations as the NewHouseMainF.)
Each item needed in the order is made through a subform. Each item falls into one of three catagories, such as Material, Labor and Subcontract. Each of these items have been assigned a catgory and my calculations, break these items into the three sums using Iff statements. Then other calculations are performed on these sums, such as taxed amount and markup. I then want to do the proposal form and I don't want the figures that went into the calculation to be on this report. I only need the grand total amount for each Builder/Subdivision/House Type.
I have made the RoofProposalR report and made an attempt at entering =Forms!NewHouseMainF!GrandTotal in the control source of the field “Roof Price” but it returns #Name? Is there anyway to get the Grand Total figure from either the NewHouseMainF form or the NewHouseFromR2 report? Please say there is a "simple" solution!
Forms![Formname]![Control] is correct - I have a feeling that your problem lies in the fact that the form you're referencing is not open. Try opening the form and leaving it open and then open the report to see if the #Name problem is resolved. If it is, just use a bit of code to open the form prior to the report and hide it until the report is closed. It might not be the most elegant approach, but it should work.
Smacdonaldnc, you were exactly right. However now that it worked, it isn’t the results I was looking for. Right now it is putting the Grand Total of the open form in every House Type Grand Total.
The way my data is set up is that there is one Builder/Subdivision that has several House Types. What I am looking for is a report that will list Builder/Subdivision and all of the grand totals for each house type. For instance:
Builder: Freedom Builders
House Type Total
There is a separate NewHouseMainF for every Builder/Subdivision/HouseType. Is there another way that I can get the result above? I am a novice at Access and I know very very limited code. I have attached my database in the hopes that someone will be able to help me!
Take a look at the modified version of your database in the attachment below. I made 3 reports that I think are what you were trying to achieve, but not totally certain. Anyway, in the DatabaseWindow look to the right and there is a bar that says Groups Look there and you will see one group called BudsReports There you will have easy access to the reports that I created. Like I said that might not be what you were seeking but as best I can tell this time of morning that is it. If not come back and someone else may be able to assist you more if I can't.
Thank you Bud, but that isn’t quite what I’m looking for. The thing that is giving me the biggest headache is that my material list is broken into three categories, Material, which gets taxed, Labor which has a markup and then the Subcontract fee which gets added to the other three. I have done this with IIf statements on the footer of the subform. Ex.
Material - 20 Yr – 1 - $ 20.00- $20.00
Material – Caps – 2 - $10.00- $10.00
Subcontract-Bay W -1 - $15.00 -$15.00
Labor – SORV – 2 – $5.00 - $ 10.00
=Sum(IIf([CategoryId]=1,[TotalSum],0)) which yields $30.00
=Sum(IIf([CategoryId]=2,[TotalSum],0)) )) which yields $15.00
=Sum(IIf([CategoryId]=3,[TotalSum],0)) )) which yields $10.00
Then I reference these three amounts in the footer of the NewHouseMainF and perform the following calculations:
Material*1.045)) which yields $31.35
Labor*1.25 )) which yields $18.75
=sum MaterialTaxed+LaborTotal)) which yields $50.01
Markup*1.32)) which yields $66.13
This is the GrandTotal amount that I want in my Proposal form for every Builder/Subdivision/HouseType. I have a NewHouseFromR report that performs the same calculations. Is there Anyway to get the GrandTotal amount from either the form or the report on the proposal report in the design that I wrote in my first post?
I know this is confusing and I appreciate all of the help and patience because I am totally baffled.