Solved

Subreport is not showing data specific to the Main Report's detail record

Posted on 2014-12-29
14
46 Views
Last Modified: 2016-02-11
Hello All

I have a Main Report that lists people.  I have a subreport that I dragged into the detail section of the main report.

WhenI run the Main report I may only want to see people from FL.  In the subreport I should see details about each person that shows up in the main report.  So in the Main report I may have 5 detail lines produced.  On each would be the name of a person.  Then each subreport should show me information about that particular person.

What is happening is that the subreport is only showing information about the last person of the group.  

How can I get the subreport to refresh it's query so that on each detail line it shows that person's information?

THank You!
0
Comment
Question by:alevin16
14 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40522777
Much depends upon your setup.
Normally, the subreport Data tab has a Master Field and Child Field and this binds the main report and subreport together.

It is important to understand WHERE those fields are physically on the report.
Let's say the two are bound on PersonID.

If the control with PersonID in it is in the Report Header, but the subreport is in the Detail section, you will likely get a subreport for the PersonID in the first record only.  After all, when the Report Header renders, the first record is the only one in force yet -- and the control doesn't change it's value there each time the Detail section renders.

Now let's say that the control with PersonID in it is in the Detail section.  Great!  It'll change with every record.  But if the subreport is in the Report Footer section, then PersonID will be the last record in the set when the subreport renders.

So, you need a control with the Master field to be in a Section that will allow it to change with each record.  Likewise, the subreport must be in a similar section.  The Detail Section is the obvious place -- but sometimes that isn't what you want organizationally.  In Grouping and Sorting, you can add fields to sort by, and you can give them their own header and/or footer section.  I'll do this with a Primary Key field that is unique for each record on the report when I need to do this.

But I suspect your problem at the moment is that the subreport is in a section other than -- and farther down the page -- than the Detail section.  This What is happening is that the subreport is only showing information about the last person of the group.   is exactly the expected result from that setup
0
 

Author Comment

by:alevin16
ID: 40522799
Hi,

Thanks for the reply.  Actually both the field and subreport are in the detail section.  This report and subreport were in an access program that was saved locally on our server.  We recently converted it so that the back end is up on an Azure machine.  So the old way based a query on the key field and it worked nicely, in this version we had to use an SQL statement in the open event of the subreport.

I hope I did not make this more confusing.

Thanks
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40522830
Ok,

Open the subreport independently of the main report if you can.
Or create a query out of the SQL statement from the Open event and open that.

All the possible desired records should show.
If only the last record desired does, that's where the problem lies.
It is the Master/Child Relationship that filters the results.
Not the SQL of the subreport's RecordSource
0
 

Author Comment

by:alevin16
ID: 40522875
Hi Nick,

I will give that a shot.  

Thanks!
0
 

Author Comment

by:alevin16
ID: 40522881
Hi Nick

I realized that the subreport will need the key field ID from the main one to run.  Here is the SQL statement in the open of the subreport

 Me.RecordSource = "SELECT * " & _
                            "FROM dbo.[JustRatesNotes]('" & [Reports]![Plan Selection and Deduction Top]![SubRateID] & "') "
                           
 I thought that each detail record of the main report would "reopen" the sub report and refresh this sql statement.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40523001
Since this has nothing to do with MS SQL Server you may want to request the topic area be replaced with a more appropriate TA such as: http://www.experts-exchange.com/Database/MS_Access/
0
 

Author Comment

by:alevin16
ID: 40523010
Hi Anthony

The backend is on an SQL server so I thought it would fit there (plus for some odd reason whenever I put in a question I can never find MS Access...just getting old and senile)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 30

Expert Comment

by:hnasr
ID: 40523742
From what is presented looks ok.
Problems lie in the unspoken issues.

Check your main form
name: mf
controls: m_id, m_f1, m_f2
Record source: table  m (m_id, m_f1, m_f2)

And the sub form
name: sf
controls: m_id, s_id, s_f1, s_f2
Record source: table s (m_id, s_id, s_f1, s_f2)

subform control sf
Link Master fields: m_id
Link Child fields: m_id

It is a good idea to upload a sample database with local dummy tables to investigate.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40523850
Me.RecordSource = "SELECT * " & _
                             "FROM dbo.[JustRatesNotes]('" & [Reports]![Plan Selection and Deduction Top]![SubRateID] & "') "

I have to say that I can't visualize what this is meant to do
This I get
Me.RecordSource = "SELECT * FROM dbo.[JustRatesNotes]
But now what is this in brackets?
('" & [Reports]![Plan Selection and Deduction Top]![SubRateID] & "') "
I suspect that it will evaluate out to a Long value of some sort (let's say 1 just for fun)  but the resulting SQL
Me.RecordSource = "SELECT * FROM dbo.[JustRatesNotes](1)"
Is not syntax I've seen before.

I take it that SubRateID is the Master/Child field?
If it is, then it should be in dbo.[JustRatesNotes] as a foreign key
(i.e. dbo.[JustRatesNotes] has fields something like JustRatesNotesID, SubRateID, and many others)
The SQL for the subreport can then just be
Me.RecordSource = "SELECT * FROM dbo.[JustRatesNotes]"
The Master/Child relationship then takes care of filtering it down to
"SELECT * FROM dbo.[JustRatesNotes] where dbo.[JustRatesNotes].SubRateID = " &  Me.[SubRateID]
(the italicized SQL is not needful)

If SubRateID is not in dbo.[JustRatesNotes], then you need to create a query with the appropriate joins so that SubRateID can be used as the Master/Child field, if that is what is desired.
0
 

Author Comment

by:alevin16
ID: 40524415
Hey Nick

I agree I never saw code like that before either.  You are also right, the subrateID is a key field in the main table, linked to the child table in a one to many relationship.

What this was doing when it was just in Access was that each detail record on the main form (for argument's sake lets just say 5 people would come up) would have an area that was linked to the subform where each person could have anywhere from 1 to about 10 records.  When it was in Access it was based on a query so all worked, but when I run it now that the backend is on Azure SQL it seems to only take the first subrateid and never refreshes the subform.  So basically person 1 shows their 1-10 lines correctly but then person 2-5 have the exact same lines in their area.

I had someone convert the database so that it could be used on the cloud with SQL as a backend and sadly I am not adept at that.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40524668
Me neither.
I tried to get an Azure/inhouse sync relationship going, but that just stalled the inhouse machine.
And at the time, you had to supply a static IP to get an authenticated Azure login working -- so much for being mobile :(
And 300 MB on a Azure SQL server was running $14/mo. without even accessing it.

Still.
Have you tried
Me.RecordSource = "SELECT * FROM dbo.[JustRatesNotes]"
0
 

Author Comment

by:alevin16
ID: 40524835
Not yet.  I got called to work on other areas of the program.  I am going to try it now.  Wish me luck :D
0
 

Author Closing Comment

by:alevin16
ID: 40624540
Well I never got it to work correctly and then I got fired so they are on their own.  I appreciate all the help and I think Nick was getting close to it.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40624605
Sadness!
Best of luck moving forward.

Nick67
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

12 Experts available now in Live!

Get 1:1 Help Now