Adding Data and Get Current Record

The autonumber is not assigned until the record is inserted.  You can always query the most recent autonumber by using the following:

Select Top 1 RecordID from YourTable

Regards,
meapledoo

Extra note:
@@identity returns the latest values of the inserted record, which is last and you want.
There is another method also though that is not efficient and little slwo also
where you reverse the query and get the max number though I do not prefer that method.
Regards,
---Pinal

well the above single query does all the work instead of either running other query where either you reverse the table and get the top one record or get the max autonumber

you can use the query as
select top 1 recordid from yourtable order desc
though you have to do this in next query.
Regards,'
---Pinal

I am writing now the code of the alternate method which I discussed above.

<cfquery datasource="#DSN#" name="staff_detail">
      insert into staff(name) values ("Pinal Dave");
      </cfquery>


      <cfquery datasource="#DSN#" name="staff">
      SELECT TOP 1 * FROM Staff_contact
      ORDER BY id DESC
      </cfquery>

This is alternate method.
Regards,
---Pinal

It just seems like there should be something easier like some kind of variable that is generated when the actual insert query occurs.

dear sir,
the variable is
@@identity
regards,
---Pinal

to use that variable you can use the query like following
<cfquery datasource="#dsn#">
          declare @incomingmailid int;
          insert into incomingmail (contactid) values (#contactid#);
          select @incomingmailid = @@identity;
          insert into interactions (incomingmailid)
          values (@incomingmailid);
</cfquery>
Regards,
---Pinal
PS. see the usage of the variable @@identity

I am trying to eliminate the need to do 2 queries.

Tell me more about the @@identity.

And just so you know, the backend is MS Access.  I've never seen the query commands you used above and am wondering if Access will handle that.

hum... i never tried to do that with MS Access. It should work with that too.
If not then you can try with above mentioned 2 query. I do not see any other way with MS access.
Regards,
---Pinal

OK, here's another approach.  I tried to do this but i received an error:

INSERT INTO table_name (col1, col2)
VALUES ('value 1', 'value 2')
SELECT * FROM table_name


Anybody ever had any success with this order?  It appears that you may not be able to combine a SELECT and INSERT command.

It will not work. You can do select and insert which helps but not serves your purpose.
Above aproach is not possible syntaxt.
Regards,
---Pinal

Please note @@Identity only works with SQL server not all databases

In general a good method is to have a unique identity (like row number)  an autonumber or identity field,etc

Then enclose two separate queries (a insert followed by a select) inside a set of cftransaction tags.

This is the best cross-platform method available

<cftransaction action="begin">
  <cfquery datasource="mydsn">
     INSERT INTO mytable (col1, col2, col3, etc) VALUES (#form.col1#, #form.col2# #form.col3#, #form.etc#)
  </cfquery>
  <cfquery datasource="mydsn" name="GetnewID">
     SELECT MAX(ID) FROM mytable
  </cfquery>
  <cfset NewID = GetnewID.ID>
<cftransaction action="commit">
</cftransaction>

Looks like it will work but what does the cftransaction do as opposed as two independent queries?  I guess my biggest concern is two users updating the database at the same moment.