If all Sub Reports are null dont show Parent

I have a Parent Report that contains individual information such as name, 
director, manager, dept.  It also has 6 Sub Reports in it linked by employee 
ID, These different Sub Reports are for Phones, Computers, Pages and such.  I 
have it set so if the sub report is null it does not show but the other sub 
reports will show their data.

Now what I would like to do is IF all of the Sub Reports are null don’t show 
that employee on the parent report.

If possible and someone could point me in the right direction it would be 
appreciated.

TIA

0
Utf
9/6/2007 5:12:01 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
554 Views

Similar Articles

[PageSpeed] 1

Steve_A wrote:

>I have a Parent Report that contains individual information such as name, 
>director, manager, dept.  It also has 6 Sub Reports in it linked by employee 
>ID, These different Sub Reports are for Phones, Computers, Pages and such.  I 
>have it set so if the sub report is null it does not show but the other sub 
>reports will show their data.
>
>Now what I would like to do is IF all of the Sub Reports are null don�t show 
>that employee on the parent report.


You can add some code to the detail section's Format event
procedure:

Cancel = Not (Me.subr1.Report.HasData _
						Or Me.subr2.Report.HasData _
						Or Me.subr3.Report.HasData _
						Or Me.subr4.Report.HasData _
						Or Me.subr5.Report.HasData _
						Or Me.subr6.Report.HasData)

-- 
Marsh
MVP [MS Access]
0
Marshall
9/6/2007 7:11:49 PM
Thanks Marshall

I tried it two ways first time I put this in the event procedure class 
module and get a Compile error: Syntax error
--------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData_
or
Me.SubCallingCardRpt.Report.HasData_
or
Me.SubCellPhonesRpt.Report.HasData_
or
Me.SubMDSRpt.Report.HasData_
or
Me.SubPagerRpt.Report.HasData_
or
Me.SubRemoteRpt.Report.HasData)

End Sub
-----------------------------------------------------------------

and then I put this in the On Format line as  a single string

Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData or 
Me.SubCallingCardRpt.Report.HasData or Me.SubCellPhonesRpt.Report.HasData or 
Me.SubMDSRpt.Report.HasData or Me.SubPagerRpt.Report.HasData or 
Me.SubRemoteRpt.Report.HasData)

and got this message
Microsoft Access can't find the macro 'Cancel=Not (Me.'

so did I missunderstand that the _ means continued on next line?
or 
does Access 97 not understand the Me.xxxx  statment?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"Marshall Barton" wrote:

> Steve_A wrote:
> 
> >I have a Parent Report that contains individual information such as name, 
> >director, manager, dept.  It also has 6 Sub Reports in it linked by employee 
> >ID, These different Sub Reports are for Phones, Computers, Pages and such.  I 
> >have it set so if the sub report is null it does not show but the other sub 
> >reports will show their data.
> >
> >Now what I would like to do is IF all of the Sub Reports are null don’t show 
> >that employee on the parent report.
> 
> 
> You can add some code to the detail section's Format event
> procedure:
> 
> Cancel = Not (Me.subr1.Report.HasData _
> 						Or Me.subr2.Report.HasData _
> 						Or Me.subr3.Report.HasData _
> 						Or Me.subr4.Report.HasData _
> 						Or Me.subr5.Report.HasData _
> 						Or Me.subr6.Report.HasData)
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
9/6/2007 9:32:04 PM
Insert a blank space just to the left of the underscore character at the end 
of each line, and don't leave the 'or' code by itself on a line:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData _
or Me.SubCallingCardRpt.Report.HasData _
or Me.SubCellPhonesRpt.Report.HasData _
or Me.SubMDSRpt.Report.HasData _
or Me.SubPagerRpt.Report.HasData _
or Me.SubRemoteRpt.Report.HasData)

End Sub

-- 

        Ken Snell
<MS ACCESS MVP>



