Eddie:
There's no way to create a "return" value from a jet query to return the
auto number id. There's three approaches that generally are what you need
to consider:
1.) If you are using a simple append query (e.g. by doing an Execute), then
you need to run that query first and then run a select query that uses the
same primary key values from the append to fetch the autonumber.
2.) If you are using DAO and adding the record using .AddNew as part of a
recordset, then, you can use the bookmark and .LastModified methods
properties of the recordset to fetch the ID just added.
3.) If you are using ADO and the .AddNew method on a recordset, there's no
..LastModified property, you can fetch the ID by using the method outlined in
this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;221931
However, in this last case, I would think that using the approach in
suggestion 1, would be faster, especially with large recordsets.
Another way to do it if using the .AddNew method on a Jet based ADO
recordset is set a field variable to the auto number field; which is
implicitly updated, even before the record is committed as shown below:
-------
HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg
---------begin code-----------
Dim rsTest As ADODB.Recordset
Dim rsField As ADODB.Field
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Set rsTest = New ADODB.Recordset
rsTest.Open "Select * from Table2", conn, adOpenDynamic, adLockOptimistic
Set rsField = rsTest.Fields("ID") 'An auto number field
With rsTest
.AddNew
!Field1 = "New Test"
Debug.Print rsField.Value 'this is before the record is committed
.Update
End With
Debug.Print rsField.Value 'after record committed
Set rsField = Nothing
rsTest.Close
Set rsTest = Nothing
