Run Time error: 3020 when editing recordSet

[bbulla]

Hi,

I get an 'Update or CancelUpdate without AddNew or Edit' error when running the following code. (the error happens where I have placed the **) The query has two fields: Phone and Fax, and in the table they are the proper format/length for this to work. When i do a debug and hover over the variables on the marked line, everything looks as expected. I cannot figure this error out.

Public Sub Add_Dashes()
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("qryPhoneFax")

Dim x As Integer
Dim y As Integer
Dim tempPhone As String
Dim tempFax As String
x = 0
y = 0
rs.Edit

Do Until rs.EOF
If Len(rs!phone) = 10 Then
tempPhone = dash_it(rs!phone)
rs!phone = tempPhone '**
x = x + 1
End If

If Len(rs!fax) = 10 Then
rs!fax = dash_it(rs!fax)
y = y + 1
End If

rs.MoveNext
Loop
rs.Update
rs.Close
rs = Nothing

MsgBox (x & " Phone Numbers changed : " & y & " Fax Numbers changed")

End Sub


Private Function dash_it(number As String)
Dim first As String
Dim second As String
Dim third As String

If Len(number) = 10 Then
first = Left(number, 3)
second = Left(Right(number, 7), 3)
third = Right(number, 4)
dash_it = first & "-" & second & "-" & third
End If

End Function

 

[pbaldy]

I would move the Edit and Update lines inside the loop where you actually change the values, and see if that fixes it.

 

[bbulla]

Hmmm....interesting. It works now after I move the rs.edit into the loop. I don't understand why, but I guess that is just part of VBA.

Thanks for the tip!!