Re: Use of AddNew

I got it working. I replaced the following line:

Set rsSet2 = rsSet1.Clone(adLockOptimistic)

with this:

SQLStr = "select * from TABLE where RECID = '"'"

Set rsSet2 = New ADODB.Recordset
rsSet2.CursorType = adOpenKeyset
rsSet2.LockType = adLockOptimistic
rsSet2.Open SQLStr, DBConn, , , adCmdText



"Waldy" <waldy@xxxxxxxxxxx> wrote in message
news:%236C%23m8L0FHA.1032@xxxxxxxxxxxxxxxxxxxxxxx
> Hi there,
> I am using ADO and VB to try and create a new record but end
> up with the existing record being updated instead. Imagine the scenario
> where I open a record set that contains one database record. I then
> create another recordset by cloning the first one and calling AddNew. I
> then change the primary key field to have a new id value and call update.
> The reason that I am doing this instead of using a SQL statement is
> because the customer can add fields to this table in the future and I want
> this to still work and copy all the fields in the table. Have I totally
> misunderstood how this works?
>
> SQLStr = "select * from TABLE where RECID = '" & RecId & "'"
>
> Set rsSet1 = New ADODB.Recordset
> rsSet1.CursorType = adOpenKeyset
> rsSet1.LockType = adLockOptimistic
> rsSet1.Open SQLStr, DBConn, , , adCmdText
>
> Set rsSet2 = rsSet1.Clone(adLockOptimistic)
>
> rsSet2.AddNew
>
> For i = 0 To rsSet1.Fields.count - 1
> rsSet2(i) = rsSet1(i)
> Next i
>
> rsSet1.Close
> Set rsSet1 = Nothing
>
> rsSet2!RECID = RecID + 1
> rsSet2!OTHER = NewValue
> rsSet2!... = ...
>
> rsSet2.Update
> rsSet2.Close
> Set rsSet2 = Nothing
>


.