Sum() in Subform

[rnutts]

Hi

I have a subform (frmquotesubfomr) which is displayed on main form (frmquote). The subform shows all the records related to a field (enquiryref) on the main form.
So far so good, now what I want to do is total one of the fields (materials) displayed in the subform in the subformform footer. I have tried simply =Sum([materials]) but this returns #error.

Any help please

Richard

 

[boblarson]

You may have nulls in materials so try:

=Sum(Nz([materials],0))

 

[rnutts]

Still getting #error after entering the revised string

 

[boblarson]

Are you absolutely sure that you are putting in the correct field name? Also, sometimes it can give you fits if your text box name is the same as the field name. Changing the text box name to txtMaterials and saving (you'll then need to go back and change the code back to the original =Sum(Nz([materials],0)) because AutoCorrect will likely change it to =Sum(NZ([txtMaterials],0)) for you, which you don't want.

 

[rnutts]

Ok have checked the field name and it is typed correctly, the box name and the field name were the same, have made the changes you suggested but still no joy.
The field is also on the main form with the same record source but with a different text box name could this be making the difference??

 

[boblarson]

Okay, a point of instruction here. A subform should NOT have the same recordsource as its main form. A subform is really meant to enable a view of a one-to-many (and possibly a many-to-many, and even rarer still, but still possible a one-to-one) relationship. Or, you can have unlinked subforms on a main form to display DIFFERENT data.

But, if the recordsource is the same, then there shouldn't be a subform, all fields should just be on the main form. And that is probably your problem.

 

[rnutts]

Thanks

The sub form is showing a summary of the records with the same (enquiryref) from the main form. If a sub form is not the way for this, what would be the best way to display this information on the main form.

 

[boblarson]

A list box

 

[rnutts]

Thanks for that

I now have a list box which looks something like this

quote ref subref materials labour
1 a £500.00 £100.00
1 b £500.00 £100.00

Totalhere Totalhere

How do I now get the materials and labour to total at the bottom of the summary?

Thanks

 

[rnutts]

that was not how I typed the question

list box looks like this

quote ref------subref-----materials-----labour
1---------------a---------£500---------£500
1---------------b---------£500---------£500

--------------------------totalhere-----totalhere

How do I get the totals to show in the space where I have typed 'totalhere'

 

[Rich]

If you can have many items per quote then there should be a one to many relationship between quote ref and items

 

[boblarson]

If you can have many items per quote then there should be a one to many relationship between quote ref and items

And to build on that, you would have a table for quotes and a details table for the items. Then, you WOULD have the makings of a mainform/subform situation. How are you storing the multiple detail items right now?

 

[rnutts]

Currently the enquiry details (enquiry ref, client name, details, date of enquiry)are stored in the table 'tbleeaenquiry' and the costing details (materials, labour, overhead buildups) are stored in the table 'tbleeaquote'
The form (frmeeaquote) discussed was linked to table 'tbleeaquote'. The relationship between the two tables is the 'enquiryref' field which is stored in both.
There can be many records within 'tbleeaquote' with the same 'enquiryref' due to the need to breakdown an enquiry into manageable project parts.
The original point of the subform was to show the various project parts of the enquiry in one area, pulling records using the 'enquiryref' field, and also to total these so that the contracts manager would have an easy view as to estimated project costs.
Hope this sounds ok

Richard

 

[boblarson]

So in this case, your main form would be based on 'tbleeaenquiry' and your subform based on 'tbleeaquote' and then the Master/Child links should be on enquiryref and enquiryref should be a numeric ID field.

To get the totals for the subform then, you would put a text box in the footer with =Sum(Nz([materials],0)) and then =Sum(Nz([labour],0)) for the labour one.

Then, to display it on the main form, you can put a text box on the main form which will be
=[YourSubformContainerNameHere].[Form].[YourTotalsTextBoxNameHere]

 

[gray]

Hi

Access 2002
Win XPPro

I've effected this too... i.e. a Sum textbox in a subform and then a textbox on the main form pointing at it so it gets displayed on the main form. The subform box is summed via:-

=Sum(Nz([Item_Sub_Total],0))

Unfortunately, if there are no records for the subform-SUM to work on, the mainform textbox displays #error.. for other purposes I need it to be 0 or even NULL.

I've tried setting the defaults in both main and subform textboxes to 0 (zero) and even tried a test in the mainform textbox with various syntax a' la

=Iif(Forms![Subform]![Sum_Amount],Forms![Subform]![Sum_Amount],0)

but I still get #error or #name.

I notice that if I run my subform on it's own against a filter that I know has no records, the textboxes don't even appear in the form... not even as empty... so I suppose that's why the SUM can't run? Hmm... now how do I test for that in my mainform?

Any ideas anyone?