Solved

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

Posted on 2008-10-21
17
481 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
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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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