Solved: Access Report removing sub-report when printing etc.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Hello.

I have a report, with 3 sub-reports in. It collects all the data for a particular SiteID. in Each of the sub-reports, there is a cost value. One for Hardware, one for Mobiles, and one for Phone lines. I have fields at the top of report which reflects the values and totals them up. Works great, except:
When i have a SiteID with one of the subforms being empty; i.e no mobiles on the site it displays fine in the report view, but when i go to print or publish as a pdf it strips out the subform with no value, and throws an error in my calculation. I need the calculation there, which means i need it to stop striping out the subform with no value. I however cannot see how to do this...its just bugging me, its fine inthe report view, just when you send it to print.

Would grateful for some help,

Thanks Mike :eek:
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Hello Mike, I think you will have to switch to VBA to do the calculations, or you could try using an IIF to check for the Null value.
It may also be possible to force the data to be printed by having a value in the query that is always there, something like a new column heading with
One: 1
which should show a field with the name One with a value of 1.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Hi OBP, i tried like you suggested in the query the report reflects too by putting in One: 1 however it only returns the value of 1 when there is a record there. If in the new column it will not until you type something into that record.

the idea with the IIF sounds good, how would you right the expression? I have tried
IIF([RptCostSummaryMobile]![Text16],0 ([RptCostSummaryMobile]![Text16]))

Text16 is the text box which displays the calculation in the subform.

In doing the expression i did above it just puts a '0' in all the time regardless if it has a value or not.

Cheers
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Have you looked at the Sub Report's "On No Data", this can be used to trigger an event when the Report has no data, for instance you could use
me.parent.Text16 = 0
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
That doesnt seem to do anything. The report is a filter, so is filtering the data for that site. Not sure if it makes any difference? I have attached a pic so you can see the report. The figures at the top are what i am having trouble with. 3.jpg is before the print, and 4.jpg is after
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
OK, are the queries supplying the Subforms using the Main Table?
If so change the Joins to include all records from the main table and only those from the sub table and then include a field (Key) from the Main table, that should create records even though they have no values from the subtables.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Yes lots of queries and tables. 7.jpg is the query the report and form run off, and 6.jpg is the query that filter report runs off. I dont know which ones i would need to do the joins on, and also the key value is the mobileUserID i guess? not really sure.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
In the Query QryMobile I would try setting all the joins from the TblMobileUserID to All records from that table and only records from the the other 3 tables.
On the filter query which is the dominant table, the office or the Mobile?
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
ive done the first part, id probably say QryMobile is the dominant in the filter query. So i have set the join to All Records from the query to the table, is that right? Then what would be the next step?

Cheers,
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Do you get records when you run them, especially for the ones where you didn't before.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Ive answered my own question in wether its right or not because it doesn't open, so i have done all records from the table to the query instead.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
i get the same when i run them, everything still works fine it creates the report as it should. Am i suppose to have the field in the Query 'One: 1' ?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
No, you should get all the records from the main table, but blank fields for the records missing from the sub tables, that is assuming that you have a field from the main table in the query.
 

Bradybunch

Thread Starter
Joined
Sep 10, 2009
Messages
211
Thats the bit that i dont get, the missing records bit. The records with no mobile phone are not missing, they are just not meant to be there. Sometimes i will have a site that has no mobile phones, or IT Hardware, only phone lines. So when the report is ran on the SiteID it will filter on what ever info is there. The example i gave you is a site that has no mobile devices to charge. In the report view the text boxes calculate and include the £0.00 values even though the £0.00 value is not there, but when i convert it, or print it...the error comes up like the pics in the other reply.

It is almost like the reports strips out any records which are not there to print it, which then gives the error...instead of just printing exactly what is on the screen, so it keeps the calculations. The total Cost box calculates the total mobile, phone and hardware cost per month. Take one of them out because of no figure, then it throws an error.

I dont know if that explains it any better? Thanks OBP
 

OBP

Joined
Mar 8, 2005
Messages
19,895
OK I get it now, it is there in Print Preview but not when printed.
You could try using Totalling queries for those 3 Fields on the main form.
As the problem is when printing perhaps looking at the Sub Reports Detail Section "On Print" event to enter your IIF function.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top