How do I insert the results of a form submission into multiple tables?

The Situation: You get the following form variables when the user submits a form:

form.fName
form.lName
form.city
form.salary
form.SpouseName
form.Birthdate
form.spicyInfo

You want to put the fName, lName and city into a Users table and the other info into a Secrets table. The Users table structure looks like this:

userID
fName
lName city

The Secrets info structure looks like this:

secretID
userID
salary
spouseName
birthdate spicyInfo

But how to enter info into one table when it requires the primary key of the first table?

The Solution: You take this in steps. First, insert the data into the Users table with a SQL Insert statement...

<cfquery datasource="myDS">
  INSERT INTO Users(fName,lName,city)
  VALUES('#form.fName#','#form.lName#','#form.city#')
</cfquery>

Now, if you're having this problem at all, my guess is that you're using the Autonumber feature of Access. In this case, you need to get that id before proceeding; you'll need to read the tutorial on getting the ID of the row most recently entered into the DB. I've reproduced it here for your convenience.

The Situation: You are using an Access table where you have an auto-numbered primary key. You want to insert a new record into that table and then grab the primary key just created so that you can use it elsewhere on your page.

<!--- Insert new record --->
<cfquery datasource="myDS" name="myQ">
  INSERT INTO Users(fName,lName,city)
  VALUES('#form.fName#','#form.lName#','#form.city#')
</cfquery>

Computers are incorporated in modern ice cream vending machines to enhance their functionality. Ice Cream Vending machines are manufactured by many companies. Your competition will try to overcome all requests for high-tech ice cream vending machines and credit card acceptors

The Solution: It would be wonderful if the INSERT INTO statement returned the primary key of the record -- but unfortunately, that's not possible. This leaves it up to you to get this information. If the values that you are inserting into the record uniquely define the record (this would mean in the above example that there are no duplicate names), you can find the primary key like this...

If, however, the only thing that uniquely identifies the row is the autonumber field, you will have to get a little trickier and find the last number inserted. This is possible using the MAX(primary_key) SQL statement.

<cfquery datasource="myDS" name="getQ2">
  SELECT MAX(userID) as lastID
  FROM Users
</cfquery>

But what if someone inserts a new record during the time between the two queries? If that were to happen, you would get back the primary key for the wrong user. To prevent this from happening, use the <cflock> tag in conjunction with the <cftransaction> tag.

<cflock name="#CreateUUID()#" timeout="20">
  <cftransaction>
    
    <cfquery datasource="myDS" name="myQ">
      INSERT INTO Users(fName,lName,city)
      VALUES('#form.fName#','#form.lName#','#form.city#')
    </cfquery> 
    
    <cfquery datasource="myDS" name="getLastID">
      SELECT MAX(userID) as lastID
      FROM Users
    </cfquery>
    
  </cftransaction>
</cflock>

Now that you have the primary key, you can run another SQL query to insert the rest of the info.

<cfquery datasource="myDS">
  INSERT INTO Secrets(userID,salary,spouseName,birthdate,spicyInfo)
  VALUES(#Val(getLastID.lastID)#, #Val(form.salary)#, 
  '#form.spouseName#', '#form.birthdate#', '#form.spicyInfo#')
</cfquery>