Form Help! (sbfrm to frm)

[Soulium]

So I have created a Database for Quotes. I have a MainForm (frmQuotesLine) where all the quote info is stored (e.g. Site Details, Customers details, remarks... etc) I then have a SubForm (SbfrmProductCosts) that is connected to the Mainform to enter details about related products to the Quotes. In the sub form I have "QuoteID, ProductID, Code, Description, Quantity, SalePrice", from that I have created a new text box to create a SubTotal for the Quantity*Sale Price and have set the settings name of the new text box to "SubTotal".

What I want to do is Sum the Subtotal textbox but have that text box on the main form instead of in the subform.

Please help as im struggling, im using Access 2003 if that helps. ^_^

Cheers,
Matt.

 

[DCrake]

Have the result on your subform as a hidden control but also have another control on your main form the references the control on the sub form.

 

[Soulium]

Sorry about this, I haven’t done Access since i was in A-level. Right, i created the Sum total in the subform and have named is "Grand Total". I have then created a new text box in my main form and in the control source have put in "Forms![Sbfrm ProductCosts]![Grand Total]". When I run this main form the Grand Total text box comes up with "#Name?". The sum is working in the subform.

 

[Rich]

Sorry about this, I haven’t done Access since i was in A-level. Right, i created the Sum total in the subform and have named is "Grand Total". I have then created a new text box in my main form and in the control source have put in "Forms![Sbfrm ProductCosts]![Grand Total]". When I run this main form the Grand Total text box comes up with "#Name?". The sum is working in the subform.

Use the code builder to obtain the correct syntax

 

[Soulium]

That is from the code builder

 

[M0E-lnx]

I also second this request... I need the same functionality for an ordering database.
I have a subform with details which contain a calculated field that shows the result of [PRICE] * [Quantity]
But I want to display a sum of these lines on the main form and not in the subform.

EDIT:
FWIW, I tried modifying my subform to add a textbox object and added this to it's Control Source property

=sum([Line Total])

But I get

#Error

on every instance of that object... any ideas why?

 

[Soulium]

I also second this request... I need the same functionality for an ordering database.
I have a subform with details which contain a calculated field that shows the result of [PRICE] * [Quantity]
But I want to display a sum of these lines on the main form and not in the subform

Great minds think alike

 

[boblarson]

Check out my sample that does this:
http://downloads.btabdevelopment.com/Samples/subforms/SAMPLE-SubformTotalOnMainForm.zip

 

[M0E-lnx]

Ok.. I tried this, but I guess that wont work for me

My [Line Total] field is a calculated field that depends on other things. This is how my field is calculated
It takes Qty, Price, U/M to get it's value. Where U/M is either EA, C or M.
so on my U/M field's afterupdate event I have a case statement that does thid

select case [U/M]
case M
iDiv = 1000
case C
iDiv = 100
case EA
iDiv = 1
END SELECT
[Line Total] = [qty] * ([price] / iDiv)

I thought I could just do a hidden field in the subform as suggested with a value of

=sum([Lite Total)]

But that gives me "#Error" like I said before.
So ovbiously linking to that field would be useless.
Can anyone think of another way to do this or point out what I'm doing wrong?

 

[Soulium]

Bah, im still getting "#Name?". But it must be something im not doing as yours works correctly. Thanks for the help that’s really nice of you to make us a sample. I'll keep cross referencing till I find out why its not doing what its supposed to do and post back if I crack it.

Thanks again.

My forms:

frmQuotes

Quote Detail subform

Feilds/Code

Quote Detail subform
Sub Total: "=[txtQuantity]*[txtSale Price]"

Total: ="=Sum([Quantity]*[Sale Price])"
(Which is my sum of sub totals)

frmQuotes
=[Quote Detail subform].Form!txtTotal

Cracked it!

Get rid of the spaces in your tables. So "Sale Price" in mine is now "SalePrice" - then change the txtbox names so they have no spaces. e.g
(it now reads like this)

Quote Detail subform
Sub Total: "=[txtQuantity]*[txtSalePrice]"

Total: ="=Sum([Quantity]*[SalePrice])"
(Which is my sum of sub totals)

frmQuotes
=[QuoteDetail subform].Form!txtTotal

 

[M0E-lnx]

seems mine is that one that wont work
Will keep trying

 

[boblarson]

seems mine is that one that wont work
Will keep trying

If you can't get it, perhaps posting it here will help.

How to Upload a Database to This Website

 

[M0E-lnx]

Ok.. I've tried renaming my subforms to kill spaces and stuff and I still the a "#Name?" on the textbox

I've managed to somehow get the sum([LineTotal]) to work. I dont even know how that happened, but the point is that it shows the right value now.

However, I'm unable to get this value on to the next parent form.
I'm posting the database here.

Look in the FrmPoRecords form and in the PoItemsDetails subform as these are the forms in question.

I have split this database, so I'm also posting the backend of it. (you may need to re-link the tables)

I tried uploading the stuff here, but I guess it's too big (2MB+) Here are some links to the files
http://www.2shared.com/file/3901799/28e2067f/db1.html
http://www.2shared.com/file/3901811/e5bc4378/db1_be.html

Please help

 

[boblarson]

Ok.. I've tried renaming my subforms to kill spaces and stuff and I still the a "#Name?" on the textbox

I've managed to somehow get the sum([LineTotal]) to work. I dont even know how that happened, but the point is that it shows the right value now.

However, I'm unable to get this value on to the next parent form.
I'm posting the database here.

Look in the FrmPoRecords form and in the PoItemsDetails subform as these are the forms in question.

Your problem is also a space. You currently have:
=[PoItems subform].Form!txtGrandTotal

But it should be

=[PoItemssubform].Form!txtGrandTotal

Your subform container name doesn't have the space.

 

[M0E-lnx]

Amazing!....
Thanks a bunch bob