Insert New Record into MS Access

almost there, what about the '

must it look like

SELECT' #holdtemp#+1', '#Trim(ARGUMENTS.Academics)#', ....

Error Executing Database Query.  
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.  
 
The error occurred in C:\CFusionMX7\wwwroot\newlifefc\survey\survey.cfc: line 96
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey_process.cfm: line 63
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey.cfc: line 96
Called from C:\CFusionMX7\wwwroot\newlifefc\survey\survey_process.cfm: line 63
 
94 :   INSERT INTO nlfcssurvey(AutoID,Academics,AcademicsComments,Faculty,FacultyComments,Athletics,AthleticsComments,Spiritualvitality,SpiritualvitalityComments,Facilities,FacilitiesComments,otherComments,resource,cur_datetime,name)
95 :  
96 :   SELECT #holdtemp#+1,'#Trim(ARGUMENTS.Academics)#','#Trim(ARGUMENTS.AcademicsComments)#','#Trim(ARGUMENTS.Faculty)#','#Trim(ARGUMENTS.FacultyComments)#','#Trim(ARGUMENTS.Athletics)#','#Trim(ARGUMENTS.AthleticsComments)#','#Trim(ARGUMENTS.Spiritualvitality)#','#Trim(ARGUMENTS.SpiritualvitalityComments)#','#Trim(ARGUMENTS.Facilities)#','#Trim(ARGUMENTS.FacilitiesComments)#','#Trim(ARGUMENTS.otherComments)#','#Trim(ARGUMENTS.resource)#',#CreateODBCDate(ARGUMENTS.cur_datetime)#,'#Trim(ARGUMENTS.name)#'
97 :  
98 :   FROM nlfcssurvey

 

The AutoID you want to insert already exists or if you have more fields that have the property 'Indexed" on 'Yes (No Duplicates)'.

just to test your query, change AutoID in your code to a number you're sure it doesn't exists.

@Nigel-SA,

Why not just make the column an autonumber?

thanks, saved my bacon....