Return auto-gen ID field after creating new record

Article
2015-06-22

Question

_{Monday, June 22, 2015 8:47 PM}

I have developed a front end to my SQL Server in MS Access. I am have created a form in which the user fills out orders. The Orders table in SQL Server has an auto-generated primary key field. After the order is filled out and inserted into the Orders table, I would like to be able to return the value of the auto-generated primary key for that order so that I can update other various tables that use this ID as a foreign key.

For instance, after the order is updated. I need to then place the ID of that order as an FK in the table I am using to keep track of each product in that order such as quantity, durations, etc.

I am doing this all in MS Access 2010, so I need to know how to do this in VBA.
Thanks for your help!

All replies (14)

_{Tuesday, June 23, 2015 4:28 PM ✅Answered}

Sorry for the plain text paste. Having browser issues. Anyways..
I am getting this compile error: Invalid use of Null at line "new_id = rst!OrderID"
rst!OrderID is returning null
In case it is important, CustomerID is also a required field(Not Null) to add records to [Orders] table.

Private Sub btnSubmitOrder_Click()
Dim db As Database
Dim rst As Recordset
Dim new_id As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM ORDERS", dbOpenDynaset, dbSeeChanges)
rst.AddNew
new_id = rst!OrderID
rst!CustomerID = 123456789
rst.Update
Debug.Print new_id

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

BTW Tried this with and without rst.Edit before rst.AddNew

IIRC, with a recordset open on a linked SQL Server table, after adding a record you have to first reposition the recordset at its .LastModified bookmark, and only then read the autonumber:

With rst
    .AddNew
    !CustomerID = 123456789
    .Update
    .Bookmark = .LastModified
    new_id = !OrderID
End With

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Monday, June 22, 2015 9:28 PM}

For instance, after the order is updated. I need to then place the ID of that order as an FK in the table I am using to keep track of each product in that order such as quantity, durations, etc.

Hi HTHP,

You could use this one-liner:

  last_id = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS LastID")!LastID

Imb.

_{Monday, June 22, 2015 9:49 PM}

How well will this function in the case of 2 or more users entering orders at the same time? Do you think if I run this code directly after the INSERT command for the Orders table that it will be so fast that I will not have to worrie about it? Or is there a better way to guarantee that I have the ID just generated and not the LastID created in case a split second prior, someone else executed the same method and another record was added before the query to return the LastID?

Is it possible to write a stored procedure on the SQL Server end that returns the exact value just generated ? What is the t-sql to create that procedure? BUT more importantly, if I am on the right track, how would I implement the use of this procedure using VBA?

Or is there a different way of insuring that it is the correct ID. Are my concerns even valid??

Thanks again!

_{Monday, June 22, 2015 11:11 PM}

Hi HTHP,

Are you simply using a bound form in Access to add the new record in SQL Server, or are you using code?

_{Monday, June 22, 2015 11:30 PM}

All VBA code. The form is unbound. It doesn't create a new record until all required controls are filled out and a submit button is pressed. Then it inserts a record in the Orders table. After that, I need to insert a record in a ProductsTracking table with other information but I need that ID from the Orders table to put in my INSERT command in the ProductsTracking table to link back to the original order because Order ID is part of a Unique Identifier for the ProductsTracking table. It takes the ProductID and the OrderID to be unique for each record. Then other fields track quantity and other data. And it is the FK to the Orders table anyways, so it is required for that reason too.

_{Monday, June 22, 2015 11:46 PM}

Hi,

Thanks for the clarification. In that case, the @@Identity property should work for you as long as you use the same connection as when you inserted the record.

_{Tuesday, June 23, 2015 7:45 AM}

All VBA code. The form is unbound. It doesn't create a new record until all required controls are filled out and a submit button is pressed. Then it inserts a record in the Orders table. After that, I need to insert a record in a ProductsTracking table with other information but I need that ID from the Orders table to put in my INSERT command in the ProductsTracking table to link back to the original order because Order ID is part of a Unique Identifier for the ProductsTracking table. It takes the ProductID and the OrderID to be unique for each record. Then other fields track quantity and other data. And it is the FK to the Orders table anyways, so it is required for that reason too.

Hi HTHP,

Another way is to grasp the new_id when adding a new record using the AddNew and Update methods:

    recordset.Addnew
    new_id = recordset!OrderID
    recordset!Otherfield = ...
    ...
    recordset.Update

Now you can use new_id as FK in related records.

Imb.

_{Tuesday, June 23, 2015 4:07 PM}

Sorry for the plain text paste. Having browser issues. Anyways..
I am getting this compile error: Invalid use of Null at line "new_id = rst!OrderID"
rst!OrderID is returning null
In case it is important, CustomerID is also a required field(Not Null) to add records to [Orders] table.

Private Sub btnSubmitOrder_Click()
Dim db As Database
Dim rst As Recordset
Dim new_id As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM ORDERS", dbOpenDynaset, dbSeeChanges)
rst.AddNew
new_id = rst!OrderID
rst!CustomerID = 123456789
rst.Update
Debug.Print new_id

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

