Ok, I already got some previous help (see the post, "Newbie needs help with
Coldfusion forms"), but I'm still having problems.

Here's my dilemma:

1. I'm using an MS Access database with many tables. The table I'm currently
trying to work with is called 'Providers' and it has a relationship with
another table (Healthcare_Catalog) in the DB through the Provider_Name_Short
field (primary key of the Providers table, and a text field).

2. The website currently uses the Provider_Name_Short field from the
Healthcare_Catalog table foreign key Provider_Name_Short, and it would be
extremely time-consuming and cumbersome to change the primary key field in the
Providers table to an autonumber field.

3. So I'm trying to create an insert, update, delete application that uses
Coldfusion to update, insert, delete fields into the Providers table.

I'm able to update the Provider_Name_Full field allright (it is not a primary
key, and is not related to another table). But for some reason I cannot update
the Provider_Name_Short field in the Providers table when it is a text field (
I can when it is an autonumber field, but like I said I don't want to do that).
I also am unable to add new records to the table.

Here are some of the code snippets that might be causing a problem:

A. From the page that decides whether the user clicked to 'add' or 'update' a
record:

<cfform action="provider_process.cfm" method="post">

<cfif EditMode>
<!--- Embed primary key as a hidden field --->
<cfoutput>
<input type="hidden" name="Provider_Name_Short"
value="#provider.Provider_Name_Short#">
</cfoutput>
</cfif>

<table align="left">
<tr>
<th colspan="2">
<cfoutput>
<h2>#FormTitle#</h2>
</cfoutput>
</th>
</tr>
<tr>
<td>
Name of Provider:</td>
<td>
<cfinput type="text"
name="Provider_Name_Full"
value="#Provider_Name_Full#"
size="40">
</td>
</tr>

<tr>
<td>
Abbreviation:</td>
<td>
<cfinput type="text"
name="Provider_Name_Short"
value="#Provider_Name_Short#"
size="10">
</td>
</tr>

<tr>
<td colspan="2" align="left">
<cfoutput>
<input type="submit" value="#ButtonText#">
</cfoutput>
</td>
</tr>
</table>

</cfform>

From the page that actually processes the request from the previous form:

<!--- Edit or update? --->
<cfif IsDefined("FORM.Provider_Name_Short")>
<!--- Update provider --->
<cfupdate datasource="akhc" tablename="Providers">
<cfset action="updated">
<cfelse>
<! --- Add provider --->
<cfinsert datasource="akhc" tablename="Providers">
<cfset action="added">
</cfif>

There is other code on both pages, but this post is already long enough.
Whoever thinks they can help me, just ask if you need to see more of the
pertinent code. Thank you!

S./