Trying to update information in a subform based on ADO recordset

[Robotron]

I am attempting to update data contained in a subform and I am getting the error:

Field 'Fieldname' is based on an expression and can't be edited.

I am populating the subform with the following code:

Private Sub SetSubFormRecordSource()
On Error GoTo Err_SetSubFormRecordSource

  Dim rst As ADODB.Recordset
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseClient
    
  rst.Open "SELECT * FROM tblDetail " _
    & "WHERE [Sys Emp ID Num]  = '" _
    & Forms("frmReconciliationDispositionGL")!txtSysEmpIdNum & "' " _
    & "ORDER BY Year, Month ", cnn1
  Set Me.frmEmpDetail.Form.Recordset = rst
  Me.frmEmpDetail.Requery
  rst.Close
  Set rst = Nothing

Exit_SetSubFormRecordSource:
    Exit Sub
Err_SetSubFormRecordSource:
    If Err.Number = 3709 Then   ' MyConn = nothing
        OpenConnection
        Resume
    Else
        MsgBox Err.Description, , Err.Number
        Resume Exit_SetSubFormRecordSource
    End If

End Sub

What is it that I am missing in this?

Robotron

 

[RoyVidar]

And on which line does the error occur?

Could it be related to you using reserwed words as field names? Both year and month are functions, and shouldn't be used as names of controls, fields... - some times you'll get around errors relating to that by surrounding the field names with [brackets]

Roy-Vidar

 

[PHV]

Why not simply do this ?
Me.frmEmpDetail.Form.RecordSource = "SELECT * FROM tblDetail " _
& "WHERE [Sys Emp ID Num] = '" _
& Forms("frmReconciliationDispositionGL")!txtSysEmpIdNum & "' " _
& "ORDER BY Year, Month "


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Robotron]

Roy, the error does not occur on any line. I get the message when I try to edit the data displayed in the subform.

PH, I do not want to do that because that would create more than one connection per user in the ldb file. I am using an existing ADO connection (cnn1) to get the records. I do this to avoid corruption in a multiuser environment with a database on a network.

 

[RoyVidar]

Except I'm using a keyset cursor and optimistic locking (your defaults to forwardonly and readonly), similar works on my setup. Note - there are some anomalities in 2000, making it a bit hard to update unless you use some "datashape" provider (heve just read this somewhere, never tried it), but for xp+ it should work (with the mentioned cursor, locking).

Roy-Vidar

 

[Robotron]

Ok Roy, I set the CursorType and LockType. Now I get the message 'This recordset is not updateable'. That confuses me because I am pulling records from only one table. Where should I look next?

Robotron

 

[Robotron]

I was thinking about this last nite. Would the recordset not be updateable because I am closing it in the code?

All help is appreciated.

Robotron

 

[RoyVidar]

I've really only tested this, not used it in production, but on my tests (xp, not 2000 of course), updates where OK even if I closed the recordset in the forms load event, making it "bound". So - I don't know - you could of course try to update the recordset programatically, and see if that worked, try static cursor, different locking... and see if any of those worked. Did you change or bracket the reserwed words field names? Or hope that someone whos met this challenge pops in...

Roy-Vidar

 

[Robotron]

Just for the next person that comes along trying to do this...

I did some more research on this. I found out that a subform that uses data based on an ADO recordset is not updateable. Period. This is coming from Russell Sinclair who wrote "From Access to SQL Server".

The workaround is to make the subform bound to a local table in the front end and make sure the linked table is updated if the local table gets updated.

Robotron

 

[RoyVidar]

Ouch - I'll see if a serious beating, a cup of coffee in the keyboard or something else teaches my subform to behave then, cause it still works (xp) ;-)

Roy-Vidar

 

[Robotron]

Roy,

I have Access 2002. Perhaps the problem has been corrected. That's good news.

Robotron