The reason you’re getting a syntax error is that Microsoft Access SQL does not allow ORDER BY or LIMIT clauses in an UPDATE statement.
Unlike some other SQL dialects (like MySQL or PostgreSQL), Access has a more limited syntax for updates. So, when you try to use ORDER BY TblTimeSheet.StartTime DESC and LIMIT 1 in your UPDATE query, Access throws a syntax error because those keywords aren’t supported there.

What you want to do:

You want to update only the latest record (the one with the most recent StartTime) for a particular employee, setting the EndTime to the current time (Now()).

How to fix this:

You can achieve this by using a subquery inside the WHERE clause that identifies the record with the maximum StartTime for that user. Here’s how you can write the query:

sql

CopyEdit

UPDATETblTimeSheet SET EndTime = Now() WHERE EmployeeUserName = 'sam.harper' AND StartTime = ( SELECT MAX(StartTime) FROM TblTimeSheet WHERE EmployeeUserName = 'sam.harper' );

Explanation:

• The WHERE EmployeeUserName = 'sam.harper' clause ensures you’re only updating records belonging to that user.

• The AND StartTime = (SELECT MAX(StartTime) ...) clause filters that down to only the record with the most recent StartTime.

• This way, even without ORDER BY or LIMIT, you can precisely target the last record for that user.

Updated VBA code snippet:

vb

Dim strSQL As String Dim db As DAO.Database Set db = CurrentDb strSQL = "UPDATE TblTimeSheet " & _ "SET EndTime = Now() " & _ "WHERE EmployeeUserName = 'sam.harper' " & _ "AND StartTime = (SELECT MAX(StartTime) FROM TblTimeSheet WHERE EmployeeUserName = 'sam.harper');" db.Execute strSQL, dbFailOnError

A couple of tips:

• Make sure to use single quotes ' around string literals in your SQL query when writing VBA code. Double quotes inside strings can cause confusion.

• If there’s a chance that multiple records have the exact same StartTime, this query might update all of them. To avoid that, if your table has a unique primary key (like an ID), you could first find the ID of the latest record and then update based on that ID.

Optional: Two-step approach if you want to be extra precise

You could first fetch the primary key of the last record in VBA, then update it specifically:

vba

Dim db As DAO.Database Dim rs As DAO.Recordset Dim lastRecordID As Long Dim strSQL As String Set db = CurrentDb ' Get the ID of the latest record for this user Set rs = db.OpenRecordset("SELECT TOP 1 ID FROM TblTimeSheet WHERE EmployeeUserName = 'sam.harper' ORDER BY StartTime DESC") If Not rs.EOF Then lastRecordID = rs!ID rs.Close ' Update the EndTime of that record strSQL = "UPDATE TblTimeSheet SET EndTime = Now() WHERE ID = " & lastRecordID db.Execute strSQL, dbFailOnError Else MsgBox "No records found for this user." End If

This method avoids any ambiguity if StartTime is not unique.