Avatar of alevin16
alevin16Flag for United States of America

asked on 

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!
Microsoft SQL ServerMicrosoft DevelopmentMicrosoft OfficeVBA

Avatar of undefined
Last Comment
Nick67
Avatar of Nick67
Nick67
Flag of Canada image

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
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

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
Avatar of Nick67
Nick67
Flag of Canada image

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
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

Hi Nick,

I will give that a shot.  

Thanks!
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

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.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

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: https://www.experts-exchange.com/Database/MS_Access/
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

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)
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

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.
Avatar of Nick67
Nick67
Flag of Canada image

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.
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

Not yet.  I got called to work on other areas of the program.  I am going to try it now.  Wish me luck :D
Avatar of alevin16
alevin16
Flag of United States of America image

ASKER

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.
Avatar of Nick67
Nick67
Flag of Canada image

Sadness!
Best of luck moving forward.

Nick67
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo