Solved

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

Posted on 2008-10-21
17
478 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

805 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