recordset clone

Dear Experts

i am getting in a mess with trying to do a recordset clone which is designed to strip out extra text and replace it with a "~".  The replacing of text works against individual records but I cannot get the recordset clone to work across all records in the form.

The code is below - can anyone help

 
Private Sub Command6_Click()
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Dim varVal
varVal = Me.Contents
'If IsNull(varVal) Then Exit Sub
varVal = Replace(CStr(varVal), "Please subscribe me to: Weekly Update", "Weekly Update")
varVal = Replace(CStr(varVal), "Please subscribe me to: Sub Sahara Newsletter", "Sub Sahara Newsletter")
varVal = Replace(CStr(varVal), "Please subscribe me to: Monthly Operations Update", "Monthly Operations Update")
varVal = Replace(CStr(varVal), "Please subscribe me to: XXXXXX", "Weekly Update")
varVal = Replace(CStr(varVal), "My details are as follows:", "")
varVal = Replace(CStr(varVal), Chr(10), "")
varVal = Replace(CStr(varVal), "Email Address: ", ">")
varVal = Replace(CStr(varVal), "Name: ", ">")
varVal = Replace(CStr(varVal), "Company: ", ">")
varVal = Replace(CStr(varVal), "Job Title: ", ">")
varVal = Replace(CStr(varVal), ">", "~")
Me.Contents = varVal
.MoveNext
Loop
End With

End Sub

Open in new window

correlateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kmslogicCommented:
Put rs.Edit before your loop starts and rs.Update before the Loop statement at the end.
correlateAuthor Commented:
hi kmslogic

Thanks for this, I'm still getting no joy with it looping through to the end,  I have added the bit you mentioned & have played around further, but alas no joy.  I have attached the code as far as I have got
Private Sub Command6_Click()
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
rs.Edit
Dim varVal
varVal = Me.Contents
'If IsNull(varVal) Then Exit Sub
varVal = Replace(CStr(varVal), "Please subscribe me to: Weekly Update", "Weekly Update")
varVal = Replace(CStr(varVal), "Please subscribe me to: Sub Sahara Newsletter", "Sub Sahara Newsletter")
varVal = Replace(CStr(varVal), "Please subscribe me to: Monthly Operations Update", "Monthly Operations Update")
varVal = Replace(CStr(varVal), "Please subscribe me to: karen.dalby@correlatesearch.com", "Weekly Update")
varVal = Replace(CStr(varVal), "My details are as follows:", "")
varVal = Replace(CStr(varVal), Chr(10), "")
varVal = Replace(CStr(varVal), "Email Address: ", ">")
varVal = Replace(CStr(varVal), "Name: ", ">")
varVal = Replace(CStr(varVal), "Company: ", ">")
varVal = Replace(CStr(varVal), "Job Title: ", ">")
varVal = Replace(CStr(varVal), ">", "~")
Me.Contents = varVal
rs.Update
rs.MoveNext
Loop
End Sub

Open in new window


Any ideas?
ralmadaCommented:
do you have any subform in there? if so, try making it explicit

...
Dim rs As DAO.Recordset
Set rs = Me![yoursubformhere].Form.RecordsetClone
...
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

correlateAuthor Commented:
No subform is used in here I'm afraid, all it seems to do is change the first record and leaves the others
kmslogicCommented:
Update looks good, the Edit should be *above* where the loop starts.  Also the recordset must be updatable (the readonly flag rs.Updatable must be true).  

If you are updating every record in the table with that button you may consider moving your code to a function in a module and then executing an update SQL statement:

add a new module, and insert:
public function CleanContents
   dim varVal as Variant

   varVal = Me.Contents
   varVal = Replace(CStr(varVal), "Please subscribe me to: Weekly Update", "Weekly Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Sub Sahara Newsletter", "Sub Sahara Newsletter")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Monthly Operations Update", "Monthly Operations Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: karen.dalby@correlatesearch.com", "Weekly Update")
   varVal = Replace(CStr(varVal), "My details are as follows:", "")
   varVal = Replace(CStr(varVal), Chr(10), "")
   varVal = Replace(CStr(varVal), "Email Address: ", ">")
   varVal = Replace(CStr(varVal), "Name: ", ">")
   varVal = Replace(CStr(varVal), "Company: ", ">")
   varVal = Replace(CStr(varVal), "Job Title: ", ">")
   varVal = Replace(CStr(varVal), ">", "~")
   CleanContents=VarVal
end function

And then in the code for the click button:

currentdb.execute "UPDATE MyTable SET Contents=CleanContents(Contents)"

You'd also probably want to set your current position by storing a bookmark to your form's recordset

dim bkHere as Bookmark
bkHere = me.recordset.bookmark
me.requery
me.recordset.bookmark = bkHere

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:
Hi kmslogic,

thanks for this, I've done as you suggested & place the following into a funtion (Attached)
Public Function CleanContents()
   Dim varVal As Variant
   varVal = Me.Contents
   varVal = Replace(CStr(varVal), "Please subscribe me to: Weekly Update", "Weekly Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Sub Sahara Newsletter", "Sub Sahara Newsletter")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Monthly Operations Update", "Monthly Operations Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: xxxxx", "Weekly Update")
   varVal = Replace(CStr(varVal), "My details are as follows:", "")
   varVal = Replace(CStr(varVal), Chr(10), "")
   varVal = Replace(CStr(varVal), "Email Address: ", ">")
   varVal = Replace(CStr(varVal), "Name: ", ">")
   varVal = Replace(CStr(varVal), "Company: ", ">")
   varVal = Replace(CStr(varVal), "Job Title: ", ">")
   varVal = Replace(CStr(varVal), ">", "~")
   CleanContents = varVal
End Function

Open in new window


and the following into the the button

 
Private Sub Clean_Click()
CurrentDb.Execute "UPDATE WeeklyUpdate SET Contents=CleanContents(Contents)"
End Sub

Open in new window


But alas I'me getting a run time error - 3075 (Alas Help doesn't do what it says on the tin & I haven't been able to work this one out)

 Error
Sorry, Any ideas?

ralmadaCommented:
the function should be declared as

Public Function CleanContents(Contents as string)
ralmadaCommented:

oops try like this
Public Function CleanContents(Contents as string) as string
   Dim varVal As Variant
   varVal = Contents
   varVal = Replace(CStr(varVal), "Please subscribe me to: Weekly Update", "Weekly Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Sub Sahara Newsletter", "Sub Sahara Newsletter")
   varVal = Replace(CStr(varVal), "Please subscribe me to: Monthly Operations Update", "Monthly Operations Update")
   varVal = Replace(CStr(varVal), "Please subscribe me to: xxxxx", "Weekly Update")
   varVal = Replace(CStr(varVal), "My details are as follows:", "")
   varVal = Replace(CStr(varVal), Chr(10), "")
   varVal = Replace(CStr(varVal), "Email Address: ", ">")
   varVal = Replace(CStr(varVal), "Name: ", ">")
   varVal = Replace(CStr(varVal), "Company: ", ">")
   varVal = Replace(CStr(varVal), "Job Title: ", ">")
   varVal = Replace(CStr(varVal), ">", "~")
   CleanContents = varVal
End Function

Open in new window

kmslogicCommented:
right good catch ralmada.
kmslogicCommented:
Although I'd make it Contents as Variant to handle possible null values in the db
correlateAuthor Commented:
Thank you both, works a treat
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.