Store AutoID after programmatically adding new record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sturose2000 - 13 Mar 2007 00:21 GMT

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

Reply to this Message

Sergey Poberezovskiy - 13 Mar 2007 03:01 GMT

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,
>
[quoted text clipped - 22 lines]
> Thanks in advance,
> Stu

Reply to this Message

sturose2000 - 13 Mar 2007 04:30 GMT

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

> If I remember correctly, starting with version 2000 you can select @@Identity
> from the table, something similar to the following:
[quoted text clipped - 32 lines]
> > Thanks in advance,
> > Stu

Reply to this Message

Sergey Poberezovskiy - 13 Mar 2007 07:47 GMT

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

> Thank you Sergey.
>
[quoted text clipped - 48 lines]
> > > Thanks in advance,
> > > Stu

Reply to this Message

sturose2000 - 13 Mar 2007 17:34 GMT

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?

> stu,
>
[quoted text clipped - 87 lines]
> > > > Thanks in advance,
> > > > Stu

Reply to this Message

onedaywhen - 15 Mar 2007 13:31 GMT

On Mar 13, 2:01 am, Sergey Poberezovskiy
<SergeyPoberezovs...@discussions.microsoft.com> wrote:

> If I remember correctly, starting with version 2000 you can select @@Identity
> from the table, something similar to the following:
>
> "SELECT @@Identity FROM " & strTbl

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.

--

Reply to this Message

sturose2000 - 15 Mar 2007 19:16 GMT

Jamie, that article was helpful. Thank you for pointing it out.
-Stu

> On Mar 13, 2:01 am, Sergey Poberezovskiy
> <SergeyPoberezovs...@discussions.microsoft.com> wrote:
[quoted text clipped - 8 lines]
> support.microsoft.com/default.aspx/kb/232144
> INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

Reply to this Message