Autonumber value of last inserted row – MS Access / VBA

by

In your example, because you use CurrentDB to execute your INSERT you’ve made it harder for yourself. Instead, this will work:

  Dim query As String
  Dim newRow As Long  ' note change of data type
  Dim db As DAO.Database

  query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
  Set db = CurrentDB
  db.Execute(query)
  newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
  Set db = Nothing

I used to do INSERTs by opening an AddOnly recordset and picking up the ID from there, but this here is a lot more efficient. And note that it doesn’t require ADO.

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Code to loop through all records in MS Access
  4. Bang Notation and Dot Notation in VBA and MS-Access
  5. How do I force VBA/Access to require variables to be defined?
  6. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  7. How to diagnose MS access crashes
  8. Equivalent cURL in VBA?
  9. How do I access the selected rows in Access?
  10. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  11. Prevent enabling of shift bypass – Access 2010
  12. VBA procedure to import csv file into access
  13. Transform multiple rows into columns with unique key
  14. how to show query results in a datasheet in the same form?
  15. Exporting MS Access Forms and Class / Modules Recursively to text files?
  16. Access VBA: Find max number in column and add 1
  17. MS Access prepared statements
  18. Create sequential ID value based on the year that a record is added
  19. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  20. Null values for variables in VBA
  21. Using a centralized VBA module in multiple Access databases
  22. How to execute a query in ms-access in VBA code?
  23. How to add a menu item to the default right click context menu
  24. VBA – Run Time Error 3271 using DAO object
  25. Trouble with InputBoxes
  26. Get Name of Current VBA Function
  27. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  28. Access query producing results like ROW_NUMBER() in T-SQL
  29. Calling stored procedure using VBA
  30. Custom row source for combo box in continuous form in Access

Leave a Comment