Does anyone can help me for the SQL??

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I want to have an insert statement, after this insert statement, I want to
get back the return value of the primary key ar??
I don't want to requery the select statement, any method I can do that??
 
Hi,


If you use a ADO connection, you can use @@Identity on that same
connection (or a safer method similar to it):

CurrentProject.Connection.execute("SELECT
@@Identity").Fields(0).Value


If you use a DAO recordset, you can read the automatically generated
number as soon as you issue the

rst.AddNew


and before you use the Update method. You can also use the LastModified
property of the recordset to get the bookmark of the modified record, after
you used the Update method.


Hoping it may help,
Vanderghast, Access MVP
 
Jason said:
I want to have an insert statement, after this insert statement, I want to
get back the return value of the primary key ar??
I don't want to requery the select statement, any method I can do that??
Click to expand...
After Insert (Post) you can just read the value of the Primary Key. The
current record is the one you want.
 
Hi,


Not with DAO, as illustrated here below. Cut and paste the following code in
a standard module:

=============
Public Sub DemoADO_DAO_Append()

Dim rst As DAO.Recordset
CurrentDb.Execute "DROP TABLE m123"
CurrentDb.Execute "CREATE TABLE m123 (f1 AUTOINCREMENT, f2 Long)"
CurrentDb.Execute "INSERT INTO m123(f2) VALUES(1) "
CurrentDb.Execute "INSERT INTO m123(f2) VALUES(2) "
CurrentDb.Execute "INSERT INTO m123(f2) VALUES(3) "
Set rst = CurrentDb.OpenRecordset("TABLE m123", dbOpenDynaset)
Debug.Print "Before appending: ", rst.Fields(0).Name, rst.Fields(0)
rst.AddNew
rst.Fields("f2") = 4
Debug.Print "While appending: ", rst.Fields(0).Name, rst.Fields(0).Value
rst.Update

Debug.Print "After appending: ", rst.Fields(0).Name, rst.Fields(0),
"<<<<<"

End Sub
===============
Then, in the Immediate Window, run the procedure and observe:
===============
DemoADO_DAO_Append
Before appending: f1 1
While appending: f1 4
After appending: f1 1 <<<<<
===============

Clearly showing that after the Update, we are back at the record where
we were before appending.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top