• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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
0
rlopez11
Asked:
rlopez11
  • 8
  • 6
  • 6
1 Solution
 
hnasrCommented:
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
 
rlopez11Author Commented:
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
 
hnasrCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
clarkscottCommented:
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
 
clarkscottCommented:
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
 
rlopez11Author Commented:
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
 
clarkscottCommented:
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
 
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.
0
 
rlopez11Author Commented:
nsar, scott;
Sorry for delay.  Attached is a sample of the table.
SAMPLE-FAM-TBL.xlsx
0
 
clarkscottCommented:
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
 
hnasrCommented:
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
 
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!!
Database31.accdb
0
 
hnasrCommented:
Put code in subreport's format event to exclude the main sibling.
Database31-1.accdb
0
 
rlopez11Author Commented:
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
 
clarkscottCommented:
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
 
clarkscottCommented:
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
 
hnasrCommented:
"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
 
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.

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
 
hnasrCommented:
Welcome!
0
 
rlopez11Author Commented:
Thanks for your expertise-- and,  above all-- your patience!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 8
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now