[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Access 2003 - Sub Report Problems?

I have a report and I have to add two subreports to it. However when I run the report it shows the main report and when it comes to displaying the sub report it show the title of the subreport but that is it? I have the subreport in the report footer, does this make a difference on the display issue. I checked the properties to ensure that everything was set to visible and the link is fine too no issues. I don't get any errors when I run the report so I am not too sure why this is happen. One thing to note is that my sub report does ask for a parameter which is the date. The main report has nothing to do with this date but the subreport has to return info on that date enter. I hope I explained my problem clear enough. Has anyone got any ideas?
Thanks in advance for all the help.
0
sabrina_spillane
Asked:
sabrina_spillane
  • 22
  • 16
  • 2
2 Solutions
 
LenaWoodCommented:
Your subreport isn't showing any data because it isn't linked to anything in your report.

For example, if I had a report that held employees names, and my subreport was their address, I would have to put the subreport in the detail section to see the addresses.  The report footer only happens one time...at the end of the report, but the information that I would be wanting (the addresses) needs to happen for each record in the report itself.

Try moving the subreport to the detail section of your main report.  If there is something in your main report that is linked to the subreport, make sure this link is present in your Parent and Child links in the preferences of the subreport (this will be found when clicking on the subreport on the main report and seeing the preference for the subreport - not in the subreport itself).

Hope that helps!
Lena
0
 
sabrina_spillaneAuthor Commented:
Hi Lena,
I have the Parent and Child link set up it is a unique identifier that both reports contain. However I don't want the subreport to output results for every record in the main report. I want it to just output the one time at the end of the report. Technically these should be two reports but i am going from a hard copy here and this is the first time they want to put it electronically and they would like to keep the format the same. What I will be doing alright in the end of the whole lot is comparing the total results and finding the difference between these fields. I haven't even got that far yet. When I did but the sub report in the detail it did exactly what you said it repeated for every field in the main report however it never displayed anything, not too sure why that was. It just displayed the title of the subreport again when i did that. But that is not what I want anyway.
Any other ideas.
0
 
LenaWoodCommented:
Since I don't know what your report is on, I will continue with my thought of using Employees.

I would have a report where I have a header for my Employee (on the main report).  Since I have a primary key set up, I would base my header on that field. (You can find this in the Sorting and Grouping Window).

If I have information on my main report that I want to display for the Employee (such as department and etc), I could also place this information in the header I just created.

Then in the detail section I would place my subreport.

As for why you are not showing any information in your subreport...do you show anything when you run just the subreport?  Are you sure there is information to show up?  Make sure that your subreport is sized big enough that it will show information.  It may even have a Can Grow property....make sure it is set to yes.

Lena
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
sabrina_spillaneAuthor Commented:
Hi Lena,
Sorry about all this. OK so I changed it to the detail section of the main report however it is prompting me to enter the date for every record, and after entering in so may it now send me and error message saying that it is too complex. It was doing that before. The last time I had it in the detail section of the main report it did promt me for the date for every record but it only showed the main report but never showed me anything for the subreport.

Here is a brief discription on what my report is about, it might give you more of an idea on where i am coming from.

My main report contains Product ID, Company Name, Product Name and Activity Date for that product and a count fields for how many products there are for different methods.

Then my sub report contains Product ID, Company Name, Product Name and Activity Date. It does sound like the same thing and it is very alike, however, in the sub report i prompt the user for the date that they want to return information for. It doesn't matter what date you enter as i have it also set to return all detail for a month so i use a between method here. So from the date the user enters between that date and the beginning of the month to the end of the month display all information. When I run the query for the sub report on its own, there is no problem, and it doesn't matter what date i enter in for it.
This sub report is to just output monthly infromation. However the main report has to output all information that exists and then i need to do a comparsion between that information and the monthly information but i haven't gotten this far yet?

I hope this makes it a bit clearer on where i am coming from. What do you think, have you any other ideas.
Thanks for all your help so far.

0
 
LenaWoodCommented:
There are probably many ways to deal with the problem of it asking you for the date so many times.  How I have dealt with that in the past is to either create a form that holds the date, or you can create an global variable that would hold the date value entered.

In a module you could put:

Public DateToStartFrom as Date

Then on the on open event of your form you could do something like:

DateToStart = InputBox ("Enter a date")

Then in the queries that run the subforms, put DateToStart as the Criteria.

I hope this helps you out.
Lena
0
 
sabrina_spillaneAuthor Commented:
So you are saying to create a Module that contains this information?

I have never created a module before, can you send me one steps in which to follow? At this stage i would try anything to get this going, it is driving me crazy. Thanks again for all the help. And when i do create this module with a data variable for users to enter the date in will i change the present query that already prompt the user for the date or will i leave it alone.
0
 
sabrina_spillaneAuthor Commented:
Or how would I use the global variable that you were talking about?

And if I used the form, would this form be like a pop up when the user would click on the report or something like that? And then how would it work from there?
Thanks again for all the help.
0
 
LenaWoodCommented:
OK...in access go to the modules section on the database window (at the bottom of the list where tables, queries and etc are).

Click on New

Type:

Public DateToStartFrom as Date

Now click on the disc icon at the top and give your module a name.  Something like modPublicVariable.

Now go to your main report (in design view)

On the events tab of your report, double click on the On Open Event.  It should now say "Event Procedure".  Now click on the 3 dots beside where it says Event Procedure.

Type:

DateToStart = InputBox ("Enter a date")

Now close your main form and save the changes.

I am assuming you know how to change your queries since you didn't ask for clarification on that (but will explain if I need too).

Now when you open your main report, you should get a message box that says:  "Enter A Date".

This will store that date in the variable DateToStart.  When you change your query for the criteria to be DateToStart instead of what you have in there now that prompts you for a date many times, it will use the date you entered.  It shouldn't ask you for it multiple times.

Hope this helps!
Lena
0
 
sabrina_spillaneAuthor Commented:
Should I be setting the event procedure for the main report to open that module or should i bet setting the event procedure for the sub report to open that module?
0
 
sabrina_spillaneAuthor Commented:
My main query for my main report doesn't need a date but for the sub report that query does request a date?
0
 
LenaWoodCommented:
You want the main report to have the code it in so that it fires before the subreports open and need that information.

Put the criteria in the query that supplies your subreport with the information.
0
 
sabrina_spillaneAuthor Commented:
Not too sure actually how to change query to allow for this. At the moment I am using a parameter should I think this out. I need to make sure that what ever date is entered it will retrieve everything for the month not just that particular date. Here is my query.

PARAMETERS [Enter Date] DateTime;
SELECT DISTINCT crpProducts.crpProduct_ID, tblCompany.CompanyName, crpProductTypes.crpProductType_Name AS [Product Type], crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate, Count(crpLicenseMethods.crpLicenseMethod_Description) AS Total
FROM ((crpProducts LEFT JOIN crpLicenseMethods ON crpProducts.crpProduct_crpLicenseMethodID = crpLicenseMethods.crpLicenseMethod_ID) LEFT JOIN crpProductTypes ON crpProducts.crpProduct_crpProductTypeID = crpProductTypes.crpProductType_ID) LEFT JOIN tblCompany ON crpProducts.crpProduct_CompanyID = tblCompany.CompanyID
GROUP BY crpProducts.crpProduct_ID, tblCompany.CompanyName, crpProductTypes.crpProductType_Name, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate
HAVING (((crpProducts.crpProduct_IsLicensed)=True) AND ((crpProducts.crpProduct_ActivityDate) Between DateSerial(Year([Enter Date]),Month([Enter Date]),1) And DateSerial(Year([Enter Date]),Month([Enter Date])+1,0)))
ORDER BY tblCompany.CompanyName, crpProductTypes.crpProductType_Name;
0
 
sabrina_spillaneAuthor Commented:
That is my sub report query
0
 
sabrina_spillaneAuthor Commented:
My main report query is as follows: there is alot of calulations in it

SELECT crpProducts.crpProduct_ID, comCompanies.comCompany_Name, crpApplicationTypeValues.AppTypeId, crpProductTypes.crpProductType_Name AS [Product Name], crpProductTypes.crpProductType_CRPNForm, crpLicenseMethods.crpLicenseMethod_Description, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate, Count(IIf([crpLicenseMethod_Description] Like '*CRP-B*',[crpLicenseMethod_Description])) AS CRPB, Count(IIf([crpLicenseMethod_Description] Like '*CRP-A*',[crpLicenseMethod_Description])) AS CRPA, Count(IIf([crpLicenseMethod_Description] Like '*CRP-L*',[crpLicenseMethod_Description])) AS CRPL, Count(crpLicenseMethods.crpLicenseMethod_Description) AS Total, Count(IIf((([AppTypeId]=1) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP5, Count(IIf((([AppTypeId]=2) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP6, Count(IIf((([AppTypeId]=4) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP10, Count(IIf((([AppTypeId]=3) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP8
FROM ((crpProducts LEFT JOIN comCompanies ON crpProducts.crpProduct_comCompanyID = comCompanies.comCompany_ID) INNER JOIN (crpProductTypes INNER JOIN crpApplicationTypeValues ON crpProductTypes.crpProductType_CRPNForm = crpApplicationTypeValues.AppTypeId) ON crpProducts.crpProduct_crpProductTypeID = crpProductTypes.crpProductType_ID) INNER JOIN crpLicenseMethods ON crpProducts.crpProduct_crpLicenseMethodID = crpLicenseMethods.crpLicenseMethod_ID
GROUP BY crpProducts.crpProduct_ID, comCompanies.comCompany_Name, crpApplicationTypeValues.AppTypeId, crpProductTypes.crpProductType_Name, crpProductTypes.crpProductType_CRPNForm, crpLicenseMethods.crpLicenseMethod_Description, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate
HAVING (((crpProducts.crpProduct_IsLicensed)=True))
ORDER BY comCompanies.comCompany_Name;
0
 
LenaWoodCommented:
Where you have [Enter Date] should be the variable we set up.

So change

[Enter Date]

to

DateToStart

Since you don't have any criteria in the main report query, you don't need to change anything in it.

Lena

0
 
sabrina_spillaneAuthor Commented:
I get an error when I run the report at the moment here is what it says:

"The expression On Open you entered as the event property setting produces the following error:
The object doesn't contain the Atuomation object 'DateToStart'...."

Any ideas what I might have done to cause this?
0
 
LenaWoodCommented:
No...I have no idea.

Looking around EE I found something similar.  The accepted answer was:

Can you create a blank database and then import all of the objects (tables, queries, etc).  If there are custom toolbars and import specs, click the options button on the import screen and select them as well.

If this doesn't work, we will try the form idea.
0
 
sabrina_spillaneAuthor Commented:
I think maybe we should try the form idea if you don't mind. Create a blank database I think would probably be a nightmare as the current one is a mess as it is! So where do we go with the form idea.
And thanks again for all the help.
0
 
LenaWoodCommented:
OK.

Forget everything we did before...starting over.

You will need an unbound form (just create and new form and have no control source)

On this form you will need an unbound text box (again no control source) and set the format of this text box to a date type (Format is on the preference dialog screen).  Name this text box something like datDateEntered.  (I am not real creative with my naming).

Put a button on this form.  Name it cmdOpenReport (this is how you will open the main report, you can use the wizard to create this button if you want).

Save this form as frmReportDate.

In the criteria in the query for the subreport (under the field you are wanting to filter on - where you had [Enter Date] before put:

Forms!frmReportDate.datDateEntered

Now as long as someone enters a date, you should get a report with the information for that date (or month as you have it set up).  If they leave the field blank, you will get an error...but making sure there is a date in the field is an easy fix.

Hope this helps get this working - I know how frustrating it can be.
Lena

0
 
sabrina_spillaneAuthor Commented:
Ok in the command button under the OnClick event I put the name of the main report which it is suppost to open but i am getting an error so i persume that is not how i set a command button up to open a report bu just putting the name of the report into the property of the onclick event? What should i put in there instead? At this stage I am not too sure what I am doing.
I also change the sub query to the following, is this what you meant?

PARAMETERS Forms!frmReportDate.datDateEntered DateTime;
SELECT DISTINCT crpProducts.crpProduct_ID, tblCompany.CompanyName, crpProductTypes.crpProductType_Name AS [Product Type], crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate, Count(crpLicenseMethods.crpLicenseMethod_Description) AS Total
FROM ((crpProducts LEFT JOIN crpLicenseMethods ON crpProducts.crpProduct_crpLicenseMethodID=crpLicenseMethods.crpLicenseMethod_ID) LEFT JOIN crpProductTypes ON crpProducts.crpProduct_crpProductTypeID=crpProductTypes.crpProductType_ID) LEFT JOIN tblCompany ON crpProducts.crpProduct_CompanyID=tblCompany.CompanyID
GROUP BY crpProducts.crpProduct_ID, tblCompany.CompanyName, crpProductTypes.crpProductType_Name, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate
HAVING (((crpProducts.crpProduct_IsLicensed)=True) And ((crpProducts.crpProduct_ActivityDate) Between DateSerial(Year(Forms!frmReportDate.datDateEntered),Month(Forms!frmReportDate.datDateEntered),1) And DateSerial(Year(Forms!frmReportDate.datDateEntered),Month(Forms!frmReportDate.datDateEntered)+1,0)))
ORDER BY tblCompany.CompanyName, crpProductTypes.crpProductType_Name;
0
 
sabrina_spillaneAuthor Commented:
i set up a marco so that will open it in print preview for me
0
 
sabrina_spillaneAuthor Commented:
that will open the main report with out any problems however the sub report is showing up like when we started. It is just showing the title and that is it nothing else, and i know for the month of march alone there is 15 resutls because i ran the query by itself. I must be driving you insane at this stage because i am even going off the head!
0
 
LenaWoodCommented:
OK...you will have to call the macro from your command button then instead of using the code I showed you above to open the report.

Lena
0
 
LenaWoodCommented:
Do you still have your report in the detail section?
0
 
sabrina_spillaneAuthor Commented:
I am calling the macro from the command button properties under the onclick property. and it will bring up the main report but will only bring up the title of the subreport.
0
 
LenaWoodCommented:
Where is your subreport located at (in the main report)?  What happens when you run just the subreport?  (Open the form we just created.  Enter a date.  Now open the subreport - don't use the button to open the report.

Lena
0
 
sabrina_spillaneAuthor Commented:
When i just double click on the report it will prompt me to enter the date by "Forms!frmReportDate.datDateEntered" and i entered in the date i have been entering in all day and it return the 15 rows that i expect it to return. so that is fine.
In the main report i had the sub report in the detail section of the main report however it was just returning the title of the report not the detail and it was returning it for every row which i don't want. What i want it to do is just output the sub report and the very end of the report as its own report really only on the same page as this thats all.
I have check the CanGrow property and it is set to Yes and everything is set to Visible True.

Then i also tried a moved the subreport to the main report footer and at least it didn't return results for every row in the main report it only return once which i want but it only showed the title again and none or the actual results. I am not too sure what to do or try next.
0
 
LenaWoodCommented:
I am at a loss myself.

Let me pose a question.  Can it be a seperate report?  What if we made your one single button print both reports at once..this would make it appear to be part of the first report.
0
 
sabrina_spillaneAuthor Commented:
I like that plan.
Is there away of doing this and printing it in the one page because you see there is another issue with this also. I do need to know the total of the sub report for and have it be calculated in the main report. What i mean by this is in the main report i will also need to calculate how many products were created for this month or the for the date they entered. the sub report displays this information and has a total number which is all i want for the main report but the sub report has to contain all the other info for them.
Maybe what i could do is have an extra field in the query of the main report that calculate the number of enters for the date that was enter, would that be possible to do? At the same time i can not restrict the main report to just that date, it would have to be only that column/ field i added that would be give me a sum of all the product that were assciated for the date that was entered? What do you think.

This is my main report query at the moment:

SELECT crpProducts.crpProduct_ID, comCompanies.comCompany_Name, crpApplicationTypeValues.AppTypeId, crpProductTypes.crpProductType_Name AS [Product Name], crpProductTypes.crpProductType_CRPNForm, crpLicenseMethods.crpLicenseMethod_Description, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate, Count(IIf([crpLicenseMethod_Description] Like '*CRP-B*',[crpLicenseMethod_Description])) AS CRPB, Count(IIf([crpLicenseMethod_Description] Like '*CRP-A*',[crpLicenseMethod_Description])) AS CRPA, Count(IIf([crpLicenseMethod_Description] Like '*CRP-L*',[crpLicenseMethod_Description])) AS CRPL, Count(crpLicenseMethods.crpLicenseMethod_Description) AS Total, Count(IIf((([AppTypeId]=1) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP5, Count(IIf((([AppTypeId]=2) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP6, Count(IIf((([AppTypeId]=4) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP10, Count(IIf((([AppTypeId]=3) And (crpLicenseMethods!crpLicenseMethod_Description Like '*CRP-n*')),[AppTypeId])) AS CRP8
FROM ((crpProducts LEFT JOIN comCompanies ON crpProducts.crpProduct_comCompanyID = comCompanies.comCompany_ID) INNER JOIN (crpProductTypes INNER JOIN crpApplicationTypeValues ON crpProductTypes.crpProductType_CRPNForm = crpApplicationTypeValues.AppTypeId) ON crpProducts.crpProduct_crpProductTypeID = crpProductTypes.crpProductType_ID) INNER JOIN crpLicenseMethods ON crpProducts.crpProduct_crpLicenseMethodID = crpLicenseMethods.crpLicenseMethod_ID
GROUP BY crpProducts.crpProduct_ID, comCompanies.comCompany_Name, crpApplicationTypeValues.AppTypeId, crpProductTypes.crpProductType_Name, crpProductTypes.crpProductType_CRPNForm, crpLicenseMethods.crpLicenseMethod_Description, crpProducts.crpProduct_IsLicensed, crpProducts.crpProduct_ActivityDate
HAVING (((crpProducts.crpProduct_IsLicensed)=True))
ORDER BY comCompanies.comCompany_Name;
0
 
LenaWoodCommented:
No, it will have to print on two pages.  I am afraid I am at a loss as to how to make this work for you.  Sorry I couldn't be more help.  Maybe someone else has some answers for you.

I am going to post a pointer question to yours so that maybe we can get some others in here helping you out.

Lena
0
 
LenaWoodCommented:
I posted a question asking for someone else to help me help you :-)

http://www.experts-exchange.com/Databases/MS_Access/Q_21852901.html

I must be over looking something, this seems like it should be easy.

Lena
0
 
sabrina_spillaneAuthor Commented:
Thank you so much Lena for sticking with this long I really appriciate it. Something will come up if not I will make them go back to the drawing board, if I can at all. Thanks again.
0
 
sabrina_spillaneAuthor Commented:
I know what you mean! I keep saying to myself its AccessReports it can't be this bad.
0
 
harfangCommented:
Hello sabrina_spillane

I answered Lena's call, so here is some "fresh insight" ;)

If I understand this correctly, you basically print two lists of products, one global and one filtered, and you want both lists as one single report. The first quite natural idea would be to create both, test them, and then include them as two subreports on one main "wrapper" report -- basically a single detail section with two subreports, or a report header and report footer for more control of a page break between them. A blank report (without record source) has three sections printed one after the other: header, detail, and footer. This is ususally fine for creating a composite report.

You can also include one of them as subreport in the other, but naturally only in the report header or report footer  sections. When you do that, however, make sure that the subreport's link fields are not automatically assigned by Access, which would happen if both lists are based on the same tables, at least most of the time. Open the subreport control's property sheet, and look for "Link Child Fields" and "Link Master Fields". In your case, they should be blank, so that the subreport prints just like it does when opened alone.

If you need to extract some information from a subreport, you should use only those controls defined in the subreports header or footer. These are normally available using:

    = <subreport control name>!<control name>

This allows to compare the results from a subreport with results in the main report, or those from two subreports.

Finally, the parameter.

When run as a subreport, the parameter(s) can be taken from the main report. This means that if the name of a  parameter is the same as  the name of a control on the main report, that value is taken instead. Likewise, the main form can get the value of a parameter from the subreport, just like it was another field.

But let's start with the main problem first: why doesn't your subreport show any data? If it does when opened alone and if there are no link fields, it should be identical when used as subreport...

Cheers!
(°v°)
0
 
LenaWoodCommented:
Harfang, you are the hero of the day!  Thanks!

Lena
0
 
sabrina_spillaneAuthor Commented:
You are a life saver, well both of ye are thank you guys so much. I don't beleive this.
It was the links between the sub report and the main report that was causing the problem all day long! I am going off the head.
Thanks again.
0
 
sabrina_spillaneAuthor Commented:
Just a quick question while you are in this mode!
I will need to get the value of one of the sub report and compare it with the total of the main report have you any ideas on this? Thanks again, ye are both great.
0
 
LenaWoodCommented:
Harfang, please post a comment in the other question so I can close it.  Thanks :-)
0
 
harfangCommented:
Hi,

Thank you for your kind words, both of you!

sabrina_spillane,

As I said, the data from a subreport's header or footer is available from the main report. Well, the data from the last detail section should be as well, but let's not go that way.

Let's say your subreport *control* is called subList. It doesn't matter what the name of the subreport is, only of the subreport control. Let's also say that you have a control called "txtGrandTotal" that you want to display on the main report, or compare with txtOtherTotal on the main report. Use:

    = subList!txtGrandTotal
    = subList!txtGrandTotla / txtOtherTotal

Technically, I'm not sure when exactly the information from the subreport becomes available. However, if used within the same section as the subreport itself, all should be well.

Good luck!
(°v°)
0
 
sabrina_spillaneAuthor Commented:
Thanks again to both of ye for giving me a hand on this one.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 22
  • 16
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now