Solved

How do I exclude the main report record from a sub-report?

Posted on 2013-12-29
20
446 Views
Last Modified: 2014-01-02
Good Morning Experts,
I've have an Access 2010 main report that will list, in a sub-report, family members.  I want to exclude the main report's family member.
How do I filter out the main report family member from the sub-report?  What property and code should I use?

Thanks in Advance
0
Comment
Question by:rlopez11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 6
20 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39744922
Assume main form a with record source table a (aid, adesc)
Sub form b with record source table b (aid, bid, bdesc, vExclude )
field vExclude default value 1

Record to be excluded from sub form, set vExclude=0, other records will have vExclude=1

Record source for b subform: Select * from b where vExclude  <> 0
0
 

Author Comment

by:rlopez11
ID: 39745423
hnasr,
Thanks for the response.  I posted a comment earlier this afternoon but now I don't see it.  Perhaps I was timed out when I pressed the submit button.

In any case, I'm sorry but I do not understand your proposed solution.  Can you please expound?

In an attempt to clarify:
Betty, Tom and Harry are a family.  The main report will have Tom's information.  A piece of informaiton on Tom's main report is his family members-- this is where the the family sub-report comes in.  Since Tom is the subject of the main report I'd like to exclude him from the sub-report.  How can I do this?  Can I do it using report or sub-report properties? Or, can I do it in the report query?  All of the family members' information is in the same table.

Thanks in advance.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39745561
Does the table hold info about many families?
Upload a sample database with the relevant tables.

The table design dictates the approach to the report design.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 20

Expert Comment

by:clarkscott
ID: 39746079
In your main table - add a field called MAIN MEMBER and make it yes/no.
In your records, mark the MAIN MEMBER record as true (for those folks you want to designate as such).

Create query that pulls in all the records where MAIN MEMBER isnull or false.

Use this query as the record source for your sub-report.

All MAIN MEMBERS will not show up.

Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39746080
Further... you can also do the reverse so only the MAIN MEMBERS are part of the MAIN REPORT.  This way, you will only see MAIN MEMBERS in the main report - and only NON-MAIN MEMBERS in the sub report.

Scott C
0
 

Author Comment

by:rlopez11
ID: 39746208
Scott,
Thanks for your reponse.  I'm not able to try your proposed solution now but I do have a question.  Will your solution work if every member of the family will have a main report.  Using my family example above:
  Tom will have a main report that should not list him in the sub-report.
  Betty will have a main report that should not list her in the sub-report.
  Harry will have a main report that should not list him in the sub-report.

Thanks
rlopez
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39746295
Well... this scenario will be a little more complex.

This is what I would do.

(this assumes you have a long integer (autonumber) for your primary key of your table - named ID )


1.  Create a public variable

Public glbID as long

2. Create a function to use the variable.

Function get_glbID() AS LONG
get_glbID = glbID
END FUNCTION

3.  Create a query and apply to subreport datasource:
Where ID <> get_glbID()



4.  In the ON CURRENT event of the report:  
glbID = ME.ID
'--then requery your subreport ------
me.YourSubReportName.requery


If this is too complex for you - attach your app and I'll look at it.

Scott C
0
 

Author Comment

by:rlopez11
ID: 39746517
Thanks Scott,
Yes, I may need some additional help.  I will forward a copy of the table as soon as possible. this evening. Thanks.
0
 

Author Comment

by:rlopez11
ID: 39748105
nsar, scott;
Sorry for delay.  Attached is a sample of the table.
SAMPLE-FAM-TBL.xlsx
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39748466
You provided me with an Excel sheet with rows.
This is not your Access app or data.
I will need to see your application, with the tables and reports, in order to help you.

I don't know much per just this Excel sheet (I'm not going to totally rebuild your project, reports, and such with only this information)

If you send me your project, I can help you.

Scott C
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39749017
In the excel sheet, you did not show the required report and how you decide which goes in the main and which goes in subreport.

As clarkscott suggested, it is preferred to upload a sample databasse, so to save us reproducing the problem.
0
 

Author Comment

by:rlopez11
ID: 39749289
nsar, Scott;
My apologies.  I thought the table would be enough.  Attached is the Access report in it's simplest configuration.

Again, I apologize and thanks for your patience.  Happy New Year!!
Database31.accdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39749389
Put code in subreport's format event to exclude the main sibling.
Database31-1.accdb
0
 

Author Comment

by:rlopez11
ID: 39749692
hnsar,
Good Morning.  From my original post:  "How do I filter out the main report family member from the sub-report?  What property and code should I use?"

Can you be more explicit?  Thanks

rlopez
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39749939
I didn't change anything:

I noticed that if you open the report in PRINT PREVIEW (not Report View), your report formats the way you want.

Don't double-click to open the report.  Right-click and select PRINT PREVIEW.


Try it.

Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 39749941
I'm not exactly sure how you use this report, but if it were me....

I'd create a query and only bring in the records designated "MAILING".
Use this query for the MAIN report source.

I'd create a 2nd query and ELIMINATE records designated "MAILING".
Use this query for the subreport source.

You will get all your data - without spitting out all that extra paper.

I attached this suggestion. PS - this will format correct REPORT VIEW or PRINT PREVIEW.



Just a suggestion.




Scott C
Database31-1-test.accdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39749963
"What property and code should I use?""
The subreport detail's format property just dont display the record that has the sibling_id equals that of the main report.
Property: subreport Detail Format event
Code as follows.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If SIBLING_ID = Me.Parent.SIBLING_ID Then ' the parent refers to main sibling report
        Cancel = True
    End If
End Sub

Open in new window

0
 

Author Comment

by:rlopez11
ID: 39750389
Again, I posted a comment earlier this afternoon and now it's not showing.  In any case hnsar's earlier post works great.  I was doing exactly what Scott warned against-- don't open in Report View.  I changed the default view to Print Preview and all works great.  My apologies hnsar, for asking you to be explicit on the code and property.

Scott,
Your last suggestion is appreciated but it will not work for the use of this report.  hnsar's solution is exactly what I was looking for.

Thanks to both for your help and patience.  If there are no further comments I'll close out the question.

Again, Thanks A Million!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39750552
Welcome!
0
 

Author Closing Comment

by:rlopez11
ID: 39751045
Thanks for your expertise-- and,  above all-- your patience!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question