[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADP subreport not returning records

Posted on 2006-04-06
11
Medium Priority
?
471 Views
Last Modified: 2013-12-05
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

Thanks.
0
Comment
Question by:bjones8888
  • 4
  • 4
  • 3
11 Comments
 
LVL 5

Assisted Solution

by:Willibob
Willibob earned 450 total points
ID: 16391385
Hi bjones8888

I have started using a free third party report building tool called LogiXML (http://www.logixml.com/). 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.

Bill
0
 

Author Comment

by:bjones8888
ID: 16391545
Willibob,

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.
0
 
LVL 2

Accepted Solution

by:
bobHacker earned 1050 total points
ID: 16392085
Hi,

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
Bob
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:bjones8888
ID: 16392422
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?
0
 
LVL 5

Expert Comment

by:Willibob
ID: 16392601
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.

Bill
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 16392698
Hi

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.
Bob
0
 

Author Comment

by:bjones8888
ID: 16392731
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.
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 16392855

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
Bob

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

thank you
 

 
0
 

Author Comment

by:bjones8888
ID: 16425831
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.
0
 
LVL 5

Expert Comment

by:Willibob
ID: 16425891
Glad you got it working.

I know how frustrating Access can be!!

BTW - Thanks for sharing the points.

Bill
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 16430080
hi

It's great you got it working.

thank you
Bob
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

872 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