[AccessD] How to Suppress Main Report Line if the Related Sub-Report has no Data - THANKS!

Brad Marks BradM at blackforestltd.com
Tue Dec 17 12:02:56 CST 2013


Paul,

I now have a test Access application working nicely using the code you posted.

Thanks a lot, I really appreciate the assistance.

Brad  


-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Paul Hartland
Sent: Tue 12/17/2013 8:12 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to Suppress Main Report Line if the Related Sub-Report has no Data
 
Brad,

That was usually the simpilest solution I used to use, just to save messing
about with showing and hiding on the actual report, although I have done
similar things on the Access XP/2003 versions at an old place of work.  If
you want to try the skipping of the main report, im sure I used to do
something in the detail format of the main report similar to

If isnull(Reports![main report]![sub report].Report![fieldname]) then
  Cancel=true
Endif

Paul


On 17 December 2013 11:54, Brad Marks <BradM at blackforestltd.com> wrote:

> Paul,
>
> Thanks for the idea.  I had not considered this approach yet.
>
> I was simply trying to use the existing main report query and sub-report
> query and then do the main report detail line "suppression" at the report
> level rather than at the query level.
>
> I will dig into the idea that you suggested.
>
> Thanks again,
>
> Brad
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Paul Hartland
> Sent: Tue 12/17/2013 5:44 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] How to Suppress Main Report Line if the Related
> Sub-Report has no Data
>
> Brad,
>
> My first thought was couldn't you build a query that links the main data
> query into the sub data query and use that as the main report query, then
> every main report should have sub report detail and won't show the ones
> that don't.
>
> Paul
>
>
> On 17 December 2013 11:32, Brad Marks <BradM at blackforestltd.com> wrote:
>
> > Paul,
> >
> > Yes,  I would like to not show specific main report lines when the
> related
> > sub-report has no lines.
> >
> > I thought that this would be easy to do, but I have not be able to figure
> > this out after many attempts.
> >
> > Thanks,
> > Brad
> >
> > PS.  Here is the rest of the story...  We have an existing Access report
> > that works nicely.  Recently our users asked for a variation of this
> > report.  This variation involved the addition of a "Where" condition on
> the
> > sub-report's underlying query.  This works nicely, except that now there
> > are many lines on the Main report that do not have any sub-report lines.
> >  The users would like to not see these extra Main report lines.
> >
> >
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com on behalf of Paul Hartland
> > Sent: Mon 12/16/2013 10:18 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] How to Suppress Main Report Line if the Related
> > Sub-Report has no Data
> >
> > Brad,
> >
> > Am I clear on this that you don't want to show the main detail/report if
> > there is no sub detail/report data ?
> >
> > Paul
> >
> >
> > On 17 December 2013 01:37, Brad Marks <BradM at blackforestltd.com> wrote:
> >
> > > Rocky,
> > >
> > > Thanks for the help.
> > >
> > > Unfortunately, I seem to be having "senior moments" and I cannot even
> get
> > > a small test example to work.
> > >
> > > Perhaps you could post more details.
> > >
> > > For example, I am not sure how to obtain the record count of the
> > > sub-report.
> > >
> > > Thanks,
> > > Brad
> > >
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com on behalf of Rocky Smolin
> > > Sent: Thu 12/5/2013 10:26 PM
> > > To: 'Access Developers discussion and problem solving'
> > > Subject: Re: [AccessD] Need to Suppress Main Report Line if
> > > therelatedSub-Report has no Data
> > >
> > > I would use the format event of the detail section to get the record
> > count
> > > of the sub report and, if it's zero then
> > >
> > > Me.MoveLayout = False
> > > Me.NextRecord = True
> > > Me.PrintSection = False
> > >
> > > HTH
> > >
> > > Rocky
> > >
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> > > Sent: Thursday, December 05, 2013 5:17 PM
> > > To: Access Developers discussion and problem solving
> > > Subject: [AccessD] Need to Suppress Main Report Line if the
> > > relatedSub-Report has no Data
> > >
> > > All,
> > >
> > > I have a report that has one sub-report.  This sub-report is tied to a
> > > field
> > > in the detail lines of the main report.
> > >
> > > Recently, our users requested that if there is no data in the
> sub-report
> > > they would like to see the related line on the main report suppressed.
> > >
> > > I have never tried to do this before.  I have set up a little test
> Access
> > > application and tried a few ideas, but have not found a good solution.
> > >
> > > Is there a way to accomplish this with Access 2007?
> > >
> > > Thanks,
> > > Brad
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > This message was scanned by ESVA and is believed to be clean.
> > > Click here to report this message as spam.
> > > http://h0stname/cgi-bin/learn-msg.cgi?id=6A8F528AEB.B87B1
> > >
> > >
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > >
> >
> >
> > --
> > Paul Hartland
> > paul.hartland at googlemail.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > This message was scanned by ESVA and is believed to be clean.
> > Click here to report this message as spam.
> > http://h0stname/cgi-bin/learn-msg.cgi?id=5AF38285E3.8409B
> >
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> This message was scanned by ESVA and is believed to be clean.
> Click here to report this message as spam.
> http://h0stname/cgi-bin/learn-msg.cgi?id=C6A6A285E3.7AFDB
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>


-- 
Paul Hartland
paul.hartland at googlemail.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message was scanned by ESVA and is believed to be clean.
Click here to report this message as spam. 
http://h0stname/cgi-bin/learn-msg.cgi?id=CE89C285E3.7124A





More information about the AccessD mailing list