Store AutoID after programmatically adding new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I'd like to be able to append a new record into an existing table, where the
data for the record comes from earlier in the VBA code; then I need to find
the Primary Key field for the record I just added.

I have code that adds a new record to a table:

DoCmd.RunSQL "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"
lngIntID = DLookup("[InterventionAutoID]", strTbl, "[InterventionCode] =
'" & strLabel & "'")

Here, strTbl and strLabel are arguments of the function in which this code
is found. The field strTbl.InterventionAutoID is an AutoNumber type with
random value, not sequential.

The second line of code has been fine for cases where strLabel is unique,
that is, does not duplicate any existing strTbl.InterventionCode. However, I
now have cases where InterventionCode is not unique so I cannot count on
DLookup to return the "true new" InterventionAutoID.

Any help grabbing the "correct" number for lngIntID?

Thanks in advance,
Stu
 
If I remember correctly, starting with version 2000 you can select @@Identity
from the table, something similar to the following:

"SELECT @@Identity FROM " & strTbl

Open an ADO recordset on the sql above, and you should get the last inserted
AutoNumber field value for the table.

HTH
 
Thank you Sergey.

I have never worked with Recordset before. I tried:
DoCmd.RunSQL "SELECT @@Identity FROM " & strTbl
This gave an Error "A RunSQL action requires an argument consisting of an
SQL statement."

Also tried to run this SQL as a saved Query while the script was paused
(Debug.Assert False) but the query result was a list with 10 records (same
number as in the strTbl) all of zero value.

Could you please elaborate on how to acccess the recordset and get the new
AutoID?

-Stu


Sergey Poberezovskiy said:
If I remember correctly, starting with version 2000 you can select @@Identity
from the table, something similar to the following:

"SELECT @@Identity FROM " & strTbl

Open an ADO recordset on the sql above, and you should get the last inserted
AutoNumber field value for the table.

HTH

sturose2000 said:
Hi all,

I'd like to be able to append a new record into an existing table, where the
data for the record comes from earlier in the VBA code; then I need to find
the Primary Key field for the record I just added.

I have code that adds a new record to a table:

DoCmd.RunSQL "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"
lngIntID = DLookup("[InterventionAutoID]", strTbl, "[InterventionCode] =
'" & strLabel & "'")

Here, strTbl and strLabel are arguments of the function in which this code
is found. The field strTbl.InterventionAutoID is an AutoNumber type with
random value, not sequential.

The second line of code has been fine for cases where strLabel is unique,
that is, does not duplicate any existing strTbl.InterventionCode. However, I
now have cases where InterventionCode is not unique so I cannot count on
DLookup to return the "true new" InterventionAutoID.

Any help grabbing the "correct" number for lngIntID?

Thanks in advance,
Stu
Click to expand...
Click to expand...
 
stu,

I used the following code to get AutoNumber from Table1 I created for testing:

sql = "select @@Identity from Table1"
With CurrentDb.OpenRecordset(sql, dbOpenForwardOnly)
Debug.Print .Fields(0)
End With

CurrentDb returns reference to the database in which the code is run and
then value of AutoNumber field is printed to the immediate (debug) window.

I would suggest you rewrite your code similar to the following:

On Error GoTo insert_Err
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngIdentity As Long

Set db = CurrentDb
strSql = "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"

db.Execute strSql, dbFailOnError

strSql = "select @@Identity from " & strTbl
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenForwardOnly)
If Not rs.EOF Then
lngIdentity = rs(0)
End If
insert_Exit:
Exit Sub
insert_Err:
MsgBox Err.Description
Resume insert_Exit

HTH
sturose2000 said:
Thank you Sergey.

I have never worked with Recordset before. I tried:
DoCmd.RunSQL "SELECT @@Identity FROM " & strTbl
This gave an Error "A RunSQL action requires an argument consisting of an
SQL statement."

Also tried to run this SQL as a saved Query while the script was paused
(Debug.Assert False) but the query result was a list with 10 records (same
number as in the strTbl) all of zero value.

Could you please elaborate on how to acccess the recordset and get the new
AutoID?

-Stu


Sergey Poberezovskiy said:
If I remember correctly, starting with version 2000 you can select @@Identity
from the table, something similar to the following:

"SELECT @@Identity FROM " & strTbl

Open an ADO recordset on the sql above, and you should get the last inserted
AutoNumber field value for the table.

HTH

sturose2000 said:
Hi all,

I'd like to be able to append a new record into an existing table, where the
data for the record comes from earlier in the VBA code; then I need to find
the Primary Key field for the record I just added.

I have code that adds a new record to a table:

DoCmd.RunSQL "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"
lngIntID = DLookup("[InterventionAutoID]", strTbl, "[InterventionCode] =
'" & strLabel & "'")

Here, strTbl and strLabel are arguments of the function in which this code
is found. The field strTbl.InterventionAutoID is an AutoNumber type with
random value, not sequential.

The second line of code has been fine for cases where strLabel is unique,
that is, does not duplicate any existing strTbl.InterventionCode. However, I
now have cases where InterventionCode is not unique so I cannot count on
DLookup to return the "true new" InterventionAutoID.

Any help grabbing the "correct" number for lngIntID?

Thanks in advance,
Stu
Click to expand...
Click to expand...
Click to expand...
 
Perfect!

As this is my first venture into Recordsets, it took some research to
understand exactly what is going on. Your sample code works exactly how I
want. Thank you for the help, Sergey.

On aside, I searched the help files for anything about "@@Identity" which
seems almost like "magic" to me, without finding anything about what it is or
why it works. Are there any references on the web that would explain that in
better detail?


Sergey Poberezovskiy said:
stu,

I used the following code to get AutoNumber from Table1 I created for testing:

sql = "select @@Identity from Table1"
With CurrentDb.OpenRecordset(sql, dbOpenForwardOnly)
Debug.Print .Fields(0)
End With

CurrentDb returns reference to the database in which the code is run and
then value of AutoNumber field is printed to the immediate (debug) window.

I would suggest you rewrite your code similar to the following:

On Error GoTo insert_Err
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngIdentity As Long

Set db = CurrentDb
strSql = "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"

db.Execute strSql, dbFailOnError

strSql = "select @@Identity from " & strTbl
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenForwardOnly)
If Not rs.EOF Then
lngIdentity = rs(0)
End If
insert_Exit:
Exit Sub
insert_Err:
MsgBox Err.Description
Resume insert_Exit

HTH
sturose2000 said:
Thank you Sergey.

I have never worked with Recordset before. I tried:
DoCmd.RunSQL "SELECT @@Identity FROM " & strTbl
This gave an Error "A RunSQL action requires an argument consisting of an
SQL statement."

Also tried to run this SQL as a saved Query while the script was paused
(Debug.Assert False) but the query result was a list with 10 records (same
number as in the strTbl) all of zero value.

Could you please elaborate on how to acccess the recordset and get the new
AutoID?

-Stu


Sergey Poberezovskiy said:
If I remember correctly, starting with version 2000 you can select @@Identity
from the table, something similar to the following:

"SELECT @@Identity FROM " & strTbl

Open an ADO recordset on the sql above, and you should get the last inserted
AutoNumber field value for the table.

HTH

:

Hi all,

I'd like to be able to append a new record into an existing table, where the
data for the record comes from earlier in the VBA code; then I need to find
the Primary Key field for the record I just added.

I have code that adds a new record to a table:

DoCmd.RunSQL "INSERT INTO " & strTbl & " (InterventionCode) VALUES ('" &
strLabel & "');"
lngIntID = DLookup("[InterventionAutoID]", strTbl, "[InterventionCode] =
'" & strLabel & "'")

Here, strTbl and strLabel are arguments of the function in which this code
is found. The field strTbl.InterventionAutoID is an AutoNumber type with
random value, not sequential.

The second line of code has been fine for cases where strLabel is unique,
that is, does not duplicate any existing strTbl.InterventionCode. However, I
now have cases where InterventionCode is not unique so I cannot count on
DLookup to return the "true new" InterventionAutoID.

Any help grabbing the "correct" number for lngIntID?

Thanks in advance,
Stu
Click to expand...
Click to expand...
Click to expand...
 
If I remember correctly, starting with version 2000 you can select @@Identity
from the table, something similar to the following:

"SELECT @@Identity FROM " & strTbl
Click to expand...

Actually, SELECT @@IDENTITY returns the last autoincrement value for
the connection i.e. across all tables. See:

support.microsoft.com/default.aspx/kb/232144
INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

Therefore, SELECT @@IDENTITY FROM Table1 does not necessarily return
the last autoincrement value for Table1; rather, it does a cross join
between @@IDENTITY and Table1 e.g. (ANSI-92 Query Mode Access/Jet
syntax):

CREATE TABLE Table1 (
data_col INTEGER NOT NULL
)
;
INSERT INTO Table1 (data_col) VALUES (55)
;
INSERT INTO Table1 (data_col) VALUES (99)
;
CREATE TABLE Table2 (
ID INTEGER IDENTITY(1, 1) NOT NULL UNIQUE,
data_col INTEGER NOT NULL
)
;
INSERT INTO Table2 (data_col) VALUES (1)
;
SELECT @@IDENTITY FROM Table1
;

The above query returns the value 1, being the last autoincemented
value for the connection (on Table2), for every row in Table1 --
Table1 doesn't even have the IDENTITY property!

FWIW it doesn't necessarily mean the value was auto-generated e.g.

INSERT INTO Table2 (ID, data_col)
VALUES (CLNG('&H80000000'), 2)
;
SELECT @@IDENTITY FROM Table1
;

returns the IDENTITY value I supplied i.e. was not auto-generated.

Jamie.

--
 
Back
Top