BTW Tried this with and without rst.Edit before rst.AddNew

_{Tuesday, June 23, 2015 4:31 PM}

Sorry for the plain text paste. Having browser issues. Anyways..
I am getting this compile error: Invalid use of Null at line "new_id = rst!OrderID"
rst!OrderID is returning null
In case it is important, CustomerID is also a required field(Not Null) to add records to [Orders] table.

Private Sub btnSubmitOrder_Click()
Dim db As Database
Dim rst As Recordset
Dim new_id As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM ORDERS", dbOpenDynaset, dbSeeChanges)
rst.AddNew
new_id = rst!OrderID
rst!CustomerID = 123456789
rst.Update
Debug.Print new_id

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

BTW Tried this with and without rst.Edit before rst.AddNew

Hi HTHP,

You have declared new_id as String, but you can better declare it as Long.

I assume that OrderID is the PrimaryKey of the ORDERS table. If it is an autonumber field, I do not expect it to have a Null value. If OrderID is not an autonumber field, you have to give it a value using your own "numbering" value first. What field do you use for referencing a FK?

Imb.

_{Tuesday, June 23, 2015 6:04 PM}

Answering 2 replies:

Imb-hb,
OrderID is PrimaryKey. OrderID was AutoNumber in Access, during migration, I let SSMA create what was needed during the migration so it still auto increments. FYI - The table auto-gens OrderID fine when using INSERT statements, creating new records on a bound form. No problems with SQL generating the OrderID. I have a CustomerID field in the Orders Table I use for an FK.
Dirk Goldgar,
I'm trying to use .AddNew but now when code throws error at line .Update "ODBC--call failed."

I have no problems opening a recordset with this same code below and reading from the table if I remove the .Update and other code and just type a few "debug.print !OrderDate" type lines.

Private Sub btnSubmitOrder_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim new_id As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Orders", dbOpenDynaset, dbSeeChanges)
With rst
.AddNew
!CustomerID = 123456789
.Update
.Bookmark = .LastModified
new_id = !OrderID
Debug.Print new_id
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

_{Tuesday, June 23, 2015 11:45 PM}

I created a new table: TopHundred with field [TopHundredID] as PK using SQL Server's newid() method to generate the id. No FKs and 3 other fields which allow nulls. I get the same error. Why can't I do I get that ODBC runtime error '3146'. Here is new code but similar just as a test I wanted to make new code and table. Same error.

Dim db As Database
Dim rst As DAO.Recordset
Dim new_id As Long
Dim last_id As Long
Set db = CurrentDb
Set rst = db.OpenRecordset("TopHundred", dbOpenDynaset, dbSeeChanges)
With rst
.AddNew
.Update
End With
rst.Close
Set rst = Nothing
Set db = Nothing

_{Wednesday, June 24, 2015 1:11 AM}

Unfortunately, the error 3146 -- "ODBC -- call failed" -- isn't very informative. But you can put in some error-handling code to examine the database engine's Errors collection to see what the error from SQL Server was. For example, you might amend your procedure as follows:

Private Sub btnSubmitOrder_Click()

    On Error GoTo Err_Handler

    Dim db As Database
    Dim rst As DAO.Recordset
    Dim new_id As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM Orders", dbOpenDynaset, dbSeeChanges)

    With rst
        .AddNew
        !CustomerID = 123456789
        .Update
        .Bookmark = .LastModified
        new_id = !OrderID
        Debug.Print new_id
        .Close
    End With

Exit_Point:
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

Err_Handler:
    Debug.Print "Error " & Err.Number; ": "; Err.Description
    If Err.Number = 3146 Then
        Dim DAOErr As DAO.Error
        Debug.Print " DBEngine Errors "
        For Each DAOErr In DBEngine.Errors
            Debug.Print DAOErr.Number, DAOErr.Description, DAOErr.Source
        Next DAOErr
    End If
    Resume Exit_Point

 End Sub

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

_{Wednesday, June 24, 2015 1:50 AM}

With Dirk's code, hopefully you could find the error.

Here's Access help.

Syntax

For Connection and Database objects:

Set recordset = object**.OpenRecordset (source,** type**,** options, lockedits**)**

For QueryDef, Recordset, and TableDef objects:

Set recordset = object**.OpenRecordset** (type, options, lockedits)

For a connection base syntax, you need 4 args instead of 3. Maybe like this...

Set rst = db.openrecordset("MySQL", dbOpenDynaset, dbSeeChanges, dbOptimistic).

HTH.

_{Wednesday, June 24, 2015 1:47 PM}

Sorry, I realized how useless that was after I retired for the night.
I found the error in the Errors collection. For my original question I was trying to ad a FK which violated relationship.

However something very interesting I learned from this:
If I try to use .AddNew to create a new record with NO OTHER field values filled out, expecting the SQL Server to just create a record with the auto-generated primary key and leave all other fields blank, it will not do it! You MUST put data into at least one other field.

Thank you everyone! Question answered.

Share via

Return auto-gen ID field after creating new record

Question

All replies (14)

Additional resources