"Steve_A" <allen.stATverizon.net.huh> wrote in message 
news:CA13D40A-2411-4CBD-BE77-0104A0ADBCCC@microsoft.com...
> Thanks Marshall
>
> I tried it two ways first time I put this in the event procedure class
> module and get a Compile error: Syntax error
> --------------------------------------
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>
> Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData_
> or
> Me.SubCallingCardRpt.Report.HasData_
> or
> Me.SubCellPhonesRpt.Report.HasData_
> or
> Me.SubMDSRpt.Report.HasData_
> or
> Me.SubPagerRpt.Report.HasData_
> or
> Me.SubRemoteRpt.Report.HasData)
>
> End Sub
> -----------------------------------------------------------------
>
> and then I put this in the On Format line as  a single string
>
> Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData or
> Me.SubCallingCardRpt.Report.HasData or Me.SubCellPhonesRpt.Report.HasData 
> or
> Me.SubMDSRpt.Report.HasData or Me.SubPagerRpt.Report.HasData or
> Me.SubRemoteRpt.Report.HasData)
>
> and got this message
> Microsoft Access can't find the macro 'Cancel=Not (Me.'
>
> so did I missunderstand that the _ means continued on next line?
> or
> does Access 97 not understand the Me.xxxx  statment?
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> "Marshall Barton" wrote:
>
>> Steve_A wrote:
>>
>> >I have a Parent Report that contains individual information such as 
>> >name,
>> >director, manager, dept.  It also has 6 Sub Reports in it linked by 
>> >employee
>> >ID, These different Sub Reports are for Phones, Computers, Pages and 
>> >such.  I
>> >have it set so if the sub report is null it does not show but the other 
>> >sub
>> >reports will show their data.
>> >
>> >Now what I would like to do is IF all of the Sub Reports are null don't 
>> >show
>> >that employee on the parent report.
>>
>>
>> You can add some code to the detail section's Format event
>> procedure:
>>
>> Cancel = Not (Me.subr1.Report.HasData _
>> Or Me.subr2.Report.HasData _
>> Or Me.subr3.Report.HasData _
>> Or Me.subr4.Report.HasData _
>> Or Me.subr5.Report.HasData _
>> Or Me.subr6.Report.HasData)
>>
>> -- 
>> Marsh
>> MVP [MS Access]
>> 


0
Ken
9/6/2007 9:42:29 PM
Thanks Ken, amazing what a few spaces in the right spots will do.  Makes for 
a much cleaner report.

You and Marshall rock

"Ken Snell (MVP)" wrote:

> Insert a blank space just to the left of the underscore character at the end 
> of each line, and don't leave the 'or' code by itself on a line:
> 
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> 
> Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData _
> or Me.SubCallingCardRpt.Report.HasData _
> or Me.SubCellPhonesRpt.Report.HasData _
> or Me.SubMDSRpt.Report.HasData _
> or Me.SubPagerRpt.Report.HasData _
> or Me.SubRemoteRpt.Report.HasData)
> 
> End Sub
> 
> -- 
> 
>         Ken Snell
> <MS ACCESS MVP>
> 
> 
> 
> "Steve_A" <allen.stATverizon.net.huh> wrote in message 
> news:CA13D40A-2411-4CBD-BE77-0104A0ADBCCC@microsoft.com...
> > Thanks Marshall
> >
> > I tried it two ways first time I put this in the event procedure class
> > module and get a Compile error: Syntax error
> > --------------------------------------
> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> >
> > Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData_
> > or
> > Me.SubCallingCardRpt.Report.HasData_
> > or
> > Me.SubCellPhonesRpt.Report.HasData_
> > or
> > Me.SubMDSRpt.Report.HasData_
> > or
> > Me.SubPagerRpt.Report.HasData_
> > or
> > Me.SubRemoteRpt.Report.HasData)
> >
> > End Sub
> > -----------------------------------------------------------------
> >
> > and then I put this in the On Format line as  a single string
> >
> > Cancel = Not (Me.SubBasicVoiceRpt.Report.HasData or
> > Me.SubCallingCardRpt.Report.HasData or Me.SubCellPhonesRpt.Report.HasData 
> > or
> > Me.SubMDSRpt.Report.HasData or Me.SubPagerRpt.Report.HasData or
> > Me.SubRemoteRpt.Report.HasData)
> >
> > and got this message
> > Microsoft Access can't find the macro 'Cancel=Not (Me.'
> >
> > so did I missunderstand that the _ means continued on next line?
> > or
> > does Access 97 not understand the Me.xxxx  statment?
> > xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >
> > "Marshall Barton" wrote:
> >
> >> Steve_A wrote:
> >>
> >> >I have a Parent Report that contains individual information such as 
> >> >name,
> >> >director, manager, dept.  It also has 6 Sub Reports in it linked by 
> >> >employee
> >> >ID, These different Sub Reports are for Phones, Computers, Pages and 
> >> >such.  I
> >> >have it set so if the sub report is null it does not show but the other 
> >> >sub
> >> >reports will show their data.
> >> >
> >> >Now what I would like to do is IF all of the Sub Reports are null don't 
> >> >show
> >> >that employee on the parent report.
> >>
> >>
> >> You can add some code to the detail section's Format event
> >> procedure:
> >>
> >> Cancel = Not (Me.subr1.Report.HasData _
> >> Or Me.subr2.Report.HasData _
> >> Or Me.subr3.Report.HasData _
> >> Or Me.subr4.Report.HasData _
> >> Or Me.subr5.Report.HasData _
> >> Or Me.subr6.Report.HasData)
> >>
> >> -- 
> >> Marsh
> >> MVP [MS Access]
> >> 
> 
> 
> 
0
Utf
9/6/2007 10:22:00 PM
Reply:

Similar Artilces:

Fixed row and header report
Hi, I have a requirement to have a report - SQL 2005 Reporting services ... SQL output: ID DateId FiscalYear FiscalPeriod TotalBooksTaken ReturnedBooks Store 1 20101001 09/10 2010-07 989 756 Store 1 1 20101001 09/10 2010-07 456 343 Store 2 1 20101001 09/10 2010-07 1456 1343 Store 3 2 YTD 09/10 ...

Duplicating MainForm, SubForm and Sub-SubForm
Hello Everyone, I have an Order Entry Form composed of a MainForm, a SubForm and 2 Sub- Subforms. The SubForm is a continuous form. The two Sub-SubForms are children of a SubForm via a foreign key (which is the Primary Key of a SubForm). There are some instances when customers revised their order by changing just the amount of orders or by just removing one item and the rest are the same. To do this, I would like to keep the initial order (record) and create another record by duplicating the first order then change the amount (or remove an item whatever the case maybe) on the new record. The...

Outlook 2000 Sub folder
Hi I am using outlook 2000 and I have a created a subfolder in my inbox however I am unable to view the folder My Shortcuts on the left hand side of the screen where all my other sub folders are located. In the navigation bar at the top of my pane there is an icon called "move to Folder" and the folder is located here. I am unable to find the location of the folder therefore unable to open the folder or move it to "My shortcuts" I am in need of your expertise. I am ok with computers and can follow instructions so please please please help. Thanking you in ...

Report Issue
I have a report that prints all of the Employees Names for a group on a preprinted paper form. I am trying to duplicate the paper form in an Access 2003 report. All works great with a subform report with a block set up in columns and there are an even number of records. When there is an odd number of records I need either a blank record input or a few lines drawn on the subreport. HOW CAN I MAKE THIS HAPPEN based on the number of records in the query. -- Hansford D. Cornett ...

X Report Printing
Here's my problem... I am unable to print a X, Z or ZZ report from my office printer. It will print to the receipt printer but I would like to be able to print it from the office on 8.5 x 11 paper. Does anyone have a template for this or know how I could go about setting this up so it will work. Any help would be most appreciated. Thanks! In SO Manager, Database/Registers/Receipt Formats, create a new format - call it something like "Full Page". Under "Receipt and Report templates", the last entry is labeled "Report" - click the magnifying glass to ...

The Fill colour icon is not showing the colours
The fill colour icon is not allowing me to insert or remove colours In Control Panel | Accessibility, make sure you haven't selected one of the High Contrast display options. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Fill colour icon" <Fill colour icon@discussions.microsoft.com> wrote in message news:BCF0AE02-C81B-44F9-B719-D9A2E8E6B8B2@microsoft.com... > The fill colour icon is not allowing me to insert or remove colours > ...

Custom Report #4
Hi I am in the process of creating a custom report in Visual Studio. We are using CRM 4.0 and I chose not to use the report wizard as we require a more complex report. I have managed to create a simple report with our company logo and a table with details from the case entity. We need to create a service activity report which shows all the cases created for a specific customer during a specific time period. I have managed to make the report prefilterable and using the filter we can select the customer and the report will display a summary of each case logged for the customer. I now...

Active hyperlinks in Access 2007 Report
I am a long time Access user and I have a need to produce a report but within the report are hyuperlinks to PDF files that are on a web site. So, here is the scenario - a user has a database on their local machine and they can search the text of 1400+ different construction projects. They submit a search request and receive back a report of all the responses and within the answerset are hyperlinks to the actual PDF documents - but n the report output the hyperlinks are DEAD! Is there functionality to have LIVE hyperlinks?? Thanks in advance for any suggestions! ...

sub-process
What is the sub-process function in the workflow? How do you set one up? Why would you use it? A sub-process is a way to call a manual rule. If you create a manual rule in workflow called Rule A, in another rule you can insert a sub-process and select the manual rule to run. This is a helpful way to create loops and if/then rules that trigger other events. -- Matt Wittemann http://icu-mscrm.blogspot.com "David M" wrote: > What is the sub-process function in the workflow? > How do you set one up? > Why would you use it? > ...

Mac Word
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a way to show inserted comments using the balloons and deleted text in strikethrough? <br><br>If I click 'show balloons&quot; it show deleted text in balloons as well, which makes reviewing changes harder... <br><br>Any thoughts???? The only option I know of is with the Show button on the Reviewing Toolbar... Deselect the items for which you don't want to display balloons. HTH |:>) Bob Jones [MVP] Office:Mac On 2/7/10 12:45 PM, in article 59bb26a7.-1@...

