• 0

[Access 2003 VBA] Return AutoNumberID of SQL Insert Result?


Question

Is there a way to get the AutoNumber ID value of the record that was just inserted via a SQL INSERT query?

EDIT:

First, get what the next AutoNumber will be:

SELECT MAX(OrderID)
FROM tblOrders;

Then insert records referencing that AutoNumber ID.

Is there a better solution...?

Edited by magik

16 answers to this question

Recommended Posts

  • 0

I think you can use either

SELECT @@IDENTITY FROM tbl

or maybe... MAYBE

SELECT SCOPE_IDENTITY() FROM tbl

I'm not certain that you can do multiple queries in one statement in Access. It's been a long time, thankfully.

Edit:

skyfox beat me to it! :)

  • 0
  skyfox01_99 said:
If using Jet 4.0 use SELECT @@IDENTITY.

Is that possible within MS Access VBA? Could I trouble you for some more detailed information/example? Forgive me, it's been a while since I have used Access VBA...

Edited by magik
  • 0

Try something like this:

Dim rs as DAO.Recordset

CurrentDB.Execute ("INSERT INTO .....")
Set rs = CurrentDB.OpenRecordset("SELECT @@IDENTITY")

If Not rs.EOF Then
	MsgBox rs.Fields(0)
Else
	MsgBox "No AutoNumber generated."
End If

rs.Close()

  • 0

Related question: Would this work with multiple records?

ie: Is the following code valid?

Dim rs As DAO.Recordset

Call CurrentDb.Execute(strSQL)
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")

rs.MoveFirst
Do While Not rs.EOF
	MsgBox rs.Fields(0)
	rs.MoveNext
Loop

  • 0

The code is valid but it won't produce the result you want; SELECT @@IDENTITY returns the last AutoNumber that was generated. Therefore the loop and the "rs.MoveNext" are unnecessary. The "rs.MoveFirst" is not required as the Recordset will be open at the first record anyway (or EOF if no AutoNumber was generated).

  • 0
  skyfox01_99 said:
The code is valid but it won't produce the result you want; SELECT @@IDENTITY returns the last AutoNumber that was generated. Therefore the loop and the "rs.MoveNext" are unnecessary. The "rs.MoveFirst" is not required as the Recordset will be open at the first record anyway (or EOF if no AutoNumber was generated).

What would you suggest for that desired result, then? I basically need a recordset of the affected rows.

Is the

SELECT SCOPE_IDENTITY() FROM tbl

That azcodemonkey suggested a plausible solution?

  • 0
  magik said:
What would you suggest for that desired result, then? I basically need a recordset of the affected rows.

Is the

SELECT SCOPE_IDENTITY() FROM tbl

That azcodemonkey suggested a plausible solution?

I'm fairly certain that Access 2003 doesn't support the SCOPE_IDENTITY function, unfortunately. All the searches I did alluded to that.

  • 0

You can only insert one row at a time (unless you're doing an "INSERT INTO ... SELECT FROM ..." but that's a different matter) so there should only ever be one generated autonumber. Can you paste some of your existing code so that we can get an idea of what you want?

  • 0
  skyfox01_99 said:
You can only insert one row at a time (unless you're doing an "INSERT INTO ... SELECT FROM ..." but that's a different matter) so there should only ever be one generated autonumber. Can you paste some of your existing code so that we can get an idea of what you want?

Actually, I am doing an INSERT INTO ... SELECT FROM. Here is some sample code, hope it helps....

INSERT INTO tblWorkOrder(Artist, Title, Vendor)
SELECT w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=intSOID;

Which, by the way, doesn't seem to be a valid query since Access complains that it didn't add X number of records because of "key violations". No idea why that is. The table structure is like so:

tblWorkOrder

WorkOrderID (PK), SalesOrderID (FK), Artist, Title, Vendor... etc

The other table in question is, tblSalesOrder, which can be thought of as a "parent table"

tblSalesOrder

SalesOrderID (PK), Date, Customer, Address, ... etc

Basically what I am trying to do is create a "Reorder" function, which copies all the necessary fields and duplicates them. I have a similar update query for the tblSalesOrder table that works fine:

INSERT INTO tblSalesOrder
SELECT s.PONumber, s.Customer, s.BillAddress, s.Priority FROM tblSalesOrder s
WHERE s.SalesOrderID = curSOID

:wacko:

Thanks for any and all help...

  • 0

I'd guess that your key violation errors are due to attempts to insert duplicate values into a column with a unique index/key. There's no Access function to return all of the autonumbers generated by a mass-insert. You'll have to run a select after the insert to pull out the IDs of the new records.

