updating 2 tables using coldfusion sql ... newbie so be gentle with me ;)

Could you tell me where exactly is the problem? Does the INSERT statement doesn't happen? do you get an error? where do things fail for you?
Also, why are you using "cf_sql_clob" as the cfqueryparam sql type? Would "cf_sql_varchar" not do?
Harel

just tell what is the problem

Thanks for responding.

My Insert statement does work and it will insert data 1 Table (propertyType1) but I need to update the (region) table too i.e. when a user selects a region and a type of property, I cant get the region and propertyType to update too. I hope I am not sounding too feeble!

I need to pass on the <region> and <propertyType> onto the insert page so the whole record is inserted

try to put hidden variable before the submit button
 for example
add_new.cfm
 
<cfquery name="findregion" datasource="cpl">
SELECT DISTINCT region
FROM region
  </cfquery>
 
  <cfform name="findregion" action="Add_property.cfm">
    <h2 align="center">Select region
        <select name="selectregion">
          <cfoutput query="findregion">
            <option value="#findregion.region#">#findregion.region#</option>
          </cfoutput>
        </select>
        <input name="Submitregion" type="submit" value="Submit">
              <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
                                    <input type="hidden" value="#FORM.findregion.region#" name="FORM.findregion.region">
 
 
    </h2>
  </cfform>
 
<!--- next page --->
 
Add_property.cfm
 
<cfparam name="FORM.regionId" default="1">
 
<cfquery name="findregion" datasource="cpl">
SELECT DISTINCT region
FROM region
  </cfquery>
 
  <cfform name="findregion" action="Add_property.cfm">
    <h2 align="center">&nbsp;</h2>
    <h2 align="center">&nbsp;</h2>
    <h2 align="center">Select region
        <select name="selectregion">
          <cfoutput query="findregion">
            <option value="#findregion.region#">#findregion.region#</option>
 
        </cfoutput>
      </select>
    </h2>
  </cfform>
 
<cfquery name="selectProperty" datasource="cpl">
SELECT *
FROM propertyType1
WHERE regionId = <cfqueryparam value="#FORM.regionId#" cfsqltype="cf_sql_clob" maxlength="50">
</cfquery>
 
<cfparam name="selectProperty" default="retail" type="Any">
<cfform name="selectProperty" action="add_propertyType.cfm" method="post">
      <div align="center">
        <h2>Property Type
          <select name="selectProperty">
            <option value="retail">Retail</option>
            <option value="Office">Office</option>
            <option value="Industrial">Industrial</option>
            <option value="Land Plots">Land Plots</option>
          </select>
          <input name="selectProperty" type="submit" value="Submit">
          <cfoutput>
            <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
          </cfoutput>
      </h2>
      </div>
</cfform>
 
<!--- last page --->
 
add_propertyType.cfm
 
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="cpl">  
    INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)
VALUES (<cfif IsDefined("FORM.Description") AND #FORM.Description# NEQ "">
<cfqueryparam value="#FORM.Description#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address1") AND #FORM.address1# NEQ "">
<cfqueryparam value="#FORM.address1#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address2") AND #FORM.address2# NEQ "">
<cfqueryparam value="#FORM.address2#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.townCity") AND #FORM.townCity# NEQ "">
<cfqueryparam value="#FORM.townCity#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.postcode") AND #FORM.postcode# NEQ "">
<cfqueryparam value="#FORM.postcode#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
  </cfquery>
  <cflocation url="#CurrentPage#?#CGI.QUERY_STRING#">
</cfif>
<cfquery name="Recordset1" datasource="cpl">
SELECT *
FROM propertyType1
</cfquery>
 
<form method="post" name="form1" action="<cfoutput>#CurrentPage#?#CGI.QUERY_STRING#</cfoutput>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Description:</td>
      <td><input type="text" name="Description" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address1:</td>
      <td><input type="text" name="address1" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address2:</td>
      <td><input type="text" name="address2" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TownCity:</td>
      <td><input type="text" name="townCity" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Postcode:</td>
      <td><input type="text" name="postcode" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
          <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
 
</form>
 <input name="selectProperty" type="submit" value="Submit">

Als ouse use cfdump on the form to see what get passed
<cfdump var="form">

