Get PK or autonumber of Newly appended Record

[Barry A&P]

I am trying to get the primary Key "TRKID" (autonumber) of a record that i
just created with the folowing code. so i can use it in another step of code.

DoCmd.RunSQL _
"INSERT INTO T_Parts ( PartNumber, SerialNumber ) SELECT " & _
Me.PartNumberCombo.Column(0) & ", " & Me.SerialNumber_Txt


If a new part is recieved I Create a record in my parts table and from then
on i refer to that part by its TRKID "trackableID" which is a autonumber
field on my T_Parts. once i create the record i want to create a few record
in other tables using the TRKID of the new record inserted by the SQL above

DoCmd.RunSQL _
"INSERT INTO T_Part_Transactions ( TRKID, TransactionNotes,
QuantityRecieved ) SELECT " & _
"How would i get the TRKID Here???" & ", NewPart Recieved, 1"

If My method is flawed i am open to suggestions

Thanks
Barry

 

[Steve Sanford]

Do you know about the "LastModified Property"?

From Help:

LastModified Property

Returns a bookmark indicating the most recently added or changed record.


HTH

 

[Jeanette Cunningham]

There is a different way of creating a new record that makes it easy to get
the ID.

'start of code ---------
Public Function NewContactKey() As Variant
On Error GoTo Error_Handler

'This function creates a new Contact record and returns the key.

Dim db As DAO.Database
Dim rec As DAO.Recordset

NewContactKey = 0 'Default no record created

Set db = CurrentDb
Set rec = db.OpenRecordset("tblContact", dbOpenDynaset)

'Add the record
With rec
.AddNew
!ContactName = "<New Contact>"
.Update
.Bookmark = .LastModified

NewContactKey = rec!ContactKey
End With

Exit_Procedure:
On Error Resume Next
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
End Function
'end of code -------------------

Replace the object names with your own names.

 

[Barry A&P]

Steve
I will check into it..

Do you think it will be ok with multiple users.. I know were probably
talking nanoseconds but is that important??

Thanks
Barry

 

[Barry A&P]

Jeanette

Thank you so much for your Suggestion..
It took me a little while to figure out how to use it but i think i got it
implemented correctly

Heres what i did

Public Function AddNewTRKID() As Variant 'Claims a new TRKID before
inserting a new record...
On Error GoTo Error_Handler
'This function creates a new Contact record and returns the key.
Dim db As DAO.Database
Dim rec As DAO.Recordset
AddNewTRKID = 0 'Default no record created
Set db = CurrentDb
Set rec = db.OpenRecordset("T_Serialnumbers", dbOpenDynaset)
'Add the record
With rec
..AddNew
!SerialNumber = "<New SN in Progress>" 'add this text to Serialnumber field
as placeholder
..Update
..Bookmark = .LastModified
AddNewTRKID = rec!TRKID 'output new TRKID
End With
Exit_Procedure:
On Error Resume Next
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
End Function

'This is the button that does the work...

Private Sub Submit_CMD_Click()
On Error GoTo Err_Submit_CMD_Click
Dim NewTRKID as Integer
NewTRKID = AddNewTRKID()
Me!TRKID_Txt = NewTRKID

' Here is where i will do all my other code.....

Exit_Submit_CMD_Click:
Exit Sub
Err_Submit_CMD_Click:
MsgBox Err.Description
Resume Exit_Submit_CMD_Click

End Sub

If i am wrong in my usage please help otherwise i will continue On

Thanks again
Barry

 

[Jeanette Cunningham]

That is how I use it too.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Steve Sanford]

What I did was to add a text field to store the user name to know which user
added the record.


'------------------beg code---------------
'need Dim statements

'get network user name
NetUser = fOSUserName() 'text
' see http://mvps.org/access/api/api0008.htm

'clear user name in table by user
sSQL = "DELETE T_Parts.User"
sSQL = sSQL & " FROM T_Parts"
sSQL = sSQL & " WHERE T_Parts.user = '" & NetUser & "';"
CurrentDb.Execute sSQL, dbFailOnError

'create the master record
sSQL = "INSERT INTO T_Parts ( PartNumber, SerialNumber, USER_ID )"
sSQL = sSQL & " VALUES (" & Me.PartNumberCombo.Column(0) & ", "
sSQL = sSQL & Me.SerialNumber_Txt & ", '" & NetUser & "');"
CurrentDb.Execute sSQL, dbFailOnError

'get new TRKID
sSQL = "SELECT TRKID FROM T_Parts"
sSQL = sSQL & " WHERE T_Parts.user = '" & NetUser & "';"
Set rs = CurrentDb.OpenRecordset(sSQL)

If Not rs.BOF And Not rs.EOF Then
NewTRKID = rs!TRKID

'create the child record
sSQL = "INSERT INTO T_Part_Transactions ( TRKID, TransactionNotes,"
sSQL = sSQL & " QuantityRecieved ) "
sSQL = sSQL & " VALUES(" & NewTRKID & ", NewPart Recieved, 1"
CurrentDb.Execute sSQL, dbFailOnError
Else
MsgBox "TRKID not found !!!???"
End If
rs.Close


Set rs = Nothing
'------------------end code---------------



HTH