By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Getting error on VBA routine

100+
P: 114
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:
================================================== ===
Expand|Select|Wrap|Line Numbers
  1. Private Sub FixMemoField_Click()
  2. On Error GoTo Err_FixMemoField_Click
  3.  
  4. Dim rs As DAO.Recordset
  5. Set rs = CurrentDb.OpenRecordset("Translated_memo")
  6.  
  7. While Not rs.EOF
  8. rs.Update
  9. rs!memofieldname = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
  10. rs!memofieldname = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
  11. rs.Update
  12. rs.MoveNext
  13. Wend
  14.  
  15. Exit_FixMemoField_Click:
  16.     Exit Sub
  17.  
  18. Err_FixMemoField_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_FixMemoField_Click
  21.  
  22. End Sub
Mar 3 '07 #1
Share this Question
Share on Google+
8 Replies


100+
P: 114
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.
Mar 3 '07 #2

NeoPa
Expert Mod 15k+
P: 31,263
Go back to the original version but replace the first rs.Update with an rs.Edit.
So, you should have :
Expand|Select|Wrap|Line Numbers
  1.   ...
  2.   rs.Edit
  3.   'Various lines of updating
  4.   rs.Update
  5.   rs.MoveNext
  6.   ...
Mar 4 '07 #3

ADezii
Expert 5K+
P: 8,607
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:
================================================== ===
Expand|Select|Wrap|Line Numbers
  1. Private Sub FixMemoField_Click()
  2. On Error GoTo Err_FixMemoField_Click
  3.  
  4. Dim rs As DAO.Recordset
  5. Set rs = CurrentDb.OpenRecordset("Translated_memo")
  6.  
  7. While Not rs.EOF
  8. rs.Update
  9. rs!memofieldname = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
  10. rs!memofieldname = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
  11. rs.Update
  12. rs.MoveNext
  13. Wend
  14.  
  15. Exit_FixMemoField_Click:
  16.     Exit Sub
  17.  
  18. Err_FixMemoField_Click:
  19.     MsgBox Err.Description
  20.     Resume Exit_FixMemoField_Click
  21.  
  22. End Sub
Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Set rs = CurrentDb.OpenRecordset("Translated_memo")
  3.  
  4. Do While Not rs.EOF
  5.    If Not IsNull(rs![memofieldname]) Then
  6.        rs.Edit
  7.           rs!memofieldname = Replace(rs!memofieldname, "<crlf>", Chr(13) & Chr(10))
  8.           rs!memofieldname = Replace(rs!memofieldname, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
  9.        rs.Update
  10.    End If
  11.    rs.MoveNext
  12. Loop
  13.  
  14. rs.Close
Mar 4 '07 #4

100+
P: 114
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
Mar 9 '07 #5

ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Do While Not rs.EOF
  2.     If Not IsNull(rs![trans_memo]) Then
  3.         rs.Edit
  4.             rs!trans_memo = Replace(rs!trans_memo, "<crlf>", Chr(13) & Chr(10))
  5.             rs!trans_memo = Replace(rs!trans_memo, "<crlflf>", Chr(13) & Chr(10) & Chr(10))
  6.         rs.Update
  7.     End If
  8.     rs.MoveNext
  9. Wend <== DO NOT USE!
  10. Loop
Mar 9 '07 #6

NeoPa
Expert Mod 15k+
P: 31,263
If you want to use Wend then it comes in the format below.
Expand|Select|Wrap|Line Numbers
  1. While {condition}
  2.   ...
  3. 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'.
Mar 9 '07 #7

100+
P: 114
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.
Mar 11 '07 #8

ADezii
Expert 5K+
P: 8,607
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.
Mar 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.