Refer to a control in a sub report Access 97

Topic

New Reply

I have a report with a sub report in the details section of the main report that provides detail of items and the quantity. I would like to refer to the Quantity control of the sub report for a total. I tried =Sum([Report].[srptAmazonOrderDetails].[Quantity]) in the main report header but on opening the report I am asked for a value. If I place the expression in the page footer I am not asked for a value but it returns an error. I prefer the result to appear in the report header but I could add another step if I could get an accurate return in the page footer. What am I doing wrong?

Reply | Quote

Replies

I believe your issues relates to the structure of your expression – and it’s a common problem developers run into. Per KB 113352 the syntax should be
Reports![main report name]![subreport control name].Report![control name]
If that syntax doesn’t work for you, post back and we will try to help.

Reply | Quote

Thanks Wendell. I had tried that syntax. =Sum([Reports]![rptAmazonOrders]![srptAmazonOrderDetails].[Report]![Quantity]) I don’t get an error but I get nothing. If I drop the “Sum” I get a result of the first record which happens to be 1. The expression also returns 1 if I drop the name of the main report. The expression builder does not add the name of the main report but like the article you referred me to, the help file adds the main report name so I had tried that.

Reply | Quote

Thanks Wendell. I had tried that syntax. =Sum([Reports]![rptAmazonOrders]![srptAmazonOrderDetails].[Report]![Quantity]) I don’t get an error but I get nothing. If I drop the “Sum” I get a result of the first record which happens to be 1. The expression also returns 1 if I drop the name of the main report. The expression builder does not add the name of the main report but like the article you referred me to, the help file adds the main report name so I had tried that.

You need to put the control with the SUM in the subform, possibly hidden (or visible) in a section footer. Then, in the main form, you just refer to the control as Wendell mentioned. You cannot SUM the values in the subform from the main form. All you can do is reference the SUM that the subform performs.

Reply | Quote

OK James, we are getting closer. I now get a return but no a correct return. On the sub report, when viewed by itself, the total is correct, but the total is not correct when viewed in the report. At least the syntax part of the problem is corrected and I thank you guys for that. I am sure it is something simple…it always is with me.

Reply | Quote

We jumped a few hoops but got it. In the sub report I placed a text box named [UnitSum] in the details section with the expression =Sum([Quantity]). In the main report I placed a text box in the details section with the expression =[Reports]![rptAmazonorders]![srptAmazonOrderDetails].[Report]![UnitSum] with the running sum property set to over group. Then I referred to that text box in my date footer and returned the correct total.

Thanks for helping.

Reply | Quote