Here is a bit of code I have written to display the Next to Last or Last But One record in a SubForm.

Needed to use the RecordSource property on Load. Hope you can pick out the fields and tables names. Used the Variable myRx as otherwise the SQL statement became far too long-winded and difficult to manage syntax-wise.

Note the check to see if there are more that 1 record as I also have a form that displays the Last Record. (PxPracticeNo is like a Client ref. No.)


If DCount("[RxFID]", "Rx Final", "PxPracticeNo = " & Me.Parent.PxPracticeNo.Value) > 1 Then

Dim myRx As Long
myRx = DMax("[RxFID]", "Rx Final", "PxPracticeNo = " & Me.Parent.PxPracticeNo.Value & " AND " & _
"RxFID <> " & DMax("[RxFID]", "Rx Final", "PxPracticeNo = " & Me.Parent.PxPracticeNo.Value))

Me.RecordSource = "SELECT [Rx Final].* FROM [Rx Final] WHERE ((([Rx Final].RxFID)= " & myRx & ") AND " & _
"(([Rx Final].PxPracticeNo)= " & Me.Parent.PxPracticeNo.Value & "));"

Me.Visible = True

Else
Me.RecordSource = ""
Me.Visible = False
End If