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

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

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

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

Hi Nick,

I will give that a shot.  

Thanks!

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.

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/

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)

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.

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.

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.

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

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.

Sadness!
Best of luck moving forward.

Nick67