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