Most recent record in a subreport

[NevilleT]

I have a report and subreport. The report has some information from a
tblProject. The subreport has information from tblDiaryNotes. What I want
the subreport to display is only the most recent diary note. I have tried to
create a query that uses TOP or DISTINCT without success. Also tried to
create an SQL recordsource in VBA by using the project number from the main
report.

"SELECT TOP 1 tblDiaryNote.ProjectNo, tblDiaryNote.Note from tblDiaryNote
where tblDiaryNote.ProjectNo = " & strProject

I put the ProjectNo value in strProject before running. Problem is that if
I use it with the on open it only displays the first project number records.

I need a bit of lateral thinking here. There must be a solution but I am
just too buried to find it. Can anyone help?

 

[Duane Hookom]

You might be able to add a text box to the detail section of the subreport:
Name: txtSeq
Control Source: =1
Running Sum: Over All
Visible: No
Then add code to the On Format event the subreport detail section:
Cancel = Me.txtSeq >1