Hey everyone

Need a little bit of help. Im trying to insert new records into a table calll Staff_History (acts as a log to keep a record of everychange made to Database)

my code keeps giving me an error for this history table...
however its adding the records to the actual database via the rest of the code not posted (I have two other insert into statments for staff personal infor table and staff project info table).

The only difference between the 3 tables is the staff History table has a "date" coloumn which records exact date and time, so im assuming there is something wrong with the expression relating to the date (highlighted in red)
.

Any advice on what I need to change to get this working properly?

strSQL = "INSERT INTO Staff_History"
strSQL = strSQL & "( Staff_ID, Date, Last_Name, First_Name, Location, Qualification_Type, Qualification_Discipline, Project, Domain_ID, Scope, Rank, MRB, Airborne_Status, Activity_Status, EA_Status, EA_Cert_Date, Reference_Letter, Exclusions, Exclusion_Notes)"
strSQL = strSQL & "SELECT [Forms]![AddForm2]![New_SID] AS Staff_ID,"
strSQL = strSQL & "[Forms]![AddForm2]![New_DT] AS Date,"
strSQL = strSQL & "[Forms]![AddForm2]![New_LN] AS Last_Name,"
strSQL = strSQL & "[Forms]![AddForm2]![New_FN] AS First_Name,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Loc] AS Location,"
strSQL = strSQL & "[Forms]![AddForm2]![New_QT] AS Qualification_Type,"
strSQL = strSQL & "[Forms]![AddForm2]![New_QD] AS Qualification_Discipline;"
strSQL = strSQL & "[Forms]![AddForm2]![New_Pro] AS Project,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Dom] AS Domain_ID,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Sco] AS Scope,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Rank] AS Rank,"
strSQL = strSQL & "[Forms]![AddForm2]![New_MRB] AS MRB,"
strSQL = strSQL & "[Forms]![AddForm2]![New_NAB] AS Airborne_Status,"
strSQL = strSQL & "[Forms]![AddForm2]![New_AS] AS Activity_Status,"
strSQL = strSQL & "[Forms]![AddForm2]![New_EAS] AS EA_Status,"
strSQL = strSQL & "[Forms]![AddForm2]![New_EACD] AS EA_Cert_Date,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Ref] AS Reference_Letter,"
strSQL = strSQL & "[Forms]![AddForm2]![New_Exc] AS Exclusions,"
strSQL = strSQL & "[Forms]![AddForm2]![New_EN] AS Exclusion_Notes;"
DoCmd****nSQL strSQL


The [Forms]![AddForm2]![New_?????] relate to textboxes which i input data into . The New_DT textbox relating to the date coloumn has a source of =Now() .


Not sure what to do....if you need any more info or not?


Many thanks guys

strSQL = strSQL & "[Forms]![AddForm2]![New_DT] AS Date

My guess would be the problem that you're using Date as a Field name! Date is a Reserved Word in Access, and RWs should really never be used as the names of anything in Access! It confuses the Access Gnomes no end, and sooner or later (and apparently sooner, in your case) will cause problems! So the first thing to do is change that name, It's possible that Access is balking at something else, but changing Date to something else still needs to be done!

Date is probably the absolute worse Reserved Word to be used in this manner, because it is the name of a commonly used Access Function, with the second worst offender being the use of Name as a name!

Linq ;0)>