Updating Recordset

[DMT Dave]

Hi Guy's, hope you are all well, this one should be a simple fix!



I am trying to update a recordset between 2 dates with a criteria: if an email address exists, then update Send Reminder field with the email addresses

If an email address doesn't exist looping through the records then just "N/A"

Result: There is nothing being added to the Send Reminder Field

Dim rs as dao,Recordset, sDate as Date,eDate as Date, em as String
sDate = Me.txtFrom
eDate = Me.txtDateTo
Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate Between #" & sDate & "# And #" & eDate & "#")
With rs
    rs.MoveFirst
Do Until rs.EOF
    .Edit
If Not IsNull(rs.Fields("Email")) Then
    em = rs.Fields("Email")
Else
    em = "N/A"
End If
    rs.Fields("SendReminder") = em
    rs.Update
    rs.MoveNext
Loop
End With
    rs.Close
Set rs = Nothing
    Me.frmWebTimes.Requery

[Welshgasman]

A. You should check EOF before even trying to use a recordset
B. Walk though your code with F8 and a breakpoint. That always allows you to find silly errors.

At first glance, I cannot see anything else wrong with your code, and that would be my approach was it mine and in the same situation.
Perhaps move the Edit to just before you set the field.?

You should also not be storing the same info more than once?

[DMT Dave]

Thanks WGM, update or cancel update without add new or edit

I think i just have something in the wrong sequence ??????

[DMT Dave]

Changed to this adding if not BOF and EOF

Still comes up with update or cancel update without add new or edit

Debugs on the bold line

Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate Between #" & sDate & "# And #" & eDate & "#")
With rs
.Edit
If Not (rs.BOF And rs.EOF) Then
Do Until rs.EOF
    rs.MoveFirst
    rs.Fields("SendReminder") = rs.Fields("Email") (DEBUGS THIS LINE)
    rs.MoveNext
Loop
.Update
End If
End With
    rs.Close
Set rs = Nothing
    Me.frmWebTimes.Requery

[Welshgasman]

Dim rs As DAO, Recordset, sDate As Date, eDate As Date, em As String
sDate = Me.txtFrom
eDate = Me.txtDateTo
Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE RemovalDate Between #" & sDate & "# And #" & eDate & "#")
With rs
    .MoveFirst
    Do Until .EOF

        If Not IsNull(rs.Fields("Email")) Then
            em = .Fields("Email")
        Else
            em = "N/A"
        End If
        .Edit
        .Fields("SendReminder") = em
        .Update
        .MoveNext
    Loop
End With
rs.Close
Set rs = Nothing
Me.frmWebTimes.Requery

[DMT Dave]

Perfect thank you WGM

[Welshgasman]

Dave,
Look at the code you posted.
It would never stop.
You were lucky it did not run.

You really need to understand what you are writing. Computers are not very forgiving. I can see what you mean, but a computer will just follow your instructions blindly.