Retrieving the [autonumber]

[Guest]

Can anyone tell me how to retrieve the [ autonumber] when executing a
Docmd.runsql statement on a table that has such a field.
When i do a INSERT INTO statement the autonumber is automatically generated.
But i want to use that number also in one of the other fields of the table...
can anyone help?

 

[Ken Snell [MVP]]

You'll need to use a different approach to the append query's execution if
you want to retrieve the autonumber value by code -- assuming that there is
no other "unique" value in the newly added record that you can use to "find"
the newly added record.

Use a recordset to enter the new record, and then you can "read" the
autonumber value directly:

Dim dbs As DAO.Database
Dim lngAuto As Long
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NameOfTable", dbOpenDynaset)
rst.AddNew
rst.Fields("FieldName1").Value = "YourNewValue1"
rst.Fields("FieldName2").Value = "YourNewValue2"
' (etc. -- do not include the autonumber field as one that
' is given a value)
rst.Update
rst.Bookmark = rst.LastModified
lngAuto = rst.Fields("AutonumberFieldName").Value

 

[Guest]

okay, thanks

You'll need to use a different approach to the append query's execution if
you want to retrieve the autonumber value by code -- assuming that there is
no other "unique" value in the newly added record that you can use to "find"
the newly added record.

Use a recordset to enter the new record, and then you can "read" the
autonumber value directly:

Dim dbs As DAO.Database
Dim lngAuto As Long
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NameOfTable", dbOpenDynaset)
rst.AddNew
rst.Fields("FieldName1").Value = "YourNewValue1"
rst.Fields("FieldName2").Value = "YourNewValue2"
' (etc. -- do not include the autonumber field as one that
' is given a value)
rst.Update
rst.Bookmark = rst.LastModified
lngAuto = rst.Fields("AutonumberFieldName").Value

--

Ken Snell
<MS ACCESS MVP>

Can anyone tell me how to retrieve the [ autonumber] when executing a
Docmd.runsql statement on a table that has such a field.
When i do a INSERT INTO statement the autonumber is automatically
generated.
But i want to use that number also in one of the other fields of the
table...
can anyone help?

 

[Guest]

Can you tell us more about this @@Identity operator? I can't find any
information on it in Acc2K VB docs, Google, or this forum.

 

[Allen Browne]

@@Identity is something SQL Server uses, so MS included it in JET 4 (Access
2000), but--as you say--they didn't really go to town with providing
documentation.

It is not as flexible or powerful as the SQL Server version.

As we said, Ken's suggestion of using DAO to get the value of the
LastModified bookmark is my preferred approach too.