Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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

0
correlate
Asked:
correlate
  • 6
  • 3
  • 2
2 Solutions
 
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)PresidentCommented:
or even more simply:

 rs!IT_Skills = Me.ITSkillsPreview

Jim.
0
 
correlateAuthor Commented:
Thanks for that - have tried both but get a run time error 3265 - Item not found in this collection
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Then IT_Skills is not a field in the forms data source.

Jim.
0
 
mbizupCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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)PresidentCommented:
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)PresidentCommented:
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)PresidentCommented:
Yeah, that works.  

 Thanks,
Jim.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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