asked on

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

mbizup

---->>> IT_Skills = ChosenSkills

Change this line to

rs!IT_Skills = ChosenSkills

or alternatively:

rs("IT_Skills") = ChosenSkills

Jim Dettman (EE MVE)

or even more simply:

rs!IT_Skills = Me.ITSkillsPreview

Jim.

Tom Crowfoot

ASKER

Thanks for that - have tried both but get a run time error 3265 - Item not found in this collection

Jim Dettman (EE MVE)

Then IT_Skills is not a field in the forms data source.

Jim.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

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

Start Free Trial

Jim Dettman (EE MVE)

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.

SOLUTION

Jim Dettman (EE MVE)

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

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

Jim Dettman (EE MVE)

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.

Tom Crowfoot

ASKER

Thanks for these - works perfectly, hopefully got it the right accreditation & scoring this time?

Many thanks

Jim Dettman (EE MVE)

Yeah, that works.

Thanks,
Jim.