Get field value from last record inserted using TransferSpreadsheet

[makenoiz]

I am importing an excel worksheet into an existing access 2000 table using TransferSpreadsheet. Im using a timestamp field as well in this table. Once the data is imported I need to find the last record imported and pull the value from the Code field. Then add that value to another table where Code is the Key Value. This is all one continuous operation in the same sub and executing directly after the DoCmd.TransferSpreadsheet

Can anyone point me in the right direction. Ive been struggling for several days and , yes I have searched and searched and experimented and experimented.



Thank you in advance

[hansup]

You could add an autonumber primary key field to your destination table, then fetch the max autonumber value after DoCmd.TransferSpreadsheet to identify which row was added last. And then use DLookup to retrieve the field value you want from that row.

In this example, id is my autonumber primary key. And I want the equip_id value from the last inserted row.
Public Sub makenoiz()
Dim strXls As String
Dim lngLastId As Long
Dim lngLastEquip_Id As Long

strXls = CurrentProject.Path & Chr(92) & "temp.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "makenoiz", strXls, True
'Debug.Print CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)
lngLastId = CurrentDb.OpenRecordset("SELECT Max(id) FROM makenoiz;")(0)
lngLastEquip_Id = DLookup("equip_id", "makenoiz", "id = " & lngLastId)
Debug.Print "lngLastEquip_Id: " & lngLastEquip_Id
End Sub