Finding a number after an insert

[CD Tom]

I'm inserting a record into a different table from a form and need to get the autonumber that it created. I've tried doing a select from the table but for some reason the table hasn't refreshed yet and I can't get the record number. Does anyone have any suggestions as to a good way to go about getting that number
Any help would be appreciated.
Thank
Tom

 

[Tim Ferguson]

Does anyone have any suggestions as to a good way to go about getting
that number.

The @@IDENTITY function works if you are using ADO, but only if you know
that nobody has inserted another record into _any_ table in the meantime.

The safest answer is to use a bit of DAO to collect the number in one go:


Public Sub NewRecordIDNumber() As Long
' there is no error trapping in this.
' you might also want to pass required values as
' parameters to the function too.

Dim db As Database
Dim rs as Recordset
Dim strSQL as String
Dim dwNumber as Long

' don't want any records, and only bare minimum of fields
strSQL = "SELECT ANField, RequiredField " & vbNewLine & _
"FROM MyTable " & vbNewLine & _
"WHERE False"

' get an empty recordset
Set db = CurrentDB()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

' add a new record
rs.AddNew
' don't forget to initialise any required fields that don't
' have a DV defined.
rs!RequiredField = "Some Value"

' get the number we want
dwNumber = rs!ANField

' save it
rs.Update

' all done
rs.Close

' return the value
NewRecordIDNumber = dwNumber

End Function



Hope that helps


Tim F

 

[ZorpiedoMan]

"The @@IDENTITY function works if you are using ADO, but only if you know
that nobody has inserted another record into _any_ table in the meantime.

That is not true. The @@Identity is a connection global variable. As long as YOU (you being your current connection to the databaes) has not added any rows to any tables, @@Identity WILL return the last ID of the last record added to a table with an Identity column

Therefore calling "Select @@Identity" immediately after your Insert statement will return the new ID

-zorpy

 

[Tim Ferguson]

Therefore calling "Select @@Identity" immediately after your Insert
statement will return the new ID.

My understanding is that the general feeling on m.p.a.adp.sqlserver is that
this is not secure enough to be trusted. But I live to learn....

B Wishes


Tim F