Getting ID of Last Record Added

[cascot]

I'm looking to get the Id (Autonumber) of the last record created within a table by a querydef run during a transaction.

At the moment I have something like:

Dim ws As Workspace

Set db = CurrentDb
Set ws = DBEngine.Workspaces(0)

Set qd = db.QueryDefs("AppendToTableXYZ&quot

ws.BeginTrans 'Begin Import Transaction
On Error GoTo TransErr
qd.Execute dbFailOnError + dbSeeChanges

Set rs = db.OpenRecordset("SELECT XYZId FROM XYZ", dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveLast
LastXYZId = rs!XYZId
End If

But this doesn't appear to work.

What is the correct way to obtain details of any records added within a transaction which has not yet been committed?

 

[grtammi]

Hi cascot ...

In an auto-number field, the last record added is always going to have the highest ID number. Therefore, in the SQL statement you use to retrieve the recordset, simply replace your existing code:

Set rs = db.OpenRecordset("SELECT XYZId FROM XYZ", dbOpenSnapshot)
If rs.RecordCount > 0 Then
  rs.MoveLast
  LastXYZId = rs!XYZId
End If

with the following:

Set rs = db.OpenRecordset("SELECT Max(XYZId) AS MaxID FROM XYZ", dbOpenSnapshot)
LastXYZId = rs!MaxId

Tada! Problem solved - this SQL statement returns one record, and one record only: the highest ID number in the field specified.

HTH

Greg

 

[gol4]

not sure if this is helpful with your situation but I usually get the autonumber using the lastmodified property but usually under diffrent circumstances.
I threw it out just in case it might help.

Text from access help
The record that was current before you used AddNew remains current. If you want to make the new record current, you can set the Bookmark property to the bookmark identified by the LastModified property setting.

 

[Escolar]

Another method is to use the DMAX function as follows:

LastID = DMAX("ThisID","TableName&quot.

This function puts the highest value of "ThisID" from table "Tablename" into variable "LastId"
Good luck

 

[GummowN]

I tend to use the domain method above, but if you are using linked tables dmax can cause performance issues try the following function to replace dmax

Function tMax(pstrField As String, pstrTable As String, Optional pstrCriteria As String) As Variant
On Error GoTo tMax_Err
' Function tMax
' Purpose: Replace DMax, which is slow on attached tables
' Created: 1 Feb 1996 T.Best

Dim dbCurrent As Database
Dim rstLookup As Recordset
Dim varValue As Variant

Set dbCurrent = DBEngine(0)(0)
If pstrCriteria = "" Then
Set rstLookup = dbCurrent.OpenRecordset("Select Max([" & pstrField & "]) From [" & pstrTable & "]", DB_OPEN_SNAPSHOT)
Else
Set rstLookup = dbCurrent.OpenRecordset("Select Max([" & pstrField & "]) From [" & pstrTable & "] Where " & pstrCriteria, DB_OPEN_SNAPSHOT)
End If
If Not rstLookup.BOF Then
rstLookup.MoveFirst
varValue = rstLookup(0)
Else
varValue = Null
End If
rstLookup.Close
tMax = varValue



tMax_Exit:
On Error Resume Next
'**** Place extra cleanup code here *****

' **** Uncomment next lines if needed
'docmd hourglass false
'varDummy=syscmd(SYSCMD_REMOVEMETER)
Exit Function

tMax_Err:
Select Case Err
Case Else
End Select

' Retry/Abort/Ignore
Select Case MsgBox(Error, MB_ABORTRETRYIGNORE Or MB_ICONEXCLAMATION, "Error " & Err)
Case IDABORT
Resume tMax_Exit
Case IDRETRY
Resume
Case IDIGNORE
Resume Next
End Select
Exit Function
End Function