Something like this:

  1. Duplicate the Sales Order,
  2. "SELECT @@IDENTITY" to get the new sales order ID,
  3. Duplicate the work order details,
  4. Run "SELECT WorkOrderID FROM tblWorkOrder WHERE SalesOrderId = <New_SOID>" to get the new WorkOrderIDs

  • 0
  skyfox01_99 said:
I'd guess that your key violation errors are due to attempts to insert duplicate values into a column with a unique index/key. There's no Access function to return all of the autonumbers generated by a mass-insert. You'll have to run a select after the insert to pull out the IDs of the new records.

Something like this:

  1. Duplicate the Sales Order,
  2. "SELECT @@IDENTITY" to get the new sales order ID,
  3. Duplicate the work order details,
  4. Run "SELECT WorkOrderID FROM tblWorkOrder WHERE SalesOrderId = <New_SOID>" to get the new WorkOrderIDs

That is exactly what I am doing. The problem is, a sales order can have multiple Work Orders related to it. There are no unique keys besides the WorkOrderID Primary Key in the tblWorkOrder table, so I am not sure why Access is giving that error. :dontgetit:

Also, since there is no way to determine all of the autonumbers generated by a mass-insert then I guess this function is impossible? Perhaps a solution with individual recordset iterations and a per-record update would be the only way to go about it? Man, I was hoping for a more efficient, clean solution. :(

  • 0

I figured out why Access was complaining about the "key violations" for the tblWorkOrder INSERT query. The following fixes the issue:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT w.SalesOrderID, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderID;

I need to be able to specify the value of SalesOrderID to be placed in that INSERT operation, namely it needs to hold the value of newSalesOrderID (that was created in the previous INSERT INTO tblSalesOrder and retrieved via SELECT @@IDENTITY) in the tblSalesOrder table.

The problem now is, how can I specify the newSalesOrderID to be inserted in the INSERT INTO tblWorkOrder statement?

  • 0

You can specify a static value in the SELECT clause:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT 1234, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderId;

You can do this by building up the SQL statement via string concatenation or by creating a QueryDef. I prefer the latter approach:

First create a query using the following SQL and save it:

INSERT INTO tblWorkOrder(SalesOrderID, Artist, Title, Vendor)
SELECT newSalesOrderId, w.Artist, w.Title, w.Vendor
FROM tblWorkOrder w WHERE w.SalesOrderID=oldSalesOrderId;


...and in VBA:

Dim qry as QueryDef
Set qry = CurrentDb.QueryDefs("procDuplicateWorkOrder")

qry.Parameters("newSalesOrderId").Value = newSalesOrderId
qry.Execute

Set qry = Nothing

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Microsoft 365 Word gets SharePoint eSignature, now you can ditch third-party signing tools by Paul Hill Microsoft has just announced that it will be rolling out an extremely convenient feature for Microsoft 365 customers who use Word throughout this year. The Redmond giant said that you’ll now be able to use SharePoint’s native eSignature service directly in Microsoft Word. The new feature allows customers to request electronic signatures without converting the documents to a PDF or leaving the Word interface, significantly speeding up workflows. Microsoft’s integration of eSignatures also allows you to create eSignature templates which will speed up document approvals, eliminate physical signing steps, and help with compliance and security in the Microsoft 365 environment. This change has the potential to significantly improve the quality-of-life for those in work finding themselves adding lots of signatures to documents as they will no longer have to export PDFs from Word and apply the signature outside of Word. It’s also key to point out that this feature is integrated natively and is not an extension. The move is quite clever from Microsoft, if businesses were using third-party tools to sign their documents, they would no longer need to use these as it’s easier to do it in Word. Not only does it reduce reliance on other tools, it also makes Microsoft’s products more competitive against other office suites such as Google Workspace. Streamlined, secure, and compliant The new eSignature feature is tightly integrated into Word. It lets you insert signature fields seamlessly into documents and request other people’s signatures, all while remaining in Word. The eSignature feature can be accessed in Word by going to the Insert ribbon. When you send a signature request to someone from Word, the recipient will get an automatically generated PDF copy of the Word document to sign. The signed PDF will then be kept in the same SharePoint location as the original Word file. To ensure end-to-end security and compliance, the document never leaves the Microsoft 365 trust boundary. For anyone with a repetitive signing process, this integration allows you to turn Word documents into eSignature templates so they can be reused. Another feature that Microsoft has built in is audit trail and notifications. Both the senders and signers will get email notifications throughout the entire signing process. Additionally, you can view the activity history (audit trail) in the signed PDF to check who signed it and when. Finally, Microsoft said that administrators will be able to control how the feature is used in Word throughout the organization. They can decide to enable it for specific users via an Office group policy or limit it to particular SharePoint sites. The company said that SharePoint eSignature also lets admins log activities in the Purview Audit log. A key security measure included by Microsoft, which was mentioned above, was the Microsoft 365 trust boundary. By keeping documents in this boundary, Microsoft ensures that all organizations can use this feature without worry. The inclusion of automatic PDF creation is all a huge benefit to users as it will cut out the step of manual PDF creation. While creating a PDF isn’t complicated, it can be time consuming. The eSignature feature looks like a win-win-win for organizations that rely on digital signatures. Not only does it speed things along and remain secure, but it’s also packed with features like tracking, making it really useful and comprehensive. When and how your organization gets it SharePoint eSignature has started rolling out to Word on the M365 Beta and Current Channels in the United States, Canada, the United Kingdom, Europe, and Australia-Pacific. This phase of the rollout is expected to be completed by early July. People in the rest of the world will also be gaining this time-saving feature but it will not reach everyone right away, though Microsoft promises to reach everybody by the end of the year. To use the feature, it will need to be enabled by administrators. If you’re an admin who needs to enable this, just go to the M365 Admin Center and enable SharePoint eSignature, ensuring the Word checkbox is selected. Once the service is enabled, apply the “Allow the use of SharePoint eSignature for Microsoft Word” policy. The policy can be enabled via Intune, Group Policy manager, or the Cloud Policy service for Microsoft 365 Assuming the admins have given permission to use the feature, users will be able to access SharePoint eSignatures on Word Desktop using the Microsoft 365 Current Channel or Beta Channel. The main caveats include that the rollout is phased, so you might not get it right away, and it requires IT admins to enable the feature - in which case, it may never get enabled at all. Overall, this feature stands to benefit users who sign documents a lot as it can save huge amounts of time cumulatively. It’s also good for Microsoft who increase organizations’ dependence on Word.
    • It's always good to have an option to secure your stuff to another medium. I did that with DVD/CD collection, and run my own media server now. It's more convenient that way and no need for separate players anymore.
    • Google Search AI Mode gets support for data visualization and custom charts by Aditya Tiwari Google announced it is rolling out support for data visualizations and graphs for finance-related queries in Google Search's AI Mode. Introduced last month at the Google I/O 2025 keynote, the feature lets you analyze complex datasets and create custom charts simply using natural language prompts. The updated AI Mode lets you compare and analyze information over a specific period, Google explained. It generates interactive graphs and provides a comprehensive explanation for your questions. AI Mode utilizes Gemini's multimodal capabilities and multi-step reasoning approach to comprehend the question's intent while accessing historical and real-time information relevant to the question. For instance, instead of manually researching individual companies and their stock prices, you can use AI Mode to compare the stock performance of different companies for a specific year. Once the graph is generated, you can choose the desired time period using the mouse cursor and ask follow-up questions based on the data presented. These new data visualizations for finance queries are available to users who have enabled the AI Mode experiment in Labs. AI Mode was introduced earlier this year as an experimental feature in the US. The feature is an upgraded version of AI Overviews, and Google closely worked with AI power users through the initial development process. It uses the “query fan-out” technique to perform multiple related searches across subtopics and different data sources, then combines them to come up with a comprehensive response. Google updated AI Mode last month to use a custom version of the latest Gemini 2.5 model. It added several new features, including Deep Search, live capabilities, agentic capabilities of Project Mariner, a new shopping experience, and the ability to add personal context by linking Google apps. The search giant is planning to turn AI Mode into its bread and butter. It has begun testing ads for the feature, which will appear below and be integrated into AI Mode responses where relevant.
    • Guys, you should find another way to promote your deals... It's the third article in the last months that promote this deal for an upgrade from 10. Considering that upgrade from 10 to 11 is free it's a total non-sense.
    • Store should be a shrine of useful applications, vetted and verified. Easily sorted by publisher. Windows should start with not much installed and have things as options in the store. Not the wild west mess that it is. You could delete 95%+ of the crap on there and no one would notice. They need to add a better UI to the updates, it's awful right now.
  • Recent Achievements

    • Week One Done
      luxoxfurniture earned a badge
      Week One Done
    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      432
    2. 2
      +FloatingFatMan
      239
    3. 3
      snowy owl
      213
    4. 4
      ATLien_0
      211
    5. 5
      Xenon
      157
  • Tell a friend

    Love Neowin? Tell a friend!