asked on

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

kmslogic

Put rs.Edit before your loop starts and rs.Update before the Loop statement at the end.

Tom Crowfoot

ASKER

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?

ralmada

do you have any subform in there? if so, try making it explicit

...
Dim rs As DAO.Recordset
Set rs = Me![yoursubformhere].Form.RecordsetClone
...

Tom Crowfoot

ASKER

No subform is used in here I'm afraid, all it seems to do is change the first record and leaves the others

ASKER CERTIFIED SOLUTION

kmslogic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Crowfoot

ASKER

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)

Sorry, Any ideas?

SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

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

kmslogic

right good catch ralmada.

kmslogic

Although I'd make it Contents as Variant to handle possible null values in the db

Tom Crowfoot

ASKER

Thank you both, works a treat