Access – VBA/SQL Recordset loop through not updating

September 30, 2022

I have a code looping through a recordset and I’m trying to update the start date from a varible (JS) but its not updating… showing an error saying ‘Update or UpdateCancel without addNew or edit’

I have put a .edit just after the ourRecordset![Start Date] = JS which is where its failing

 Dim ourDatabase As Database
    Dim ourRecordset As Recordset
    Dim strSQL As String
    Dim ActvJbNum As Long
    Dim RecCnt As Long
    Dim LpRecCnt As Long
    Dim JS As Date
    
    JS = Now()
    ActvJbNum = Me.EntJobNum
    
    strSQL = "SELECT tblRouting.[Job Number], tblRouting.[Start Date], tblRouting.OpDescription, tblRouting.[Op Seq], tblRouting.OpleadTm " & vbCrLf & _
    "FROM tblRouting " & vbCrLf & _
    "WHERE (tblRouting.[Job Number])= " & EntJobNum & vbCrLf & _
    "ORDER BY tblRouting.[Op Seq] ASC;"
    
    Set ourDatabase = CurrentDb
    Set ourRecordset = ourDatabase.OpenRecordset(strSQL)
    
    
    With ourRecordset
        
        Do Until ourRecordset.EOF
            RecCnt = ourRecordset.RecordCount
            LpRecCnt = LpRecCnt + 1
            ourRecordset![Start Date] = JS
            ourRecordset.Edit
            MsgBox ourRecordset![Start Date] & vbNewLine & ourRecordset![Op Seq] & vbNewLine & LpRecCnt
            
            ourRecordset.MoveNext
            
        Loop
    End With

any idea why it’s not updating the start date?

>Solution :

Call Edit before changing the field value. Afterward, call Update to commit the change.

ourRecordset.Edit
ourRecordset![Start Date] = JS
ourRecordset.Update

However, consider doing this with a single SQL UPDATE, as the commenters recommended, instead of with a recordset. Here it is as a parameterized UPDATE …

Dim qdf As DAO.QueryDef
Dim strUpdate As String

strUpdate = "UPDATE tblRouting SET [Start Date] = Now() WHERE [Job Number]=[Which Job]"
Set qdf = CurrentDb.CreateQueryDef(vbNullString, strUpdate)
qdf.Parameters("Which Job") = EntJobNum
qdf.Execute dbFailOnError