Lastupdated Button

[Hagar]

I have a table named DETAIL I set the form to open and sort by NAME (ascending) then TITLE (ascending). If it matters, TITLE is the primary key. I have a field named LASTUPDATED which obviously fires every time a record is updated. What I need is a button that takes me to the record I last updated. I know I could just sort by LASTUPDATED, but I want to keep the records in the order they sort now so I can close the form, open it again later, and return to the last one I updated and keep going from there. Can someone please help me?

[mike60smart]

Hi
It is recommended that you do not have a Text field as the Primary Key.

Can you upload a copy of the database?

[CJ_London]

be aware that Name is a reserved word and should not be used as a field name - reason: all objects have a Name property - field.name, control.name, table.name, query.name etc. It is also meaningless within the scope of an application. Suggest rename to something like custName

with regards your question something like

me.find "LastUpdated=#" & format(dmax("LastUpdated","myTable"),"yyyy-mm-dd hh:nn:ss") & "#"

might provide what you are looking for

[Welshgasman]

You could use DLookup() and DMax() and go to that record.
An autonumber as PK would be better.

[Hagar]

First to address that I should not use 'name' as a field nor use a text field as a key, I know and I agree. Sadly I did not know this back when I built it and I have too many programs and too many forms to change it now.
As for the SQL examples, yes they work at finding the last record changed. Here is my situation. About once per month I go through this database one record at a time checking for information that is left out by someone getting in a hurry or information that is impossible (ie age=236 instead of 36. etc etc. Often I'm called away or go to lunch and I shut everything down and log out before I go. When I return, I open the database, run a SQL I have to show me the last update, then open the form, click find record, go to the correct record, and continue. But on the day(s) I'm doing this I may leave it and come back a dozen times.

So I was just looking to shorten it by a few clicks. I just want to open the form, click a "last updated" button, and continue through the records from there. I've tried to make this button, but so far the best I have achieved is to make a button that takes me to the last record updated, but then I cannot go forward or back, so I close the form and reopen it to go to other records. I used this code to do that:

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "select * from detail where lastupdated = (select MAX(lastupdated) from detail)"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Set Me.Recordset = rs

[Welshgasman]

I would set the filter, not restrict the recordset.

[davegri]

I would set the filter, not restrict the recordset.

WGM: Setting the filter would restrict the form to that single record, which is the problem trying to be resolved.

Hagar: See if you can adapt this, which uses the DLookup provided by CJ_London above to get the target starting point.

'---------------------------------------------------------------------------------------
' Method : Whatever_AfterUpdate
' Author : davegri
' Date   : 8/20/2021
' Purpose:  All the records in this form's recordsource are available after
'    this type of lookup.
'---------------------------------------------------------------------------------------
Private Sub cboSearch_AfterUpdate()
dim LU as date
LU = "LastUpdated=#" & format(dmax("LastUpdated","detail"),"yyyy-mm-dd hh:nn:ss") & "#"


With Me.RecordsetClone
    .FindFirst "lastupdated = #" & LU & "#"
    If Not .NoMatch Then
        If Me.Dirty Then Me.Dirty = False
        Me.Bookmark = .Bookmark
    End If
End With
End Sub

[jojowhite]

maybe try:

Dim strSQL As String
Dim dte As Date
dte = DMax("lastUpdated", "detail")
strSQL = "select * from detail where lastupdated = #" & Format$(dte,"mm/dd/yyyy hh:nn:ss") & "#"
Me.Recordsource = strSQL

[davegri]

maybe try:

Dim strSQL As String
Dim dte As Date
dte = DMax("lastUpdated", "detail")
strSQL = "select * from detail where lastupdated = #" & Format$(dte,"mm/dd/yyyy hh:nn:ss") & "#"
Me.Recordsource = strSQL

OP needs to have all records available in the form after lookup. That code would restrict the recordsetclone to one record (if lastupdated includes time).

[Welshgasman]

WGM: Setting the filter would restrict the form to that single record, which is the problem trying to be resolved.

Hagar: See if you can adapt this, which uses the DLookup provided by CJ_London above to get the target starting point.

'---------------------------------------------------------------------------------------
' Method : Whatever_AfterUpdate
' Author : davegri
' Date   : 8/20/2021
' Purpose:  All the records in this form's recordsource are available after
'    this type of lookup.
'---------------------------------------------------------------------------------------
Private Sub cboSearch_AfterUpdate()
dim LU as date
LU = "LastUpdated=#" & format(dmax("LastUpdated","detail"),"yyyy-mm-dd hh:nn:ss") & "#"


With Me.RecordsetClone
    .FindFirst "lastupdated = #" & LU & "#"
    If Not .NoMatch Then
        If Me.Dirty Then Me.Dirty = False
        Me.Bookmark = .Bookmark
    End If
End With
End Sub

Sorry, I am confusing this thread with one for strange date filters.

[jojowhite]

OP needs to have all records available in the form after lookup. That code would restrict the recordsetclone to one record (if lastupdated includes time).

that is the original OP in post #1, having the code in a Button.

or he can just Search for that record:

DoCmd.SearchForRecord Wherecondition:="[LastUpdated] = #" & Format$(DMax("LastUpdated", "Detail"), "mm/dd/yyyy hh:nn:ss") & "#"

no filtering.

[Hagar]

When I run this I get the error message

Run-time error '13':
Type Mismatch

and the debugger highlights the line

LU = "LastUpdated=#" & Format(DMax("LastUpdated", "detail"), "yyyy-mm-dd hh:nn:ss") & "#"

[Hagar]

Just to let you know, with SQL I'm quite well versed. With VB code I am a complete novice that knows very close to nothing

[davegri]

When I run this I get the error message

Run-time error '13':
Type Mismatch

and the debugger highlights the line

LU = "LastUpdated=#" & Format(DMax("LastUpdated", "detail"), "yyyy-mm-dd hh:nn:ss") & "#"

Try simplified:

Private Sub SearchBtn_Click()
    Dim LU As Date
    LU = DMax("OrderDate", "OrderT")
    With Me.RecordsetClone
        .FindFirst "OrderDate =#" & LU & "#"
        If Not .NoMatch Then
            If Me.Dirty Then Me.Dirty = False
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub

[davegri]

jojo, I like that. It'll go in my MZTools code library

DoCmd.SearchForRecord Wherecondition:=