E-Mail Report
I want to E-Mail a report. I know how to do that, but I don't want to send it as an attachement. How can I put the data of the report into the body of the E-Mail? Thanks DS ...

Spending by Category Report
I am using Money 2006 and trying to get a snapshot of my spending over the last 6 months by category. I am using the standard report for this, but for some reason I can't get it to show all my tranfers on the report. I have a lot of $ transferred each month to kids 529 accounts or IRAs and I want to see what my monthly "Grand Total" is including these transfers. I can't find any way to tell money to subtract these from the total in the reports. There is a "Transfer" category on the report, but the only thing it shows is a transfer for the principle paid o...

option to show or hide social security number throughout system
Now that identify theft is a big issue, a great feature in HR and/or UPR would be an option to hide or show SS# on windows and reports. I'm having to cusomize HR and Payroll reports just to remove the SS#. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://ww...

Reports Issues
> CRM and Reports are on the same box. SQL (named instance) is on a different > server. The following is true of our setup: > > -IE clients are setup to allow Kerberos Authentication > -We are not using Host Headers > -Everything is using the "Network Service" security account > -Our domain functional level is Windows 2003 > -CRM was installed by a domain admin > > When installing, I got an error message '...TrustForDelegationAction > Failed...' I was able to ignore the error and continue with the installation. > Now, when I am trying...

