• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

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

0
correlate
Asked:
correlate
  • 4
  • 4
  • 3
2 Solutions
 
kmslogicCommented:
Put rs.Edit before your loop starts and rs.Update before the Loop statement at the end.
0
 
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?
0
 
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
...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

0
 
ralmadaCommented:
the function should be declared as

Public Function CleanContents(Contents as string)
0
 
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

0
 
kmslogicCommented:
right good catch ralmada.
0
 
kmslogicCommented:
Although I'd make it Contents as Variant to handle possible null values in the db
0
 
correlateAuthor Commented:
Thank you both, works a treat
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now