scratch the <cftransaction>, Access does not support it
<cfquery name="qry_addcontact" datasource="#DSN#">
INSERT INTO Contact( the fields... )
VALUES ( the values... )
</cfquery>
<cfquery name="qry_getnewid" datasource="#DSN#">
SELECT MAX(ID) AS TheID
FROM ContactInfo
</cfquery>
<cfquery name="qry_addcontactinfo" datasource="#DSN#">
INSERT INTO ContactInfo ( ContactID, the fields... )
VALUES (#qry_getnewid.TheID#, the values..)
</cfquery>
The cftransaction tag will ensure that no other records will be inserted
into these tables while processing the queries between the start and end of
the transaction.
-s
Stuart Miller
Rocom New Media
t: 01937 487492
e: [EMAIL PROTECTED]
w: http://www.rocom.co.uk
-----Original Message-----
From: Shannon Rhodes [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 2:41 PM
To: CF-Talk
Subject: Question: Creating a primary key field
Is it possible to query which was the last ID number created (autonumber
field in Access) for the purpose of adding 1 to explicitly state the new ID
number for a newly created record?
The reason why I ask is that I'm having trouble inserting my new info into
two separate tables because I need the newly created ID number for table A
to be inserted into table B. If I can't "guess" what the number will be,
I'm stuck trying to take the user through a second page where I insert into
table A then turn around and query the new record to insert the new field
into table B. That leaves me with the problem of what to do if the user
abandons the application on the second page, leaving no new record created
in table B.
This may sound like screwy application design (and maybe it is, I'm still
pretty new at this), but the problem is that table A contains contact
information, while table B contains all other information (class
registration, payment methods, etc), and they have a one-to-one relationship
because I may need to change which person the info is associated with (it's
possible that a person will register and pay for a class, but a co-worker
will attend in their place). I can't make the classID a primary key in
table B either (the person could decide to switch classes) so I just have an
autonumber as a primary key. Argh! All suggestions welcomed.
TIA,
Shannon
[EMAIL PROTECTED]
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
