Store AutoID after programmatically adding new record

S

sturose2000

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
 
S

Sergey Poberezovskiy

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
 
S

sturose2000

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

Sergey Poberezovskiy

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

sturose2000

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Naming of Form Button 3
Copy previous record into form field 0
Dev Ashish Relink Tables 9
Autonumber field in Excel that behaves the same as Access 0
wildcard char 2
5 minute time increments 2
Percentile Function 3
GRANT UPDATEIDENTITY? 3

Top