Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Edit a record in a recordset (2000)

    Hi.

    I have created an Access form, with a button that the user can click to send an e-mail when a record meets a condition, in this case when a due date is coming up. The code works fine and I can send the e-mail. The issue that I have is that once the e-mail has been sent, I would like to update a field in the record to say that a reminder has been sent. However the code that I have does not seem to update the field. Maybe I need to do an update query instead of using "edit"? I am not sure how "edit" works.

    Any help is much appreciated.

    Private Sub cmdRenewals_Click()
    Dim intAnswer As Integer
    Dim dbs As Database
    Dim rst As Recordset
    Dim recorda
    intAnswer = MsgBox("Do you wish to send an e-mail to staff " & Chr(13) _
    & "whose CRB checks are due for renewal", vbYesNoCancel)
    If intAnswer = vbYes Then
    Set dbs = CurrentDb()
    Set rst = Me.Recordset
    rst.MoveFirst
    Do Until rst.EOF
    With rst
    If .Fields("Status") = "DUE FOR RENEWAL" Then
    SendMail rst
    .Edit
    .Fields("CRB_NOTE").Value = "DUE -reminder sent " & Format(DATE, "DD/MM/YY") _
    & " " & .Fields("CRB_NOTE").Value
    End If
    .MoveNext
    End With
    Loop
    Set rst = Nothing
    Set dbs = Nothing
    End If


    Amanda

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Edit a record in a recordset (2000)

    You need one additional line.
    .Update

    before the End if

    .Update gets the changes you made in Edit mode saved.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Edit a record in a recordset (2000)

    The line .Edit places the recordset in edit mode. The next line, .Fields("CRB_NOTE").Value = ... changes the value of a field. To save the change, you must insert a line

    .Update

    below it (above the End If that follows it). Without the .Update, changes are discarded when you move to the next record.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Edit a record in a recordset (2000)

    Hans

    Thank you so much. As usual, your advice worked no problems.

    Amanda

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Edit a record in a recordset (2000)

    thank you. thank you.

    It is now working splendidly.

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •