how to get the last Increased number in access after inserting new record to database

[E_Gold]

hi

i have table MEN in access that contain unique key generation (auto increased field)

when i insert record to database, this field increase.

how to read this value ?

is there way to read this value when i insert the record ?

thanks in advance

 

[MarkK]

In Access you can run a special query having this SQL ...

SELECT @@Identity

... to return the last primary key value created on a particular connection. So if you have a table ...

tTable
TableID
Field1

... and you do an insert ....

with currentdb
  .execute "INSERT INTO tTable ( Field1 ) VALUES ( 'Foo' )"

... then you can safely retrieve the value of TableID for that inserted record using ...

with currentdb
  .execute "INSERT INTO tTable ( Field1 ) VALUES ( 'Foo' )"
  debug.print .openrecordset("SELECT @@Identity").Fields(0)
end with

... but note that within the With block you are using the same reference to CurrentDB. You could also declare a variable ...

  dim dbs as dao.database
  set dbs = currentdb
  dbs.execute "INSERT INTO tTable ( Field1 ) VALUES ( 'Foo' )"
  debug.print dbs.openrecordset("SELECT @@Identity").Fields(0)

But this risks the possibility that someone else will insert another record before you read @@Identity....

  currentdb.execute "INSERT INTO tTable ( Field1 ) VALUES ( 'Foo' )"
  debug.print currentdb.openrecordset("SELECT @@Identity").Fields(0)

... because your second reference to currentdb is a new instance of the connection to the database.
Cheers,
Mark