Retrieving the [autonumber]

R

Rli

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?
 
K

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
 
R

Rli

okay, thanks


Ken Snell said:
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>



Rli said:
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?
Click to expand...
Click to expand...
 
R

RuralGuy

Correct me if I'm wrong Ken but I believe the AutoNumber field is available as
soon as you "fill" your first field:

rst.AddNew
rst.Fields("FieldName1").Value = "YourNewValue1"
rst.Fields("FieldName2").Value = "YourNewValue2"

'-- Get the AutoNumber right here!
lngAuto = rst.Fields("AutonumberFieldName").Value

' (etc. -- do not include the autonumber field as one that
' is given a value)
rst.Update
'-- Next two lines no longer needed!
'-- rst.Bookmark = rst.LastModified
'-- lngAuto = rst.Fields("AutonumberFieldName").Value

At least I use it that way and it seems to be reliable.

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
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
K

Ken Snell [MVP]

Yes, it is... but I don't like to "grab" it at that point in case the append
fails... if the append fails, the number will not be one that is the
recordset. Granted, with proper error handling, etc., one might work around
this, but by grabbing it after the append is successful, that issue never
arises.
 
R

Rli

thanks again, Ken, this was very usefull

Ken Snell said:
Yes, it is... but I don't like to "grab" it at that point in case the append
fails... if the append fails, the number will not be one that is the
recordset. Granted, with proper error handling, etc., one might work around
this, but by grabbing it after the append is successful, that issue never
arises.
Click to expand...
 
R

RuralGuy

Excellent point Ken (as I expected). Thanks for responding. Once again I am
wiser than I was before I asked the question.

Yes, it is... but I don't like to "grab" it at that point in case the append
fails... if the append fails, the number will not be one that is the
recordset. Granted, with proper error handling, etc., one might work around
this, but by grabbing it after the append is successful, that issue never
arises.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
A

Allen Browne

Most times I find the approach Ken suggested to be the best one, but if you
did want to get the identity after inserting a record with an Append query,
you could use this approach in JET 4 (Access 2000 and later):

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
 
J

JonOfAllTrades

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.
 
A

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.
 
Top