Limit records in subreport

[mortenlamoey]

Greetings

In Access 2010 I am previewing a customer report. I also try to give users the choice of seeing history sub records for any given customer. I have a selection dropdown box where they can chose ALL, 5, 10 or 15 in a report. To this end I set the record source in the "Open" event for the subreport. if I want all records it works fine, but as soon as I put in "SELECT TOP 5" the subreport goes invisible. I have tested the query directly and it works fine. I'm sure this worked in Access 2007 :banghead:

 

[Estuardo]

G'd evening!
In a SELECT statement, always use an ORDER BY clause with the TOP clause otherwise it may not work.
If that's not the case change you query and instead of TOP use COUNT
G'd luck

 

[mortenlamoey]

Thanks Estuardo, but that did not help. However I did find a solution.

The main problem is that the TOP query is run before the Master/Child filter is run, so unless by pure luck, no data will ever come out of the subreport. What I have done now is include a reference to the CustomerID from the parent report in my query, and not the actual CustomerID, as that hardwired the same CustomerID to all records. In essence I added this reference to the parent report: "WHERE CustomerID = [Reports]!PrintSchedule]![CustomerID]" which solved my problem, and probably reduced the amount of data fetched from the database at the same time (if the backend is other than an Access DB)

I did try this, which did not work: "WHERE CustomerID = " & [Reports]!PrintSchedule]![CustomerID] since the customer ID checges for each record in the parent report and the query got stuck with the first CustomerID and did not read any of the others.

 

[Estuardo]

I'm glad have found the source of the problem and most important the solution!