By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Calculated control in a report

100+
P: 418
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.
May 28 '09 #1

✓ answered by 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.

Share this Question
Share on Google+
19 Replies


NeoPa
Expert Mod 15k+
P: 31,709
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)?
May 28 '09 #2

100+
P: 418
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.
May 28 '09 #3

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 28 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 28 '09 #5

100+
P: 418
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.
May 28 '09 #6

100+
P: 418
I will read it and shall let you know. Thanks.
May 28 '09 #7

100+
P: 418
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:
Expand|Select|Wrap|Line Numbers
  1. =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:
Expand|Select|Wrap|Line Numbers
  1. =[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:
Expand|Select|Wrap|Line Numbers
  1. =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:
Expand|Select|Wrap|Line Numbers
  1. =IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)
May 28 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 28 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
@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.
May 28 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
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.
May 28 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
It seems the other is called [rptEchoPaySum]. I may have run out of time for tonight though. I'll look again when I can.
May 29 '09 #12

100+
P: 418
NeoPa:

I got this issue resolved. Don't spend any of your time on this matter. Thanks.
May 29 '09 #13

100+
P: 418
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"
May 29 '09 #14

NeoPa
Expert Mod 15k+
P: 31,709
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?
May 29 '09 #15

100+
P: 418
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.
May 29 '09 #16

NeoPa
Expert Mod 15k+
P: 31,709
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 :)
May 29 '09 #17

100+
P: 418
NeoPa:

I will remember your suggestions and will try to follow it. Thanks. M
Jun 1 '09 #18

P: 14
@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! :)
May 4 '10 #19

NeoPa
Expert Mod 15k+
P: 31,709
I'm pleased you found it helpful, and thanks for posting your comments. It never hurts to hear things like that :)
May 5 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.