Calculated control in a report

[MNNovice]

Hi:

I have a report that's based on a query. This report has two sub reports which are also based on two separate queries.

Main report: rptECHO
Sub reports: srptEchoAP & srptEchoPay

srptEchoAP has all data pertaining to invoice payments for all ECHOs. (ECHO is a process through which expenses for a grant get reimbursed.) Similarly, srptEchoPay has data pertaining to payroll expenses.

I have sub total of AP and sub total of Payroll expenses on the respective sub reports. I need to show the Total ECHO expenses (AP + Payroll) at the bottom of the rptECHO.

How do I do it? Any help is much appreciated.

Thanks.

[NeoPa]

What are the names of the controls on your subreports?

What are the names of your subreport controls on the main report (You've already supplied the names of the reports themselves, contained within. This is not the question)?

[MNNovice]

Sub Report: srptEchoPayDetail
Control Name: txtPayAmount

Sub Report: srptEchoAPDetail
Control Name: txtAPNet

I need the total on rptECHO (main report)
txtAPNet + txtPayAmount

I tried this on the report footer. Didn't work. Perhaps somehow I need to refer to these text boxes to the sub report. This I don't know how to do.

Thanks.

[NeoPa]

Before I get a chance to look at this properly, you may want to take a look through Referring to Items on a Sub-Form. If you remember that most references to subforms should work very similarly for subreports, you should be able to work out the referencing.

If not then I'll try to take a proper look later to see if I can get it to work.

[NeoPa]

I've just checked over your answer in more detail & I notice you haven't answered my questions. I expect you haven't understood clearly, but please read again carefully and provide the requested information.

[MNNovice]

What are the names of the controls on your subreports?

Sub Report: srptEchoPayDetail
Control Name: txtPayAmount (Text67 on the version of DB you have)

Sub Report: srptEchoAPDetail
Control Name: txtAPNet (Text71 on the version of DB you have)

What are the names of your subreport controls on the main report (You've already supplied the names of the reports themselves, contained within. This is not the question)?

I don't have created a control (to calculate the total) for the subreport on the main report - yet (hope I understood your question).

If not, let me know.

[MNNovice]

I will read it and shall let you know. Thanks.

[MNNovice]

NeoPa:

I found this write up on the net and it solved my problem. However I succeeded only with the STEP 1. I would like to try the NZ formula part of it, may be tomorrow.

The controls of my subform on the main form are: PayExpenes and APExpenses

Thanks and have a nice evening.


Bring the total from a subreport back onto the main report
Your subreport has a total at the end - a text box in the Report Footer section, with a Control Source like this:

=Sum([Amount])

Now, how do you pass that total back to the the main report?
Stage 1
If the subreport is called Sub1, and the text box is txtTotal, put the text box on your main report, and start with this Control Source:

=[Sub1].[Report].[txtTotal]

Stage 2
Check that it works. It should do if there are records in the subreport. If not, you get #Error. To avoid that, test the HasData property, like this:

=IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)

Stage 3
The subreport total could be Null, so you might like to use Nz() to convert that case to zero also:

=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)

[NeoPa]

@MNNovice
When putting a subreport into another (main) report, the subreport is actually defined as a report. What makes it a subreport is that a control (a subreport control) is created on the main report. This control contains a report, which is what you generally refer to as the subreport. Technically however, this is not the subreport. The subreport is the control that contains the embedded report. The names of these (subform) controls are what I'm after.

[NeoPa]

@MNNovice
This is fundamentally what I was trying to get at (if you check the link I provided in post #4). Notice the code refers to the subreport control ([Sub1]).

What you need to do with this process is firstly to work out the names of all the elements you want to deal with. This I was trying to help with, with my request for names etc.

[NeoPa]

I need to ask why [Text67] is in ECHOID Footer, instead of the Report Footer? Do you want only the last ECHOID value?

PS. One of your subreport controls is called [A/P Expenses] if that helps.

[NeoPa]

It seems the other is called [rptEchoPaySum]. I may have run out of time for tonight though. I'll look again when I can.

[MNNovice]

NeoPa:

I got this issue resolved. Don't spend any of your time on this matter. Thanks.

[MNNovice]

I have updated the DB since the last copy that was posted. I learned about the control of a sub report once I read the article I posted on Posting #8.

"A/P Expenses" was rejected by ACCESS so I changed it to "APExpenses"

[NeoPa]

Thanks for updating M.

As for the names of your controls, I recommend finding some form of consistent naming convention so that you can always understand from the name what the item is about. Also, that you can know what the name should be simply by knowing what the control is for.

Does that make sense?

[MNNovice]

NeoPa:

Thanks for the tips. I usually try to follow the naming convention to the best of my ability. My tables are tblXXXX, query/ qryXXXX, report / rptXXXX Forms, frm, Subforms/sfrm....and for controls I use cmd (Command box), txt (text box). Majority of the time I don't name the labels. However, for subform's control - I didn't know how to name it.

Thanks.

[NeoPa]

That sounds quite sensible M :)

I would only suggest that the subform control is more important to name (as sfm or sfrm) than the form which is to be used as a subform (which is essentially just a form).

I would always recommend avoiding any and all punctuation characters in your names. Spaces included.

Otherwise you already have a very solid foundation :)

[MNNovice]

NeoPa:

I will remember your suggestions and will try to follow it. Thanks. M

[codicecrm]

@NeoPa
I've learned the value of spending quality time searching through old threads...........thank you for this understanding of naming controls - and that they are not necessarily equivalent to the name of your subreports. Oh how I love when the light bulb pops on! Many thanks! :)

[NeoPa]

I'm pleased you found it helpful, and thanks for posting your comments. It never hurts to hear things like that :)