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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
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
rlopez11Author Commented:
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.
Hamed NasrRetired IT ProfessionalCommented:
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.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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
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
rlopez11Author Commented:
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.

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

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 ------

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

Scott C
rlopez11Author Commented:
Thanks Scott,
Yes, I may need some additional help.  I will forward a copy of the table as soon as possible. this evening. Thanks.
rlopez11Author Commented:
nsar, scott;
Sorry for delay.  Attached is a sample of the table.
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
Hamed NasrRetired IT ProfessionalCommented:
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.
rlopez11Author Commented:
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!!
Hamed NasrRetired IT ProfessionalCommented:
Put code in subreport's format event to exclude the main sibling.
rlopez11Author Commented:
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

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
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
Hamed NasrRetired IT ProfessionalCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rlopez11Author Commented:
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.

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!
Hamed NasrRetired IT ProfessionalCommented:
rlopez11Author Commented:
Thanks for your expertise-- and,  above all-- your patience!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.