SubReport subtotal 1

[matrixindicator]

In my form I have a calculated field that takes a subtotal form a subform. The subform is always visible.
I need the same construction also on my reports.
So I use a subreport to get a subtotal. The problem here is that if there is not a subtotal the subform is not showed and the calculated field in my report give then an #error. If there is a subtotal it works.

So I need to verify if there is a subform visible ? How can I verify that ? With an IIF statement, ok. But how to say if there is no subform visible just take a default value else take the subtotal or should I do something else.

 

[GingerR]

You could use DSum instead, and sum from the source table.

=DSum("FieldName","TableName","IDField = " & [IDField])

You could also put a query in instead of a table.

This will sum the field FieldName in the table TableName where the field IDField = the value that is in the IDField of the record you are on in the report.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[matrixindicator]

=IIf(IsNull([R01S_OPTIONS]![Subtotal]);[Price] & " " & Chr(128);[Price]+([R01S_OPTIONS]![Subtotal]) & " " & Chr(128))

A subreport has the property that if there are no data he does not show the subreport. When you are looking for data in the subreport you've got only a result when there are data. Otherwise he's is looking for a non existing object and you get
#error.

This is a bug I can't fix for the moment. I scanned the Internet and found out that this problem also exist by others but did not found a answer to this.

 

[GingerR]

Did you try my solution?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[matrixindicator]

yep, but the example show how to get a value from a table. The value I work with is a calculated field, I struggle with the definition to say what and where.

 

[matrixindicator]

Driving my car, I was thingking about this issue. I think I found a useable solution. I will test it later and tell you tomorrow if it works or not.

 

[dhookom]

You can use an expression in your main report like:

=IIf(MysbrptControl.Report.HasData, MysbrptControl.Report.txtTotal, 0)

You would need to replace the subreport control name and the control on the subreport (txtTotal) with your names.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[matrixindicator]

dhookom,

thx, I was a little desperate and spend a lot of time to find a solution to one of the last bugs of my application till you came up with this solution. IT WORKS, merry Christmas. I saw on the Internet the HasData method, but no ID I could use it.

=IIf([R01S_OPT].Report.HasData;[PRICE]+[R01S_OPT].Report.Subtotal;[PRICE]) & " " & Chr(128)