Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Recordset clone problem

Posted on 2013-10-31
11
455 Views
Last Modified: 2013-10-31
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
Comment
Question by:correlate
  • 6
  • 3
  • 2
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39613695
---->>> IT_Skills = ChosenSkills

Change this line to

rs!IT_Skills = ChosenSkills

or alternatively:


rs("IT_Skills") = ChosenSkills
0
 
LVL 57
ID: 39613704
or even more simply:

 rs!IT_Skills = Me.ITSkillsPreview

Jim.
0
 

Author Comment

by:correlate
ID: 39613706
Thanks for that - have tried both but get a run time error 3265 - Item not found in this collection
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 57
ID: 39613710
Then IT_Skills is not a field in the forms data source.

Jim.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 39613711
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
 
LVL 57
ID: 39613717
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 total points
ID: 39613720
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
 

Author Comment

by:correlate
ID: 39613734
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
 
LVL 57
ID: 39613794
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
 

Author Closing Comment

by:correlate
ID: 39613968
Thanks for these - works perfectly, hopefully got it the right accreditation & scoring this time?

Many thanks
0
 
LVL 57
ID: 39614069
Yeah, that works.  

 Thanks,
Jim.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question