Loop not working as expected (3 Viewers)

[John Lee]

Hi guys,

thank you once again for all your help, I've resolved my problem, below is the code which is working great:

 

[RonPaii]

Hi guys,

thank you once again for all your help, I've resolved my problem, below is the code which is working great:
View attachment 123649

Glad you got it working, but you could replace all this with a single statement.

CurrentDB.Execute _
"UPDATE qryHealthSafetyMatrixFireSafetyFireWarden " & _
"SET qryHealthSafetyMatrixFireSafetyFireWarden.[dtmFireWardenMarshallRefresherDate] " & _
"""= DateAdd(""yyyy""," & 3 & "," & [dtmCourseStartDate] & ");"

 

[MadPiet]

Good for learning how to use recordsets, but the second lesson you should learn is to never use a recordset unless you absolutely have to. They're crazy slow compared to a regular update query.

 

[Gasman]

Learn to indent your code.
It makes debugging much easier, also no point using code tags if you do not.

 

[RonPaii]

If you insist on using recordsets, the following is cleaner and will let the user know when all the records were not updated. A update query will update all the records or none of them.

private Sub Form_Load()
    On Error Goto ErrorHandler
    Dim I as long
    ' Only Select the columns you require
    With db.OpenRecordset("Select " & _
                          "dtmCourseStartDate, " & _
                          "dtmFireWardenMarshalRefresherDate " & _
                          "FROM qryHealthSafetyMatrixFireSafetyFireWarden")
        If not (.EOF And .BOF) Then
            .MoveFirst
            Do While Not .EOF
                .Edit
                .dtmFireWardenMarshalRefresherDate = DateAdd("yyyy",3, .dtmCourseStartDate)
                .Update
                i = i + 1
                .MoveNext
            Loop
            .Close
        Else
            MsgBox("No Records Found")
        End If
    End With
DoneLoad:
    Exit function
ErrorHandler:
    MsgBox("Error Updating Dates. " & vbcrlf & _
           I & " records were update." & vbrlf & _
           "Some may not have been updated. " & vbcrlf & _
           Err.Description
    Resume DoneLoad
End Sub

 

[xavier.batlle]

Nobody says anything about the missing
"Option Explicit"?