Edit ADO Recordset

[gray]

Hi

Anyone know where I'm going wrong here? Trying to
1. Add a record to an ADODB recordset
2. Call update to ensure a new record is created and a value given to my autonumber field (TBL1.Unique_No)
3. Scroll back to the new record again
4. Edit a field in that new record based on the newly created autonumber.

I get an error

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

Here's my code:-

Public frm_rstADO As New ADODB.Recordset
Public frm_Cnn As ADODB.Connection   
 
Set frm_Cnn = CurrentProject.Connection
 
With frm_rstADO
    Set .ActiveConnection = frm_Cnn
    .Source = SQLLine
    .LockType = adLockOptimistic
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .Open
End With
 
frm_rstADO.AddNew
frm_rstADO![TBL1.Date_Record_Added] = Now()
frm_rstADO![TBL1.Record_Status] = "(+)"
frm_rstADO.Update
 
Main_Rec_Unique_No = frm_rstADO![TBL1.Unique_No]
 
frm_rstADO.MoveFirst
frm_rstADO.Find "[TBL1.Unique_No]=" & 
Main_Rec_Unique_No, 0, adSearchForward, 1
If frm_rstADO.BOF Or frm_rstADO.EOF Then
    MsgBox "Can't Find New Record!!"
End If
 
frm_rstADO![TBL1.ID] = Main_Rec_Unique_No
frm_rstADO.Update  <------ fails

It fails at the second update? I know with DAO you had to do an Rs.Edit but how do you do it in ADO please?

I tried a requery after the update but that crashes it too.

Thnx

 

[Guus2005]

you must do .Edit before you do .update

I would suggest that you exit the procedure when you don't find a new record.

frm_rstADO.MoveFirst
frm_rstADO.Find "[TBL1.Unique_No]=" & [COLOR="Red"]_[/COLOR]
Main_Rec_Unique_No, 0, adSearchForward, 1
If frm_rstADO.BOF Or frm_rstADO.EOF Then
    MsgBox "Can't Find New Record!!"
    [COLOR="red"]exit sub[/COLOR]
End If

[COLOR="red"]frm_rstADO.edit [/COLOR]
frm_rstADO![TBL1.ID] = Main_Rec_Unique_No
frm_rstADO.Update  <------ fails

 

[vbaInet]

To get you going grey, here are some ADO code examples:

http://allenbrowne.com/func-ado.html

 

[Galaxiom]

you must do .Edit before you do .update

No. DAO requires Edit but it is not used in ADO. The current record can be edited any time by simply changing the values of the fields.

Try using CurrentProject.AccessConnection.

 

[gray]

Hi

Thanks for the replies.

Unfortunately, I originally had my CNX set to CurrentProject.AccessConnection but it would not allow me to do .addNews ... I could only get that with CurrentProject.Connection.... so looks like I'll have to think of something else... rgds

 

[LPurvis]

A few questions spring to mind.

• First of all - storing a value in another field based on the autonumber field value is textbook data redundancy (violating normalisation). Do you even need to do this at all? What purpose does it serve?
• What's the source of this data? An MDB/ACCDB file (aka "Access" db :-s) or a server RDBMS (usually SQL Server)?
  If SQL Server then your Identity value is "lazy" so you need to commit the row first. If not then consider the fact that you don't need to use a client side cursor - and by keeping the cursor server side you can retrieve the new autonumber value before even committing (calling Update). Even after committing in an ADO recordset, the cursor should remain exactly where it was - on the newly inserted row (i.e. no need to attempt to Find that row again as you would in DAO after an Update call).
• That your recordset source fields are referenced such as "TBL1.Unique_No" implies that SQLLine is a query joining more than one table. If the case then a) why and b) why and c) do you need this, as you don't seem to reference any other table data.

See how you get on.

Cheers

 

[gray]

Hi

Thanks for the reply.

"storing a value in another field based on the autonumber field"

I use an "ID" field to link a family of documents... the first autonumber , "Unique_No", created for any given family is used as the common ID number for all members. So the need to collect and re-write the autonum field in "ID" only happens once.

"What's the source of this data?"

It's an Access 2007 mdb file using native local-machine Access Db (but will be split FE/BE sooner or later)

"source fields are referenced such as "TBL1.Unique_No"

Yes, I sometime need to join a table for sorting purposes, sometimes I don't.

Actually I have had another problem with this... Once the RS is built and the .addnew is completed, I attach the Rs to the form with ...

Set me.form.recordset=frm_rstADO

But when I alter the record in the form and (if I) move the focus, I get the Write Conflict box... another user has amended this data?.... they haven't tho' (there are no other users)... I presume Access thinks the .addnew & .update is done by one user, but when the record is edited in the form it thinks it's another user??



BTW
I've had real joy with joins and table/column aliasing.. The controls are bound so they need a 'permanent' controlsource, [TBL1.ID] for example. It seems that aliases only get applied when tables are joined and there is a common column, take, [ID] for example.. so the joined Rs has [TBL1.ID] and [TBL2.ID]... remove the join and [TBL1.ID] now becomes [ID]... even though the first table is still selected as TBL1... resulting in a dozen invalid controls and references....

Also, I've noticed some of the procs don't seem to like "." in column names even when they are in square brackets... and it's a legitimate notation afterall.

To get round this, I've had to create 'permanent' aliases for every column... so now instead of [TBL1.ID]
and [TBL2.ID] I've had to string with underscores...

SELECT TBL1.ID As TBL1_ID, TBL2.ID As TBL2_ID... etc FROM xxx As TBL1....... etc

Unwieldly, bt at least in this way I'm able to rely on the controlsources being valid....

Any advice on this please?

cheers