Data Insert...getting back the "autonumber" value.

[Chatbox]

When I insert a record that consist of a field that is "autonumber"...how do I get back the value of that into my VBA code?

 

[pbaldy]

How are you inserting the record?

 

[mseifman]

Use mean to read the autonumber field

for use somewhere else?

 

[Chatbox]

Inserted via bounded form in with "allow addition" & "Data entry" set to yes.

If it was an SQL server, I'd have used "Select @@Identity"...I need something similar but for Access's own Jet database.

 

[pbaldy]

In the after insert event for a new record, the autonumber would be available with simply:

Me.AutoNumberField

 

[Chatbox]

Sweet!!! Thank you!

 

[Chatbox]

Next question, but related: What if I'm using an unbound form and that new records are add to the table via ADODB.connection and ADODB.command?

How do I get the autonumber (ID/identifier) of the last (newly) inserted record of my current session?

 

[pbaldy]

I rarely use ADO, but this works in DAO:

  rs.AddNew
  rs!SomeField = WhatEver
  MsgBox rs!AutoNumberField 'the autonumber is available at this point
  rs.Update

You might try @@Identity, as later versions of Jet support it.

 

[Chatbox]

Thanks, I just tried the @@Identity in Access 2003, works well.
Not sure if it will work with Access 2000, or do I only need to update the JET on the client computers (those with Access 2000) to get this @@Identity feature working for them?

Thanks for the tips with the rs, I'll keep that in mind as an alternative solution. You've been great!

 

[pbaldy]

Just tested and it works fine with A2k.

 

[Chatbox]

Thanks man, you're the best!

 

[pbaldy]

There are a large number of people here that would have a legitimate argument with that last statement, but thanks anyway!