Textbox total of table

[LB79]

Hello everyone...

I have a form which has a subform containing a table based on a query.
On the main form I have a textbox that I want to show the total of a specified column in the subform table.

Ive tried various tips on the net but cant seem to get anything working.

Can anyone help?

Thanks

 

[wazz]

in the control source of your unbound textbox:

=DSum("yourfield","yourquery")

- make sure the query is the same one in the subform.

 

[LB79]

Thanks for the suggestion - Ive tried that but i get #Error

Do you know what the reason for this could be?

 

[fibayne]

Hi ..try this it works for me ..

Your subform text box should be in the subform footer

Make this the control source of your text box in the main from

=Nz([subformnamehere].[Form]![nameofcontrolinsubformthatholdsthetotalyouwant],0)

hope it helps

 

[LB79]

Thanks for that... is there anyway to have the textbox on the main form instead of the subform?

 

[fibayne]

Hi..the field that you do the calculation in on the subform, (you need to do the original calculation there unless someone knows otherwise..) is referenced on the main form by using ..

=Nz([subformnamehere].[Form]![nameofcontrolinsubformthatholdsthetotalyouwant],0)

which is the control source of the text box on your main form

hope this helps

 

[LB79]

Ah I see what you mean - understand that.
But im still having problems.
Ive put =DSum("Target","qryTarget") in the textbox on the sub form... but the value is returned as #Error still

 

[wazz]

what's the data type of your field?

 

[LB79]

Data type is standard

 

[wazz]

er...data type...text, number, datetime, currency?
what's the default view of the subform? single form, datasheet?

 

[LB79]

Sorry - Datatype of the txtbox is standard. The table field is Number.
Subform default view is datasheet

 

[wazz]

i think that would be the format.
anyway i'm stuck. i can't get mine to not work, and i can't get fibayne's to work (though i thought it would; i tried something like that first.)

at this point i can only think that something is misnamed, a field name or query or control name is misspelled.

or, try DSum again with the textbox on the main form. make sure the DSum part is in the Control Source property, not the Name property.

 

[micks55]

I had a problem like this but I only got Error when the sub had no records. This is what I do now.
On the footer of the SubForm there is a total box called ctlTotal (this is hidden as I want the total on the main form)
On the main form I have a hidden text box called DetailsCount.
In the OnOpen of the Subform I have;
Me.Parent!DetailsCount = Me.RecordsetClone.RecordCount
In the total field that is on the main form the ControlSource is;
=IIf([DetailsCount]>0,[SubFormName].[Form]![ctlTotal],0)

Hope that helps.
Mick