How to get last autoincrement value

  • Thread starter Thread starter Niels
  • Start date Start date
N

Niels

Hi folks,
my problem was asked for a million times.
But during the last hour i dont't found a
solution (at home and internet).

I have a ID field using the autoincrement function.
After inserting a new record i need to know the
new number. I call a event-handler on "AfterInsert" at
a contionus form, but i always get the ID from the new
*current* record not from the inserted record.

How can I get this ???

Thanks for any hint.
Niels
 
One suggestion would be to add a date/time stamp column to the table.
Before you insert the record, put the value of NOW() in a variable.
Insert the record, putting in the value of the variable.
Do a lookup of the record based on the variable that contained the date/time
stamp.


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
If you're doing it on a form, use the BeforeInsert
function and check the control that is bound to the ID
field. You can even have that field hidden.


Chris
 
I have a ID field using the autoincrement function.
After inserting a new record i need to know the
new number.
Click to expand...

To be honest, I handle this by not using the automatic Form functions, but
go for a DAO approach instead. This is a short version with no error
trapping etc. but it should give you the idea. I use this approach to
create a new record and point the form filter at it, perhaps in the
Form_Load event so it can be cancelled if there's an error. Hope it helps,
but bear in mind this is typed in from memory without testing!

Private Function NewIDNumber() As Long

dim db as database
dim rs as recordset
dim strSQL as string

' create a empty recordset: no need to load
' the network
strSQL = "SELECT ANField, RequiredField " & _
"FROM MyTable " & _
"WHERE FALSE;"

' open a dynaset to write to
set db = currentdb()
set rs = db.openrecordset( strSQL, dbOpenDynaset )

' add the new record, and make sure any required
' fields have something in them
rs.AddNew
rs!RequiredField = "Default Value"

' extract the number before updating the record,
' because that otherwise the recordset may move on
NewIDNumber = rs!ANField

' got it, now save the record
rs.Update

' and tidy up
rs.Close

End Function
 
Thanks to Rob, Chris and Tim.

I will try the "form way" suggested from Chris. This looks
easy and i don't need a second primary key at the table.

Thanks to you and have an nice evening.
Niels
 
Back
Top