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?
CodePudding user response:
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