Sub, Private Sub, Public Sub
I got a different lap top and when I moved my Personal.xls from one to the other some of my macros were not on the list of macros when you go to tools>macros. Although when I look in the code they are there. I figured out that the ones that were "Private Sub" macros were no working. I also figured that when I changed them to just "Sub" they worked. I do know that if "It aint broke don't fix it, cus to try is usually to fix it till it is broke." But what is the difference between "Sub", "Private Sub", & "Public ...

Cascade current view to sub-folders
Hi, Is there any way I can set a custom View setting to a set of folders (e.g. set a view setting for a high-level folder and then have it cascade to all sub-folders)? By a custom View I mean the way that items in a folder shoud look - i.e. font settings and the like. Cheers, Stu ...

Fiscal Reporting Periods
Clients are having trouble printing financial statements for historical fiscal periods. Currently in GP 9 and 10, when creating a historical report in Advanced Financial (ie. Balance Sheet 2006), they must select the historical year and periods. Next year, they will have to change the historical year and period settings and save the report as Balance Sheet 2007. They feel that they should be able to create a historical report once, and save it (ie. Historical Balance Sheet). Whenever they would like to print a historical report, they simply select the appropriate year and print. -- ...

(Sub)totals
Hello everybody. I have the following problem: I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i need to count all the quantities within the same sector Sorting + AutoSum isn't an option, since the file has other calculations in it as well, that also depend on a sort. There's in my Dutch version a function 'DBSUM' and a function 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they match with English versions ... but could someone please tell me how these functions work, since i can't get them right (n...

