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

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

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.

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.

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

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

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

Thanks Scott,
Yes, I may need some additional help.  I will forward a copy of the table as soon as possible. this evening. Thanks.

nsar, scott;
Sorry for delay.  Attached is a sample of the table.
SAMPLE-FAM-TBL.xlsx

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

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.

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

Put code in subreport's format event to exclude the main sibling.
Database31-1.accdb

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

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
Database31-1-test.accdb

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!

Welcome!

Thanks for your expertise-- and,  above all-- your patience!!