Solved

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

Posted on 2013-12-29
20
422 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
  • 8
  • 6
  • 6
20 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
nsar, scott;
Sorry for delay.  Attached is a sample of the table.
SAMPLE-FAM-TBL.xlsx
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
Put code in subreport's format event to exclude the main sibling.
Database31-1.accdb
0
 

Author Comment

by:rlopez11
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
Welcome!
0
 

Author Closing Comment

by:rlopez11
Comment Utility
Thanks for your expertise-- and,  above all-- your patience!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now