Printing reports through VBA
A while ago, I was using the following 4 lines of code to print reports from an Access form: DoCmd.OpenReport ReportName, acViewPreview, , criteria, acWindowNormal On Error Resume Next DoCmd.RunCommand acCmdPrint DoCmd.Close acReport, ReportName, acSaveNo This allowed the windows Print box to be displayed so that the user could choose the appropriate printer and whatever print options he/she wanted. Now that we are using Access 2007, these 4 lines of code work about 80% of the time. The other 20% of the time, what prints out is a copy of the form on the ...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

Report showing which users have access to a particular window/repo
Is there a report that can be printed (or a particular table that can be looked at) that will list all users that have access to a particular window or report? We're on GP 10, upgraded from GP 9, so all the roles and tasks are CNV_..... In particular, we need a quick and easy way to see which users have access the the payroll check history report. Sure there is. Abreviated instructions follow but detailed ones can be found in our Dynamics GP Security Handbook on page II-38. Load the Security Resource Table first by going to Maintenance, select Clear Tables (BE CAREFUL)...

Formating Content on reports
I've tried following the steps in the SDK but am getting no where. How do I apply formating to numbers, dates ETC? I've added a dataset for "DSNumandCurrency" with a SELECT * FROM dbo.fn_GetFormatStrings() but can't seem to use anything. ...

Creating an a report from an indivual record in a table
Hi all, I've been searching for an answer to my question for a while and have got more and more confused as to the best way to approach it...I am a total novice I'm afraid... I have an Access 2003 database set up with one table with a unique ID for each person and numerous other fields. I have set up forms to collect data (about 6 in all). What I would like to do is to be able select a unque ID and pull almost all the fields in the table associated with it into a report. I would do this for each person when required. I presume I would have to set up a query first but h...

Report Email Automation
Hi, I am trying email a specific report to a customer on a specific day, is there an easy way of doing this as I have tried to do it through a workflow...does it actually need to been done in Reporting Services? Any help on this would be great. Youu should use Reporting Services mail delivery option to archieve your goal. Read the Reporting Services Books Online, you'll find how to prepare your server for mail delivery. -- Laszlo Kovacs MCSE+S, MCT, Microsoft Dynamics Certified Master "Patrickh" wrote: > Hi, > > I am trying email a specific report to a custom...