• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

ADP: "This recordset is not updatable"-- problem with form SQL

Hi all,

I have an ADP project with a major subform as one of about 5 tabs.  On one subform in the tab named "Customer File", I have the SQL of the form regularly updating to reflect different records.  This form works great and I can update it without any problem.  But on another tab, I have a form called "For Sales" where information from an inventory table (tblinv) displays info.  However, anytime I go to any record, the query isn't updatable.  I've checked the form itself and it is set (like the other one) as an Updatable Snapshot showing up to 10,000 records at a time from the SQL Server datasource.

My understanding is that for a query to be updatable, these are the rules:

The select list can't include a DISTINCT or TOP clause.    
The select list can't include an aggregate function.    
The select list can't include a calculated value.    
The SELECT statement can't include a GROUP BY or HAVING clause.  

Here is my SQL that I feed to the form for it to update--in full compliance with the above:

sql = "SELECT tblinv.* FROM tblinv WHERE (tblinv.status='FOR SALE' Or tblinv.status='Available') AND tblinv.InvID=" & i

Am I missing something?  Why do I still get the error:  "This recordset is not updatable" in the lower left corner of Access when trying to add or edit a field?

Thank you in advance for your help--

  • 3
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the table have a primary key?
normenclatureAuthor Commented:
it should-- let me check...
normenclatureAuthor Commented:
holy dumb data, Batman!  the InvID is NOT a primary key!!  That's probably the issue!  Thanks!  Let me make that happen
normenclatureAuthor Commented:
That was it!!  Thanks-- you're an ANGEL!!  (no pun intended)

I could only change the ID field to a PRIMARY key using Enterprise Manager.  As soon as I did, it worked perfectly.

Thanks again!


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now