4

I have a button click event that takes information from controls and enters it into a table via INSERT INTO SQL statement in VBA.

I was wondering if there is anything I could add to this, or some other method to acquire the record number that is created for the record?

Could I just turn around and SELECT against the table and use rs.last?

John Koerner
  • 37,428
  • 8
  • 84
  • 134
Justin
  • 4,461
  • 22
  • 87
  • 152

1 Answers1

2

You can "SELECT @@IDENTITY" from the same DAO.Database object variable where you previously executed your INSERT statement. See here on Stack OverFlow for details.

Community
  • 1
  • 1
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • so HanUps, how would I then take the value I have retrieved and assign it back to a control? lets say this exists after my query ( "SELECT @@ IDENTITY"), and I want to show that value in a text box on my form ("textReturn").....what variable, or container does it exist in right now? newRow? – Justin Mar 26 '10 at 08:40
  • @HansUp...nevermind, I see that it is simple after that. Thanks very much for your help....this is exactly what I needed! – Justin Mar 26 '10 at 10:12
  • For code that does this see http://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba#1633346 – David-W-Fenton Mar 26 '10 at 19:08
  • @David...yeah that was exactly the exact same answer I was refering to. I was able to use it, and it worked great. Exactly what I needed. Thanks! – Justin Mar 27 '10 at 02:19