Recordset clone problem

Dear Experts

I am trying to build an "Update All" button which will add pre selected text to every record on a form, but it doesn't work & only updates the first record.  The fields involved are as follows:

IT_Skills = the field to be updated

ITSkillsPreview = the chosen text to be added to each record

My code is below, can anybody help?

Private Sub SelectITSkills4All_Click()
Dim ChosenSkills As String
ChosenSkills = Me.ITSkillsPreview
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
rs.Edit
IT_Skills = ChosenSkills
rs.Update
rs.MoveNext
Loop
End Sub

Open in new window

correlateAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Are you sure that the field is actually in your form's recordsource... checked for typos, made sure that the underscore is not actually a space, checked for table prefixes etc?
0
 
mbizupCommented:
---->>> IT_Skills = ChosenSkills

Change this line to

rs!IT_Skills = ChosenSkills

or alternatively:


rs("IT_Skills") = ChosenSkills
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
or even more simply:

 rs!IT_Skills = Me.ITSkillsPreview

Jim.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
correlateAuthor Commented:
Thanks for that - have tried both but get a run time error 3265 - Item not found in this collection
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Then IT_Skills is not a field in the forms data source.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
By the way, it's also a good idea in VBA code to close any object you open and set object variables to nothing, so at the end of your procedure, do this:


 Set rs = nothing

  This doesn't have anything to do with your current problem, but can lead to other problems.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
You'd also execute a tad faster if you did:

Private Sub SelectITSkills4All_Click()

Dim rs As dao.Recordset

Set rs = Me.RecordsetClone

With rs
.MoveFirst

Do Until .EOF
  .Edit
  !IT_Skills = Me.ITSkillsPreview
  .Update
  .MoveNext
Loop

End With
Set rs = nothing

End Sub
0
 
correlateAuthor Commented:
Works perfectly Thank you very much - the only other thing I did was to remove change the field name from IT Skills to ITSkills in case the space was interfering with it
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I think you accepted the wrong comment on this.  Mbizup was the first to give you the correct answer.  

 I just threw in some extra comments, but in regards to your initial question, she answered it first.

 As I happen to be one of the Topic Advisors for the Access area (and mbizup is as well BTW), I'm going to un-accept this.

 Please accept her first comment as the anwer and you can give me an assist if you want for the extra stuff I threw in.

Jim.
0
 
correlateAuthor Commented:
Thanks for these - works perfectly, hopefully got it the right accreditation & scoring this time?

Many thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yeah, that works.  

 Thanks,
Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.