ADP subreport not returning records

I have an ADP report that has two subreports.  Neither of the subreports are returning any records.  This works fine with a mdb, but not the adp.  The report filters to a single master record out of a table containing over 47k records.  Each of the subreports contains multiple records for each record in the master.

I've set the LinkChildFields and the LinkMasterFields, but it appears to ignore them.

I'm converting this application from a mdb to adp.  Originally I hadn't set the max records to 0, so I only had 10,000 records in each of these tables, but that's now corrected.

I'm using the WhereCondition parameter when calling the main report.  Is there a timing issue in filling a field of the subreport that causes the subreport to load (with an empty value for the link field) before the filter is set for the main report?

I've spent a few hours on this and feel pretty dumb at this point.  I thought I knew how to build a report in Access.  :-s

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Hi bjones8888

I have started using a free third party report building tool called LogiXML ( I've only messed about with a couple of sample reports but initial results are good.

I know that doesn't answer your question but I actually moved to a third party tool because Access is too quirky, erratic and can have you chasing your tail for no reason.

bjones8888PresidentAuthor Commented:

I'm sure it's a good tool, but this is for a client who has to get approvals for this type of add-on.  I'd rather not make them jump through that hoop, if I can avoid it.

The first thing to check whould be to determine if your datasource is working for the mainreport.
Are you getting data in the mainreport? ...and especially the key fields?
Are the key fields in the mainreport source and the subreport source of the same type (all 3 number, text etc...?)

So if you have set up child and master fields in each of the subreports to link to a field that is in main report
you should be OK.

Just a note.
FYI : The subforms/subreports fire first before the mainform/mainreport

hope this helps

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bjones8888PresidentAuthor Commented:
The datasource is working for the main report.  The correct data for the master record appears in the report.  The datatypes of the master link field and the child link field are the same.  The datasource for the main report is along the lines of "Select * from tblMaster".  There's a WhereCondition specified in the OpenReport call.  That filters correctly.

However, I can't possibly grasp how a subreport can fire before the mainreport and ever have it work.  It perfectly explains why I'm getting no results for the subreport, because the value of the link field isn't filled yet in the master report, if that's indeed the order of events.  How could it EVER work?
I haven't used Access inbuilt reports outside of MDBs before so the following may not make any sense (apologies if that's the case!!):

Have you put the subreport in the detail section of the master report?

Have you tried opening the subreport separately and passing in a value to ensure that it works correctly on its own?

If you take the subreport out and replace it with a textbox which is bound to the key field in the master report does the value show in the textbox?

Hope this makes some sense.


If you run the subreports separately do they execute and give you the data your expecting? ...especially the "link" field value?  

Maybe you can try to open the report without the the filter, just to make sure that's not the issue.

I know it does not sound right, I was suprised to find out the the sub fired first when I was working with a problem once.

The way I look at it is that the subreports should be showing all of the data you would hope to see.
When the subreport fires it looks for the links in the mainform and only shows the data that is related to the key in the
row that you are on in the mainreport.

Another test might be to make a copy of the mainreport and test each subreport separately.

hope this helps.
bjones8888PresidentAuthor Commented:
If I change the recordsource for the subreport to a view that is "hard-wired" to the record I'm expecting, it looks right.  So the problem is the link somehow.  By the way, this report/subreport is a copy & paste from a mdb where the report works beautifully.  In the working copy the recordsource is a linked table to another mdb.  In the one not working, since this is an adp, it links to the SQL Server table.

Hi Again,

Perhaps there is an issue in the ADP, and someone else has experience deailing with this in ADP.
I am not use to ADP yet.

The only other thing I can think of is to compile all modules, and do a compact and repair.

Good Luck

...another person may read what we have discussed so far and help you further.

thank you

bjones8888PresidentAuthor Commented:
Turns out that since I pasted this report from the original mdb, the connection to the master wasn't working correctly (something embedded that I couldn't see with the property editor?).  I had to create an entirely new sub-report and make it look like the original.  Go figure.
Glad you got it working.

I know how frustrating Access can be!!

BTW - Thanks for sharing the points.


It's great you got it working.

thank you
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 Access

From novice to tech pro — start learning today.