getting autonumber from last INSERT?

[Steve Leferve]

Hey folks -

I have some VBA code for creating a row and related sub-rows from a 'wizard'
form.

I'm using sql 'INSERT' statements to create the rows.
My problem is that I need to get the autonumber id field from the parent
table to properly insert the rows in the subtable. PHP has a function for
MySQL called mysql_last_id(). Is there something similar in VBA?

Currently I am using a "SELECT Max(id)" query to get what should be the id #
of the latest record. However, it doesn't seem to work -- it's giving me the
second-latest id, not from the most recently executed query.

Can I make this work with SQL, or should I use DAO recordsets?

Steve Lefevre

[Trevor Best]

Steve Leferve wrote:

Hey folks -

I have some VBA code for creating a row and related sub-rows from a 'wizard'
form.

I'm using sql 'INSERT' statements to create the rows.
My problem is that I need to get the autonumber id field from the parent
table to properly insert the rows in the subtable. PHP has a function for
MySQL called mysql_last_id(). Is there something similar in VBA?

Currently I am using a "SELECT Max(id)" query to get what should be the id #
of the latest record. However, it doesn't seem to work -- it's giving me the
second-latest id, not from the most recently executed query.

Can I make this work with SQL, or should I use DAO recordsets?

Steve Lefevre

Using a DAO recordset you'd do something like:

with rst
.AddNew
!Foo = bar
.update
.Move 0, .LastModified ' [1]
' you are now on the record you just inserted
end with

with ADO I think you're on the new record as soon as insert it but I've
seen inconsistencies here between ADO performed in VB (VB and VBA) and
in VBScript (ASP) so I wouldn't rely on that. If you open the recordset
"where false" so no records are returned then insert one then yours
should be the only one there so a .movelast or .movefirst should get you
onto that record but that may depend on your recordset being able to see
updates from other users (e.g. dbSeeChanges in DAO[2]).

Using SQL you're pretty much stuck unless going against a SQL Server in
which case SCOPE_IDENTITY() [3] is your friend.

[1] Check that against .LastUpdated, I may have mixed them up.
[2] To update a DAO recordset on SQL Server you need dbSeechanges so
this method would be NFG, use an SP or pass-through query and see next
footnote.
[3] There is a @@IDENTITY system variable in SQL Server but that may be
affected by inserts performed by insert triggers.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer