Get id of record just added

[lawsonta]

I have a function that inserts a record into a table after pulling the info from a ADO recordset. This works fine. My question is, is there a way to determine the id of the record just added, so I can use it for the second batch of code....

In the Transactions table, there is a AutoNumber field, "TransactionID" that I need. Any help would be much appreciated!

******************************************************

Dim.....

rs.Open "Select * from qrySQLDictations_Totals", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF

JobNo = rs("JobNumber")
PhyID = rs("PhysicianID")
Lines = rs("LineCountSum")
Chars = rs("JobLength")
TransID = rs("TranscriberID")
TransDate = rs("TranDate")
DicDate = rs("CompleteDate")

strSQL1 = "INSERT INTO Transactions(InternalID," & _
"PhysicianID, ChargeLines, ChargeCharacters, TranscriberID," & _
"TransactionDate, DictateDate)" & _
"VALUES ('" & JobNo & "','" & PhyID & "'," & _
"" & Lines & "," & Chars & ",'" & TransID & "'," & _
"#" & TransDate & "#, #" & DicDate & "#)"

DoCmd.RunSQL strSQL1

'I NEED THE TRANSACTIONID AT THIS POINT SO I CAN CARRY ON WITH MORE CODE
'*********************************************************************************************
rs.MoveNext
Loop
rs.Close
Set rs.ActiveConnection = Nothing
....
End Function

 

[lawsonta]

Okay I got it I guess...there may be an easier way... I set up another recordset to pull the max id as it loops through the first recordset.

Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection

rst.Open "Select MAX(TransactionID) from Transactions", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

AccessTranID = rst(0)