Inserting latest USERID into multiple tables

Take a look at the docs for the cfquery tag: http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_17.html#1102316

Under "usage", you'll see a section for using the "result" attribute to return the id of an inserted row.  Based on what you said in your post, this would probably do the trick.  Of course, there are SQL-based ways of doing this too, but I'd start with the cfquery docs.

Example:

<cfquery name="myquery" datasource="mydsn" result="myresult">
     insert into thetable (stuff, things, whatever)
     values (
          <cfqueryparamm value="#stuff#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#things#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#whatever#" cfsqltype="cf_sql_varchar">
     )
</cfquery>
<cfquery name="secondquery" datasource="mydsn">
     insert into othertable(id,interest,whatever)
     values (
          <cfqueryparamm value="#myresult.identitycol#" cfsqltype="cf_sql_integer">, <!---sql server--->
          <cfqueryparamm value="#interest#" cfsqltype="cf_sql_varchar">,
          <cfqueryparamm value="#whatever#" cfsqltype="cf_sql_varchar">
     )
</cfquery>

NOTE: The result_name.identitycol is database specific.  So whether you're using MySQL, SQL Server, Oracle, etc., the syntax will be a bit different.  Be sure to check the docs to use the one that will work for your environment.

Re: adding the transaction, that wouldn't be a bad idea, although in and of itself it won't accomplish the new id inserting into the second table.  All the transaction will do is allow you set isolation levels on the db transactions and rollback the transactions if the whole process doesn't complete properly.