Find the latest record problem

[Guest]

Hi i have a form which shows which items are Red status, via a list. the list
runs of a query which only pulls in the latest week. To go to the correct
record i have a find button (see below) except this does not work, it pulls
in all the records in that location. I would like it just to pull in the
latest one. Or if this is not possible then any within the last 7 days.

I cannot use the record number as the database uses random key numbers, and
i cannot change this

Private Sub Find_Red_Button_Click()
On Error GoTo Err_Find_Red_Button_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AuditMainFrm"

stLinkCriteria = "[LocCode]=" & "'" & Me![Red List] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Find_Red_Button_Click:
Exit Sub

Err_Find_Red_Button_Click:
MsgBox Err.Description
Resume Exit_Find_Red_Button_Click

End Sub

 

[John W. Vinson]

Hi i have a form which shows which items are Red status, via a list. the list
runs of a query which only pulls in the latest week. To go to the correct
record i have a find button (see below) except this does not work, it pulls
in all the records in that location. I would like it just to pull in the
latest one. Or if this is not possible then any within the last 7 days.

Does the table have a Date/Time field recording when the record was
created? If so, you can use a Subquery; put a criterion on the field
of

=(SELECT Max([datetimefield]) FROM yourtable AS X WHERE X.LocCode =
yourtable.LocCode)

If you do not have a date field, then you're out of luck - Access does
not record when records are created unless you do so yourself.

John W. Vinson [MVP]