Form to Display Next to Last record

[ted.martin]

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