by Mike Kendrick (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 2nd August 2002
Date Added: Mon 8th February 2021
Rating: (1 Votes)
Retrieve a record number when doing an INSERT statement
API Declarations
'This code assumes you have a database connection already created called DBConn
'And an auto-incrementing number field in your database
'I looked long and hard for a way to do this - this is the best i came up
'with - if you find a better way or have questions - shoot me an email.
'Your Insert Statement
SS$ = "INSERT INTO YOURTABLE (Field1, Field2) VALUES ('Value1','Value2')"
'Execute the INSERT and also retrieve the record number
Set rs = DBConn.Execute(SS$ & ";Select @@Identity as ident")
Set rs = rs.NextRecordset
'Set iRecNum = to the record number of the data you just inserted into the table
iRecNum = rs!Ident
rs.Close: Set rs = Nothing