Solved

[This Recordset is not updateable] error when opening a form.

Posted on 2008-10-21
17
467 Views
Last Modified: 2013-11-25
I am getting "Run time error '-2147352567(80020009) [This Recordset is not updateable]' when i open the form.  Under form load i have the following code:

Private Sub Form_Load()
    Set rs = Me.Recordset
    rs.MoveFirst
    Do Until rs.EOF
        Me.Customer_Lookup = Me.CName
        rs.MoveNext
    Loop
    Set rs = Nothing
End Sub

It was working fine earlier.  Not sure what happened.  Please help!!!!!!
0
Comment
Question by:ajitxd
  • 6
  • 6
  • 4
17 Comments
 
LVL 75
ID: 22770784
Maybe
Set rs = Me.RecordsetClone   ' ** use the Clone

Also

Me.Customer_Lookup = Me.CName

What are you actually doing with the Recordset ?

mx
0
 

Author Comment

by:ajitxd
ID: 22770789
I started a brand new database from scratch with just one form and one query and i am still getting this.
0
 

Author Comment

by:ajitxd
ID: 22770804
All i am trying to do is ... copy the values from field [CName] to [Customer_Lookup] for all the records in that particular query behind the form.
0
 
LVL 75
ID: 22770809
What is that code supposed to be doing ?

mx
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22770833
What is the datasource for the form?  Does it include all of the columns of the table, or is it based on a Query?

Typically, a recordset will not be updatable unless you have selected the primary key on the table.  If the table doesn't have a primary key, or if you have excluded it from the select statement then the recordset may not be updatable.

Also, querys that join two tables together may not be updatable, because Access has trouble figuring out which table to update.

I assume this code is part of a Form in Access?  Or is this in VB?

The logic inside of your Do loop doesn't make much sense to me either, as you'll be overlaying the value in Me.Customer_Lookup every time with the same value, whatever is in Me.CName... and, that field isn't part of the recordset, so, you're not using any fields from the recordset at all.
0
 
LVL 75
ID: 22770844
is Customer_Lookup a Control or a field in the table?

What about:

Private Sub Form_Load()
    Dim rs as Dao.Recordset                  '* you need this - unless defined elsewhere ?
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    Do Until rs.EOF
        Me![Customer_Lookup] = Me![CName]
        rs.MoveNext
    Loop
    Set rs = Nothing
End Sub
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22770849
If all you want to do is update your database, you can create a query and set it as an Update query, then just run the query and it will update your database without you having to code anything  (just point and click in the Query builder).
0
 

Author Comment

by:ajitxd
ID: 22770952
i get the same error with the above code!
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:ajitxd
ID: 22770973
customer_lookup is a field on the table
0
 
LVL 75
ID: 22770992
Can you post the SQL for the query driving the form?

mx
0
 

Author Comment

by:ajitxd
ID: 22771025
now it says it cant find field 'CName' in the expression.  the query behind the form links 2 table and the field [Cname] come from a table named [NoDups_CreditMapShortList].
0
 

Author Comment

by:ajitxd
ID: 22771034
SELECT Facilities_Exposure.*, NoDups_CreditMapShortList.[Crisis Name]
FROM Facilities_Exposure INNER JOIN NoDups_CreditMapShortList ON Facilities_Exposure.[Customer SDS ID] = NoDups_CreditMapShortList.Id;
0
 
LVL 75
ID: 22771093
Typically, a query with 2 or more tables in a Join is not updatable.

Try just running the query from the database window and see if you can update the field?

mx
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22771140
Normally, if you were looping through a recordset, then your references inside the loop should be pointing to RS not ME

Private Sub Form_Load()
    Dim rs as Dao.Recordset                  '* you need this - unless defined elsewhere ?
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    Do Until rs.EOF
        rs!Customer_Lookup = rs!CName
        rs.MoveNext
    Loop
    Set rs = Nothing
End Sub

As I said above, a recordset might not be updatable, if it includes a JOIN between two tables.  You might make it updatable, if you include the primary keys of both tables in the list of columns selected, even if you never display them on your form, but sometimes it works, sometimes it doesn't.
0
 
LVL 75
ID: 22771161
"then your references inside the loop should be pointing to RS not ME"

I glazed right over that one ... good catch!

mx
0
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
ID: 22771392
I know that when you move through a form's recordset, Access does an implicit commit on every move if the data has changed, but behind the scenes, editing a value in a textbox, say, causes a recordset.Edit statement, and the move causes a Recordset.Update statement.

When updating the recordset in the loop, do you think he'd need to explicitly issue the Edit and Updates?

Private Sub Form_Load()
    Dim rs as Dao.Recordset                  '* you need this - unless defined elsewhere ?
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        rs!Customer_Lookup = rs!CName
        rs.Update
        rs.MoveNext
    Loop
    Set rs = Nothing
End Sub

In any event, creating an Update Query through the Query builder would be a lot easier way to update this table...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now