Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2014-12-29
Medium Priority
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!
Question by:alevin16
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 26

Expert Comment

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

Author Comment

ID: 40522799

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.

LVL 26

Expert Comment

ID: 40522830

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 40522875
Hi Nick,

I will give that a shot.  


Author Comment

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.
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/

Author Comment

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)
LVL 31

Expert Comment

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.
LVL 26

Expert Comment

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.

Author Comment

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.
LVL 26

Accepted Solution

Nick67 earned 2000 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.

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

Author Comment

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

Author Closing Comment

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.
LVL 26

Expert Comment

ID: 40624605
Best of luck moving forward.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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