Return AutoNumber Value

[Brian]

I have a application with an Access front-end and a SQL Server 2005 back-end.
All of my records include what Access called an AutoNumber (Identity in SQL)
which is an auto-incrementing integer value. When I create a new record
through my forms, through a VBA function using rst.addnew and rst.update, is
there any way for the VBA function to return the newly assigned autonumber
value? In my database, there are many fields which can have duplicate
values, so the autonumber field is the only one that is definitely unique.

Thanks

 

[ruralguy via AccessMonster.com]

From MVP Ken Snell
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

'--- Another approach

From MVP Allen Browne
Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function