Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Unanswered: Cannot Edit a Record

    I am trying to change the value of Field "PlanCode" when Field "PlanName = "A" in a record, but I get the following error message "Update or CancelUpdate without AddNew or Edit."
    Here is my code:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tblName As String
    Set db = CurrentDb

    Set rs = CurrentDb.OpenRecordset("tblName", dbOpenDynaset)

    rs.MoveFirst
    Do Until rs.EOF
    If rs("PlanName") = "A" Then
    rs("PlanCode") = 2
    End If
    rs.Update
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    Any help with what is wrong with above is much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try like this:
    Code:
    Do Until rs.EOF
        If rs("PlanName") = "A" Then
            rs.Edit
            rs("PlanCode") = 2
            rs.Update
        End If
        rs.MoveNext
    Loop
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Sinndho: Thank you so much! My day is now much nicer!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are doing nothing else with this data and recordset it would probably be smarter to issue an update statement, which is more efficient and quicker to run

    eg
    Code:
    strSQL = "update tblName set PlanCode=2 where PlanName='A'"
    docmd.execute strSQL
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Posts
    67
    Thank you, healdem. I will try this since speed is important as I will be running hundreds of thousands of records multiple times.

Posting Permissions

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