Trying to display last record on a report

[chrisroy1972]

Hi,
What I am trying to do is have the user click a button to open a report based on the current specification they are updating. Now for each specification there are multiple revisions so i added a 'revision history' table. When the user prints the specification, I only want the latest revision number, date, and rev descr to show. I tried using the following:

Private Sub Report_Load()
Dim db As Database
Dim Rev As Recordset
Set db = CurrentDb()
Set Rev = db.OpenRecordset("SELECT tblRevisionHistory.revnum, tblRevisionHistory.revdate, tblRevisionHistory.revision FROM tblRevisionHistory;")
Rev.MoveLast
Me.Text23 = Rev.Fields("revnum")
Me.Text26 = Rev.Fields("revdate")
Me.Text28 = Rev.Fields("revision")
End Sub

The above only showed me the last record in the table regardless of the specification number filter. Not sure what to do next. Any help would be greatly appreciated.

 

[pbaldy]

Well, you'd add a WHERE clause that referred to the specification on the form or report. Personally I wouldn't go this way, I'd probably use a subreport linked to the report and have it use the TOP predicate to only pull a single record.

 

[BlueIshDan]

SELECT TOP 1 ....

ORDER BY Revision DESC

 

[SparklyPrincess6969]

(I'm not 100% on what you're trying to do here - are you directly assigning values to these three textboxes on the report based on the last record in the recordset Rev?)

Anyway, there doesn't appear to be any filter for the specification number on the "Set Rev = etc" line - you might try adding to your SQL a WHERE condition that references whichever textbox on the specification update form displays the link field.

(Apologies if I've misunderstood what the problem is.)

 

[BlueIshDan]

SELECT TOP 1
	revnum, 
	revdate, 
	revision 

FROM 
	tblRevisionHistory

ORDER BY revnum DESC