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"
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?
At the moment I have something like:
Dim ws As Workspace
Set db = CurrentDb
Set ws = DBEngine.Workspaces(0)
Set qd = db.QueryDefs("AppendToTableXYZ"
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?