?
Solved

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

Posted on 2008-10-21
17
Medium Priority
?
508 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
[X]
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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

777 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