Getting error on VBA routine

[ljungers]

Hi to all. Trying to get a onclick VBA routine to run, but I'm getting an error message when I click on the button that is used to run the VBA code to change/update a memo field in a table. This routine is only called one time after the table is reloaded monthly. There is data in this memo field that needs to be converted, exam "<crlflf>" should be changed to "Chr(13) Chr(10) Chr(10)".

The error message I receive is "Update or CancelUpdate without AddNew or Edit". Hope someone can help me and thanks in advance.

Here is the code for the onclick:
================================================== ===

Private Sub FixMemoField_Click()
On Error GoTo Err_FixMemoField_Click
 
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Translated_memo")
 
While Not rs.EOF
rs.Update
rs!memofieldname = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
rs!memofieldname = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
rs.Update
rs.MoveNext
Wend
 
Exit_FixMemoField_Click:
    Exit Sub
 
Err_FixMemoField_Click:
    MsgBox Err.Description
    Resume Exit_FixMemoField_Click
 
End Sub

[ljungers]

I took another look at the code and saw that "rs.Update" was used 2 times n the code. I deleted the 1st occurance of it and tried to run it.

I now get the following message "Item not found in this collection". Anyone have an idea of what is happening.

[NeoPa]

Go back to the original version but replace the first rs.Update with an rs.Edit.
So, you should have :

  ...
  rs.Edit
  'Various lines of updating
  rs.Update
  rs.MoveNext
  ...

[ADezii]

Hi to all. Trying to get a onclick VBA routine to run, but I'm getting an error message when I click on the button that is used to run the VBA code to change/update a memo field in a table. This routine is only called one time after the table is reloaded monthly. There is data in this memo field that needs to be converted, exam "<crlflf>" should be changed to "Chr(13) Chr(10) Chr(10)".

The error message I receive is "Update or CancelUpdate without AddNew or Edit". Hope someone can help me and thanks in advance.

Here is the code for the onclick:
================================================== ===

Private Sub FixMemoField_Click()
On Error GoTo Err_FixMemoField_Click
 
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Translated_memo")
 
While Not rs.EOF
rs.Update
rs!memofieldname = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
rs!memofieldname = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
rs.Update
rs.MoveNext
Wend
 
Exit_FixMemoField_Click:
    Exit Sub
 
Err_FixMemoField_Click:
    MsgBox Err.Description
    Resume Exit_FixMemoField_Click
 
End Sub

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Translated_memo")
 
Do While Not rs.EOF
   If Not IsNull(rs![memofieldname]) Then
       rs.Edit
          rs!memofieldname = Replace(rs!memofieldname, "<crlf>", Chr(13) & Chr(10))
          rs!memofieldname = Replace(rs!memofieldname, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
       rs.Update
   End If
   rs.MoveNext
Loop
 
rs.Close

[ljungers]

I corrected the script as shown in last post. I added a command button to my form so that when the ascii text file that is imported into Access each month, this routine can be run. When I click on it the disk drive light flickers and it looks as nothing is happening. I never get the msg box showing that it completed.

=========================================
Private Sub FixMemoField_Click()
On Error GoTo Err_FixMemoField_Click

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Translated_memo")

Do While Not rs.EOF
If Not IsNull(rs![trans_memo]) Then
rs.Edit
rs!trans_memo = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
rs!trans_memo = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
rs.Update
End If
rs.MoveNext
Wend

MsgBox ("The updates have completed!")

Exit_FixMemoField_Click:
Exit Sub

Err_FixMemoField_Click:
MsgBox Err.Description
Resume Exit_FixMemoField_Click

End Sub

[ADezii]

I corrected the script as shown in last post. I added a command button to my form so that when the ascii text file that is imported into Access each month, this routine can be run. When I click on it the disk drive light flickers and it looks as nothing is happening. I never get the msg box showing that it completed.

=========================================
Private Sub FixMemoField_Click()
On Error GoTo Err_FixMemoField_Click

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Translated_memo")

Do While Not rs.EOF
If Not IsNull(rs![trans_memo]) Then
rs.Edit
rs!trans_memo = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
rs!trans_memo = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
rs.Update
End If
rs.MoveNext
Wend

MsgBox ("The updates have completed!")

Exit_FixMemoField_Click:
Exit Sub

Err_FixMemoField_Click:
MsgBox Err.Description
Resume Exit_FixMemoField_Click

End Sub

Replace Wend with Loop:

Do While Not rs.EOF
    If Not IsNull(rs![trans_memo]) Then
        rs.Edit
            rs!trans_memo = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
            rs!trans_memo = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
        rs.Update
    End If
    rs.MoveNext
 Wend <== DO NOT USE!
 Loop

[NeoPa]

If you want to use Wend then it comes in the format below.

While {condition}
  ...
Wend

Otherwise, ADezii's Do...Loop is a perfectly adequate alternative.
The Do...Loop format is actually more flexible as you can use 'While' OR 'Until', and you can either put them with the 'Do' or with the 'Loop'.

[ljungers]

I forgot to thank you ADezii and NeoPa for your help. That VBA routine works now and performs the way it should.

Thanks to both of you.

[ADezii]

I forgot to thank you ADezii and NeoPa for your help. That VBA routine works now and performs the way it should.

Thanks to both of you.

Glad to help ya.