sorry wrong post try to put hidden variables before submit
<cfoutput>
            <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
          </cfoutput>
<input name="selectProperty" type="submit" value="Submit">

Als0 use use cfdump on the form to see what get passed
<cfdump var="form">

thanks for the response again,

Ok I copied your code above and my database table is still the same. Although the data from the last page with the insert goes in, the <region> and <propertyType> still doesnt.
Hehe I have never used <cfdump> before so when i tried it using <cfdump var="form"> of course all I got was "form"!!! Told you I was a newbie!

cfdump for is used to see the form variables, also you can use url,variables, sessions and so on

Do you have access to the coldfusion administrator

If you do can you see if there is an error in the insert

I dont see an error in the INSERT

All the data from the insert or last page gets inserted into the database table (propertyType1) with no error but none of the previous 2 pages data is passed

the other way is to try use url.variables, for example
<cfform name="findregion" action="Add_property.cfm?region=#form.selectregion#">

ok I will give it a try and let you know.

and use <cfdump var="#url#">

To see is they get passed

hmm,
I tried that but of course I get this error:


 Error Occurred While Processing Request
Element SELECTREGION is undefined in URL.
 
The error occurred in C:\Inetpub\wwwroot\Edinburgh Commercial\add_new.cfm: line 41

39 :   </cfquery>
40 :  
41 :  <cfform name="findregion" action="Add_property.cfm?region=#url.selectregion#">

what I cant understand really is why it seems so hard to update 2 tables using coldfusion and sql. Surely there has to be an easy answer?

I think this is all over complicated - passing both url and form paramters.
What you need is one of two options: pass all the variables from one form to the next using hidden fields,
or between each step set a session struct holding the data and in the last step use that struct to create and/pr update your database.
You can encapsulate all this into a couple of functions. See (oversimplified) example below.
In that example, I've used a function to copy the form submission to the session. It allows you to hold the entire form state in your session and because of that you can extend your UI to be a wizard style form with forward/back buttons to cycle between the different pages. The function always returns  the full session data struct which holds all the form's submissions to date. Note that fields named the same will overwrite themselves.
Using this approach you don't hvae to rely on hidden fields etc.
You can then use that struct alone to insert/update your database. You can also validate it as a whole before you commit to the db - because its in the session you can show the users's their errors and allow them the chance to correct them.
This is a simplified example which can be taken many degrees further but I thiink it carries my point.
My other option was to use a similar function which takes a form submission and returns it as a big string of hidden fields.

<!--- put this in all your form and form processing pages --->
<cffunction name="formToSession" returnType="struct" output="no">
	<cfargument name="data" type="struct" required="true">
	<cfargument name="overwrite" type="boolean" required="false" default="true">
	<!--- sets the default session struct to hold the data if it doesn't exist --->
	<cfparam name="SESSION.form_data" default="#structNew()#">
	<cfset structAppend(SESSION.form_data, arguments.data,  arguments.overwrite)/>
	<cfreturn SESSION.form_data/>
</cffunction>	
 
<!--- if a form is submitted into this page, copy all the data to the session struct and return the current session struct --->
<cfif not structIsEmpty(FORM)>
	<cfset formData = formToSession(form)/>
</cfif>
<!--- formData now holds all the data from all the form's pages. use that struct to access any form data from any step. At the end, remember to do a structDelete(session.form_data) or session.form_data=structNew() to clear out that session struct. --->

Select allOpen in new window

ok I think I am just about following that Yamagami. I have inserted your code to my pages but... it still doesn't update my database fully. Only the last page is inserted. Maybe I actually need to take a different approach.
All I need to do is have users enter a property. The property is in a region (region table), the property can also be an office, retail, industrial etc and it has an address in the city. This approach I have been using is one idea but maybe there is something simpler or easier?
Thank you for your help so far.

scooferman:
you need to put this code at the top of each page. Then instead of accessing the FORM.variables you simply access formData.
For exmple, instead of looking at FORM.Description, you'll look at formData.description.
This is not a magic formula. Its just one way to go about it. You do need to modify your page to use that method. The other option is to make sure all the fiels you submit are present in EACH form as hidden variables and thus are trasnferd between each form.
Harel

one more thing, i only see one insert statement there. where is the 2nd one?

