Access - VBA/SQL Recordset loop through not updating

Question

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?

ourRecordset![Start Date] = JS <-- What are you trying to do here? Why are you trying to write-back to a (read-only) ADODB Recordset object? Are you trying to update values in your database table? If so then you need to execute an explicit UPDATE statement. — Dai
– Dai, Commented Sep 30, 2022 at 14:08
Hi Dai, thanks for helping. I'm trying to update the tblRouting.[Start Date] field each time it loops trough. At the moment for testing JS is set to Now() but when working the dates will be pulled from the DB. and there's 2 criteria the 1st pass will be different to the rest of the loops. I should be ok with that once i can get it updating — sambob_628
– sambob_628, Commented Sep 30, 2022 at 14:13

HansUp · Accepted Answer · 2022-09-30 14:20:22Z

1

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

edited Sep 30, 2022 at 14:20

answered Sep 30, 2022 at 14:11

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

snj Over a year ago

As others have said, use a SQL UPDATE statement. It will update all of the rows at the same time and prevent deadlocks.

sambob_628 Over a year ago

aghhh.... I was so close...... hrs I've spent trying to work it out, Thanks HandsUp working now!

Collectives™ on Stack Overflow

Access - VBA/SQL Recordset loop through not updating

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related