Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unhappy Unanswered: Error insert into variables

    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

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    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)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts