This recordset is not updateable.

[AgisConsult]

Hi,

I joined the forum as I am getting nowhere is resolving a problem with a recordset - basically I want the update the related form but I get the message that the 'Recordset is not updateable.'

I'm using Access 2007 and the Recordset Type is 'Dynaset' - I've tried Dynaset (inconsistent updates) to no avail.

The recordset is getting the data from one table. Here's the event procedure when the form is opened :-

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT DISTINCT Lease_Ref, * FROM tblFL_Header " & Me.OpenArgs
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

I want to be able to update the form so that the data in table 'tblFL_Header' will be updated.

Any help is appreciated!

Regards

ColinH

 

[namliam]

SELECT DISTINCT Lease_Ref, * FROM tblFL_Header

DISTINCT is your problem, distinct makes from 2 or more duplicated records 1 visible record. From that point onwards you cannot change this data.

 

[AgisConsult]

Hi Mailman,

I actually added the DISTINCT entry to try and resolve the problem - I have retried, removing DISTINCT, and I still have the same error.

Any other options to try?

Regards

Colin

 

[namliam]

I am seeing you are feeding the RS into a form? LOL Didnt even know that was possible?

This can actualy be the problem, as the form itself isnt exactly "bound" to the table/query.

Have you tried doing simply adding the query into the form instead of the actual recordset?
Me.Recordsource = "Yoursql"

I think...

 

[AgisConsult]

Hi Mailman,

It worked - thanks a million!

So I now know not to feed a RS into a form - I won't try that again!

Thanks,

Colin