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
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