Access Subreport with TOP clause

I have a report with a subreport that uses TOP 1 clause in the query but it is not displaying the subreport when I use TOP 1.
Who is Participating?
hnasrConnect With a Mentor Commented:
To display top record for each subreport, add a text box to the detail section of the subreport, say txtX
Control SOurce=1
Running Sum: Over All
Visible: No

Remove the top 1, and sort properly.
Detail_Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If txtX > 1 Then
        Cancel = True
    End If
End Sub
Rey Obrero (Capricorn1)Commented:
perhaps there is no record to display
PapoteAuthor Commented:
If I remove the TOP clause the records DO show up using the same key.
I tested the query alone with the specific key and it works, even the report alone, but not when using the Top clause as a subreport.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Dale FyeCommented:
How is your report configured?  Is your subreport linked (Master/Child) to the parent report?  If so, an you use the Top 1 syntax in the query, you will only see the entry in the sub-report for that record in the main report that corresponds to the record in the subreport.

You might want to change the query of the subreport to something like:

SELECT [GroupID], [Field1], [Field2]
FROM SomeTable as T
WHERE [Field1] = (SELECT MAX([Field1]) FROM SomeTable WHERE [ID] = T.ID)

Assuming that the [GroupID] field is used in the main report, this query would return one record (with all the appropriate fields) for each [GroupID] value.  Then use the [GroupID] field in your master/child relationship and you will have your main report and a single record for each matching GroupID in the subreport.
Top 1 for a seubreport will use only 1 record from the underlying recordsource. So I expect your report to not show any record, or that only one record selected using Top 1.

Change top 1 to top 100 and check!
Try this:

' No page footer on page 1
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If Page = 1 Then
        If Page = pages Then
          Cancel = True
        End If
    End If
End Sub

' No report footer and increase size of page footer
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    PageFooterSection.Height = PageFooterSection.Height + 3000  '(adjust to your needs-do some homework)
    Cancel = True
 End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.