oh... I didn't know you can use more than one insert statement. Do they have to be nested?

You can use 2 separate CFQUERY tags... one after the other.
(You can put 2 insert statements right after the other in one cfquery tag but its not as straight forward and i doubt it would work with an access db).
Your code simply does not contain another insert - this might be why you're only seeing one row  inserted...

Thanks for that Harel. I will now try to have a look at this (I was just making my dinner!)

Ok I have now updated my code and it is below. I now get a curious error which reads:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''' INSERT INTO region (regionId, region) VALUES ( '' , '' )'.
 
The error occurred in C:\Inetpub\wwwroot\Edinburgh Commercial\add_propertyType.cfm: line 57

55 : <cfelse>
56 : ''
57 : </cfif>
58 : )
59 :   </cfquery>

***************************************************************************************

Interesting!

<cffunction name="formToSession" returnType="struct" output="no">
	<cfargument name="data" type="struct" required="true">
	<cfargument name="overwrite" type="boolean" required="false" default="true">
	<!--- sets the default session struct to hold the data if it doesn't exist --->
	<cfparam name="SESSION.form_data" default="#structNew()#">
	<cfset structAppend(SESSION.form_data, arguments.data,  arguments.overwrite)/>
	<cfreturn SESSION.form_data/>
</cffunction>	
 
<!--- if a form is submitted into this page, copy all the data to the session struct and return the current session struct --->
<cfif not structIsEmpty(FORM)>
	<cfset formData = formToSession(form)/>
</cfif>
<!--- formData now holds all the data from all the form's pages. --->
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="cpl">   
    INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)
VALUES (<cfif IsDefined("FORM.Description") AND #FORM.Description# NEQ "">
<cfqueryparam value="#FORM.Description#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address1") AND #FORM.address1# NEQ "">
<cfqueryparam value="#FORM.address1#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address2") AND #FORM.address2# NEQ "">
<cfqueryparam value="#FORM.address2#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.townCity") AND #FORM.townCity# NEQ "">
<cfqueryparam value="#FORM.townCity#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.postcode") AND #FORM.postcode# NEQ "">
<cfqueryparam value="#FORM.postcode#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
INSERT INTO region (regionId, region)
VALUES (<cfif IsDefined("FORM.regionId") AND #FORM.regionId# NEQ "">
<cfqueryparam value="#FORM.regionId#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.region") AND #FORM.region# NEQ "">
<cfqueryparam value="#FORM.region#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
  </cfquery>
  <cflocation url="#CurrentPage#?#CGI.QUERY_STRING#">
</cfif>
 
 
<cfquery name="Recordset1" datasource="cpl">
SELECT *
FROM propertyType1 
</cfquery>
<cfquery name="Recordset2" datasource="cpl">
SELECT *
FROM region 
</cfquery>
 
<form method="post" name="form1" action="<cfoutput>#CurrentPage#?#CGI.QUERY_STRING#</cfoutput>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Description:</td>
      <td><input type="text" name="Description" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address1:</td>
      <td><input type="text" name="address1" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address2:</td>
      <td><input type="text" name="address2" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TownCity:</td>
      <td><input type="text" name="townCity" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Postcode:</td>
      <td><input type="text" name="postcode" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
          <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
  <input name="selectProperty" type="submit" value="Submit">
 
</form>
 
<p>&nbsp;</p>

Select allOpen in new window

and I gave you the wrong error....

it should have been:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement.
 
The error occurred in C:\Inetpub\wwwroot\Edinburgh Commercial\add_propertyType.cfm: line 57

55 : <cfelse>
56 : ''
57 : </cfif>
58 : )
59 :   </cfquery>

how is you sql looks like. Do you have debugger enabled? that will tell you how is your query looks like

without seeing the sql, i'd say add a semi colon (;) at the end of your sql statement. perhaps access needs i? (never worked with access so I don't know).

I have my debugger on and I have tried adding a semicolon after the first INSERT sql and I get the error message
"Characters found after end of SQL statement"

If I add it onto the end of the second it still says I have a missing semicolon! I will attach all the code for that page

<cffunction name="formToSession" returnType="struct" output="no">
	<cfargument name="data" type="struct" required="true">
	<cfargument name="overwrite" type="boolean" required="false" default="true">
	<!--- sets the default session struct to hold the data if it doesn't exist --->
	<cfparam name="SESSION.form_data" default="#structNew()#">
	<cfset structAppend(SESSION.form_data, arguments.data,  arguments.overwrite)/>
	<cfreturn SESSION.form_data/>
</cffunction>	
 
<!--- if a form is submitted into this page, copy all the data to the session struct and return the current session struct --->
<cfif not structIsEmpty(FORM)>
	<cfset formData = formToSession(form)/>
</cfif>
<!--- formData now holds all the data from all the form's pages. --->
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="cpl">   
    INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)
VALUES (<cfif IsDefined("FORM.Description") AND #FORM.Description# NEQ "">
<cfqueryparam value="#FORM.Description#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address1") AND #FORM.address1# NEQ "">
<cfqueryparam value="#FORM.address1#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address2") AND #FORM.address2# NEQ "">
<cfqueryparam value="#FORM.address2#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.townCity") AND #FORM.townCity# NEQ "">
<cfqueryparam value="#FORM.townCity#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.postcode") AND #FORM.postcode# NEQ "">
<cfqueryparam value="#FORM.postcode#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
) ;
INSERT INTO region (regionId, region)
VALUES (<cfif IsDefined("FORM.regionId") AND #FORM.regionId# NEQ "">
<cfqueryparam value="#FORM.regionId#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.region") AND #FORM.region# NEQ "">
<cfqueryparam value="#FORM.region#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
  </cfquery>
  <cflocation url="#CurrentPage#?#CGI.QUERY_STRING#">
</cfif>
 
 
<cfquery name="Recordset1" datasource="cpl">
SELECT *
FROM propertyType1 
</cfquery>
<cfquery name="Recordset2" datasource="cpl">
SELECT *
FROM region 
</cfquery>
 
<form method="post" name="form1" action="<cfoutput>#CurrentPage#?#CGI.QUERY_STRING#</cfoutput>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Description:</td>
      <td><input type="text" name="Description" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address1:</td>
      <td><input type="text" name="address1" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address2:</td>
      <td><input type="text" name="address2" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TownCity:</td>
      <td><input type="text" name="townCity" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Postcode:</td>
      <td><input type="text" name="postcode" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
          <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
  <input name="selectProperty" type="submit" value="Submit">
 
</form>

Select allOpen in new window

try to take the query into acees and execute from thrre so you can get the synatx right

tried that with access and I cant get it to insert into more than one table. Maybe this is an access issue and I should try MYSQL instead

Don't put the two sql queries in the SAME CFQUERY tag. Use 2 separate CFQUERY tags. See my comment from 8:57 AM above.

<cfquery name="query_name_1" datasource="">
   INSERT into blah blah
</cfquery>
 
<cfquery name="query_name_2" datasource="">
   SECOND INSERT into blah blah
</cfquery>

Select allOpen in new window

Harel,
Thanks again for your continued support. I have pasted the code now containing the 2 queries. I think you will see that it is ok. However I now get this error: Index or primary key cannot contain a Null value. So when I include the "regionID" as a primary key I get the message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
There is just no winning!!!
 

<!--- put this in all your form and form processing pages --->
<cffunction name="formToSession" returnType="struct" output="no">
	<cfargument name="data" type="struct" required="true">
	<cfargument name="overwrite" type="boolean" required="false" default="true">
	<!--- sets the default session struct to hold the data if it doesn't exist --->
	<cfparam name="SESSION.form_data" default="#structNew()#">
	<cfset structAppend(SESSION.form_data, arguments.data,  arguments.overwrite)/>
	<cfreturn SESSION.form_data/>
</cffunction>	
 
<!--- if a form is submitted into this page, copy all the data to the session struct and return the current session struct --->
<cfif not structIsEmpty(FORM)>
	<cfset formData = formToSession(form)/>
</cfif>
<!--- formData now holds all the data from all the form's pages. --->
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
  <cfquery datasource="cpl">   
    INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)
VALUES (<cfif IsDefined("FORM.Description") AND #FORM.Description# NEQ "">
<cfqueryparam value="#FORM.Description#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address1") AND #FORM.address1# NEQ "">
<cfqueryparam value="#FORM.address1#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.address2") AND #FORM.address2# NEQ "">
<cfqueryparam value="#FORM.address2#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.townCity") AND #FORM.townCity# NEQ "">
<cfqueryparam value="#FORM.townCity#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
, <cfif IsDefined("FORM.postcode") AND #FORM.postcode# NEQ "">
<cfqueryparam value="#FORM.postcode#" cfsqltype="cf_sql_clob" maxlength="50">
<cfelse>
''
</cfif>
)
  </cfquery>
  <cflocation url="#CurrentPage#?#CGI.QUERY_STRING#">
</cfif>
<cfquery datasource ="cpl">
INSERT INTO region (region)
VALUES (<cfif IsDefined("FORM.region") AND #FORM.region# NEQ "">
<cfqueryparam value="#FORM.region#" cfsqltype="cf_sql_clob" maxlength="1073741823">
<cfelse>
''
</cfif>
 
)
 
</cfquery>
 
 
<cfquery name="Recordset1" datasource="cpl">
SELECT *
FROM propertyType1 
</cfquery>
 
<form method="post" name="form1" action="<cfoutput>#CurrentPage#?#CGI.QUERY_STRING#</cfoutput>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Description:</td>
      <td><input type="text" name="Description" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address1:</td>
      <td><input type="text" name="address1" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Address2:</td>
      <td><input type="text" name="address2" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">TownCity:</td>
      <td><input type="text" name="townCity" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Postcode:</td>
      <td><input type="text" name="postcode" value="" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_InsertRecord" value="form1">
          <input type="hidden" value="#FORM.selectProperty#" name="FORM.selectProperty">
  <input name="selectProperty" type="submit" value="Submit">
 
</form>
 
<p>&nbsp;</p>

Select allOpen in new window

You missed out some vital info so I'll just give an educated guess: I thinkyou mean the regionid in the region table, right? If so, I think that that field is not set to be an 'auto incremented' field. If it was, it would assign itself a numeric unique value upon each insert, and it would have no problem being the primary key (and SHOULD be).  I *think* that in access an auto incremented field is called like it is in MsSql, which is "Identity" field. But i'm not 100% sure.  Take a look at access and the table designer thingy there  - i'm sure its an easy thing to set.

Hello again.
Ok I have done as you said and made the "regionID" an autonumber. Although I am now not getting errors and the "propertyType1" table is being updated with "description, address1, address2, townCity, postcode", I am not getting the "region" or "retail, office, industrial, land plot" to be updated. below is the layout of my 2 tables to be updated

here are the table layouts:
 
propertyType1 table:
 
propertyId (primary key) AutoNumber
regionID (Foreign key) text
retail text
office text
industrial text
landplots text
lock_ups text
developments text
address1 text
address2 text
townCity text
postCode text
***************************
region table
 
regionID (primary key) AutoNumber
region text

Select allOpen in new window

First of all, you are only inserting "Description", address1, address2, townCity, postcode into the table. You are not inserting office or retail or regionId:

INSERT INTO propertyType1 ("Description", address1, address2, townCity, postcode)

Also, to insert region Id you'll need to run that query first and then get the newly created id. There are a few ways to do this, however, as access is NOT a real database that is to be used for web applications, perhaps not all of them will work (I suggest you consider moving to a proper sql database if what you are doing is intended for public use).
What you need to do is :
perform  the region table insert first
query the region table for the highest id (or if access can report back the latest newly created autonumber - get that).
then do the second propertyType1 insert, using the value from the previous query for the region Id.
You should encapsulate the queries above in a CFTRANSACTION tag but i do'nt know how access will take it.
I dont know if you did that before but when you insert the data into the region id, you should in theory check if the region you are inserting exists already and if so, use its region id instead of creating a new record. However, if you don't mind the repetition of data, keep it as is.

Ahh been a while I know ... yes I'm still here!

Ok I have now got coldfusion 8 working properly after sorting out issues with dreamweaver but thats another epic story!
I have installed MySQL and managed to update the datasource (which again was another battle - you can see why I have been away for a while!!!)

So back to business. I think (and hope) that using MySQL will sort out a few of my issues with inserting into 2 tables.

So if we can take it from here any advise would be MUCH appreciated

Thanks