Using an ADO recordset, I issue an AddNew, then populate, then issue an
Update. If the primary key is an autonumber field, how do I know what that
new column value is? Will MoveLast do it? Or does the recordset pointer
remain on the record I just updated?
--
Jim

Haven't tested with ADO, but with DAO, you can simply refer to the
Autonumber field to find out its value.

rs.AddNew
rs!Field1 = "abc"
Msgbox "I just added a record with Autonumber " & rs!Id


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JimS" <(E-Mail Removed)> wrote in message
news:95075260-1E26-4784-AC0C-(E-Mail Removed)...
> Using an ADO recordset, I issue an AddNew, then populate, then issue an
> Update. If the primary key is an autonumber field, how do I know what that
> new column value is? Will MoveLast do it? Or does the recordset pointer
> remain on the record I just updated?
> --
> Jim

With DAO, you have the automatically generated key after the AddNew, but if
you don't capture it, be aware that after the Update, the recordset will
likely be focused at the record you were before the AddNew.
With ADO, you can read the key after the AddNew since the record with the
focus will be the one you just added.



The following code illustrates the behavior about which record is the
current record after you append a new record:

===================
Public Sub ADODAO()
Dim rst As DAO.Recordset
Dim uvw As New ADODB.Recordset
Set rst = CurrentDb.OpenRecordset("Table1")

uvw.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
uvw.MoveLast
uvw.MovePrevious
Debug.Print "ADO, we start with: " & uvw.Fields("f1").Value
uvw.AddNew
uvw.Fields("f1") = "new field"
uvw.Update
Debug.Print "ADO, after the Update, we are at: " &
uvw.Fields("f1").Value

rst.MoveLast
rst.MovePrevious
Debug.Print "DAO, we start with: " & rst.Fields("f1").Value
rst.AddNew
rst.Fields("f1") = "DAO's one"
rst.Update
Debug.Print "DAO, after the Update, we are at: " &
rst.Fields("f1").Value
End Sub
=====================

and, in my case, I got:


-------------------Immediate Window
ADODAO
ADO, we start with: ab
ADO, after the Update, we are at: new field
DAO, we start with: a
DAO, after the Update, we are at: a
--------------------



Vanderghast, Access MVP




"JimS" <(E-Mail Removed)> wrote in message
news:95075260-1E26-4784-AC0C-(E-Mail Removed)...
> Using an ADO recordset, I issue an AddNew, then populate, then issue an
> Update. If the primary key is an autonumber field, how do I know what that
> new column value is? Will MoveLast do it? Or does the recordset pointer
> remain on the record I just updated?
> --
> Jim

I meant

With ADO, you can read the key after the ***UPDATE**** since the record
with the
focus will be the one you just added.

and not after the ***AddNew***


Vanderghast, Access MVP


"vanderghast" <vanderghast@com> wrote in message
news:8B07CBE0-3E75-4E04-8F08-(E-Mail Removed)...
> With DAO, you have the automatically generated key after the AddNew, but
> if you don't capture it, be aware that after the Update, the recordset
> will likely be focused at the record you were before the AddNew.
> With ADO, you can read the key after the AddNew since the record with the
> focus will be the one you just added.
>
>
>
> The following code illustrates the behavior about which record is the
> current record after you append a new record:
>
> ===================
> Public Sub ADODAO()
> Dim rst As DAO.Recordset
> Dim uvw As New ADODB.Recordset
> Set rst = CurrentDb.OpenRecordset("Table1")
>
> uvw.Open "Table1", CurrentProject.Connection, adOpenKeyset,
> adLockOptimistic, adCmdTable
> uvw.MoveLast
> uvw.MovePrevious
> Debug.Print "ADO, we start with: " & uvw.Fields("f1").Value
> uvw.AddNew
> uvw.Fields("f1") = "new field"
> uvw.Update
> Debug.Print "ADO, after the Update, we are at: " &
> uvw.Fields("f1").Value
>
> rst.MoveLast
> rst.MovePrevious
> Debug.Print "DAO, we start with: " & rst.Fields("f1").Value
> rst.AddNew
> rst.Fields("f1") = "DAO's one"
> rst.Update
> Debug.Print "DAO, after the Update, we are at: " &
> rst.Fields("f1").Value
> End Sub
> =====================
>
> and, in my case, I got:
>
>
> -------------------Immediate Window
> ADODAO
> ADO, we start with: ab
> ADO, after the Update, we are at: new field
> DAO, we start with: a
> DAO, after the Update, we are at: a
> --------------------
>
>
>
> Vanderghast, Access MVP
>
>
>
>
> "JimS" <(E-Mail Removed)> wrote in message
> news:95075260-1E26-4784-AC0C-(E-Mail Removed)...
>> Using an ADO recordset, I issue an AddNew, then populate, then issue an
>> Update. If the primary key is an autonumber field, how do I know what
>> that
>> new column value is? Will MoveLast do it? Or does the recordset pointer
>> remain on the record I just updated?
>> --
>> Jim
>

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:(E-Mail Removed):

> Haven't tested with ADO, but with DAO, you can simply refer to the
> Autonumber field to find out its value.
>
> rs.AddNew
> rs!Field1 = "abc"
> Msgbox "I just added a record with Autonumber " & rs!Id

Depends on your back end, of course. If it's Jet, yes, you'll have
the value immediately. If it's SQL Server, you won't.

But, of course, if it's SQL Server, it's not an Autonumber.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"vanderghast" <vanderghast@com> wrote in
news:2F2C4F42-3FED-41D7-BECA-(E-Mail Removed):

> With ADO, you can read the key after the ***UPDATE**** since the
> record
> with the
> focus will be the one you just added.
>
> and not after the ***AddNew***

And, of course, there's always:

SELECT @@IDENTITY

that could be used after the insert is done. This is paricularly
useful in cases where you choose to use a SQL INSERT instead of a
recordset to add data -- execute the INSERT and then immediately ask
for the identity value, and you'll be done. In fact, you can do
this:

lngNewID = db.OpenRecordset("SELECT @@IDENTITY")(0)

Of course, you have to be certain you use the same database variable
that you used for Executing the INSERT. That is, you can't use
CurrentDB for the execute followed by the statement above with
CurrentDB in place of the db variable.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/