Update Field in ADODB Form RS

[gray]

Hi

WinXpPro Sp2
Access2002/2007

Does anyone know how to achieve this please? I've spent 3 x 12/14 hour days trying to get it to work. I really don't think what I'm trying to achieve is so spectacular?

Simple Form with a Subform. The idea is to permit users to deselect and/or re-order the order of parameters used in things like orderbys or fields on printed reports. See attached screen shot for a flavour.

Because I have some checking to do, I use SQL Transactions so that the users' changes don't get commited until the appropriate time (if at all).

I tried with DAO as my subform recordset but I need a Sort capability so that it gets displayed correctly...so I moved to ADODB for the subform's recordset.

Whenever I try to edit a field and .Update it I get errors... usually this one.

Error No: -2147217864 - Error Decription: Row cannot be located for updating. Some values may have been changed since it was last read.

I make these declarations in the main form module.

Public Edit_rstADO As New ADODB.Recordset
Public Edit_Cnn As ADODB.Connection
Public Edit_Transaction_Begun As Boolean

In the Form-Open I build the transaction resources

 'Set up Transaction resources
Set Edit_Cnn = CurrentProject.AccessConnection
 
'Set SQL String to Load an empty recordset 
SQLLine = "SELECT TBL1.* FROM Form_Sort_And_Filter_Fields AS TBL1 WHERE Unique_No = -999" 
 
'Open the RS
Edit_rstADO.Open SQLLine, Edit_Cnn, adUseClient, adLockOptimistic
Edit_Cnn.BeginTrans
Edit_Transaction_Begun = True

I then manually add some records into the rs - just 1 shown here

'Add some records into the RS
Edit_rstADO.AddNew
Edit_rstADO![Table_Friendly_Name] = Sort_Args(1)
Edit_rstADO![Field_Friendly_Name] = Sort_Args(2)
Edit_rstADO![OrderBy_Direction] = Sort_Args(3)
Edit_rstADO![Field_Selected] = True
Edit_rstADO![List_Order] = List_Order_No
Edit_rstADO.Update

'Set the sort order on the RS and point subForm at it

Edit_rstADO.Sort = "List_Order"
Set My_Fields_SubForm.Form.Recordset = Edit_rstADO

So far so good!

I have move-up & move-down buttons which do the following:-

Move-Up button
a) Grabs the Unique_No (PKey) and existing List_Order_No of current record.
b) Substracts 1 from the current row's List_Order_No
c) Does a FIND to see which record 'owns' that list order no. moves to that record and does an update of List_Order_No to demote it.
d) Does a FIND to go to previously selected record and does an update of the List_Order_No to promote it.

Up-Button-Click

'Dim all the variables here.........including another Rs:-
Dim....
Dim....
Dim rst As New ADODB.Recordset
 
Set Cntrlbx = My_Fields_SubForm
 
Extg_List_Order = Cntrlbx.Form!List_Order
Trgt_List_Order = (Extg_List_Order) -1
 
Set rst = Cntrlbx.Form.RecordsetClone
rst.Find "List_Order=" & Trgt_List_Order
If rst.EOF Then
       MsgBox "Ouch"
End If
 
'Move the Form Rs to the record just found
Cntrlbx.Form.Recordset.Bookmark = rst.Bookmark
Cntrlbx.Form.Recordset.Fields("List_Order") = Extg_List_Order
Cntrlbx.Form.Recordset.Update
 
etc etc....

And it's at the .Update that it fails.... If I leave thr .Update out it fails when trying to move to another record.

I've tried different cursors, connections, AddNews before and after assignment of the subform Rs... I saw threads talking about having no defaults set up for nunber fields.... well, you name it, I've tried it...

Thanks

 

[jdraw]

When you manually Add a new record, you may have to Refresh the recordset. I'm not sure, but just quickly reading your post, it seems what is "in memory" may not be what was written to the table.

 

[gray]

Certainly worth a go thanks... I added a referesh after setting the form recordset.. alas no joy...

 

[gray]

I think I've resolved this...

Opened the form RS with:

Edit_rstADO.Open SQLLine, Edit_Cnn, adUseClient, adLockOptimistic

Trapped the "error" which I don't think is really an error??

If Err.Number = -2147217864 Then
        Resume Next
    End If

(Great that... we were taught never, never, never make use of error trapping... but there you go... 21st Century I.T.!!)

Create an RsClone for navigating records and then divorce the subForm from the Rs:

Set rst = Cntrlbx.Form.RecordsetClone
Set Cntrlbx.Form.Recordset = Nothing

Using the cloned Rs, navigate to whichever record needs editing, perform the edit of the field and then update the Rs

Edit_rstADO.Bookmark = rst.Bookmark
Edit_rstADO.Fields("List_Order") = Extg_List_Order
Edit_rstADO.Update

Reset the Rs Sort, re-Marry the Rs to the subform, then for good measure, re-OrderBy the subform:-

Edit_rstADO.Sort = "List_Order"
Set Cntrlbx.Form.Recordset = Edit_rstADO
 
Cntrlbx.Form.OrderBy = "List_Order"
Cntrlbx.Form.OrderByOn = True

Geez...... !

 

[VilaRestal]

Well if you've solved it great but I thought you needed to say Edit before changing a field in a recordset:

Cntrlbx.Form.Recordset.Fields("List_Order") = Extg_List_Order
Cntrlbx.Form.Recordset.Update

Should be

Cntrlbx.Form.Recordset.Edit
Cntrlbx.Form.Recordset.Fields("List_Order") = Extg_List_Order
Cntrlbx.Form.Recordset.Update


Should it not????

 

[gray]

Hi

I thought so too.... But turns out it's not needed for an ADO Rs (rather than DAO)... found this ....

' ADO is naturally in edit mode, so do not use "rs.Edit"
rs![LastName] = "Smith-Jones"
rs.Update

in this useful guide....

http://www.classanytime.com/mis333k/sjdaoadorecordsets.html#editADO

Cheers