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

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!
alevin16Asked:
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.

Nick67Commented:
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
alevin16Author Commented:
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
Nick67Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

alevin16Author Commented:
Hi Nick,

I will give that a shot.  

Thanks!
0
alevin16Author Commented:
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
Anthony PerkinsCommented:
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
alevin16Author Commented:
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
hnasrCommented:
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
Nick67Commented:
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
alevin16Author Commented:
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
Nick67Commented:
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

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
alevin16Author Commented:
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
alevin16Author Commented:
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
Nick67Commented:
Sadness!
Best of luck moving forward.

Nick67
0
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 SQL Server

From novice to tech pro — start learning today.