Results 1 to 8 of 8
  1. #1
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47

    Syntax error in INSERT INTO statement

    I've got a set of code designed to insert some data into a table, [tblTC_1_2_report_detail], from a query, [sql3]. There will be multiple records in [tblTC_1_2_report_detail] from this single query, but not too many- no more than 20, and more often, 5-10. The fields rs("OriType"), rs("QCType"), and rs("void_comment") may either be a string or null. I'm not sure how to handle that in a query like this, which I think is the problem. Then again, it's a long string, so there could be another mistake I've overlooked. In any event, I'm getting run-time error 3134, syntax error in INSERT INTO statement when I attempt to run the docmd.runSQL line of this code. My current (relevant) code as it stands:



    Code:
    Dim a as integer
    Dim rs as DAO.recordset
    Dim sql3 as string
    Dim sql4 as string
    
    a = DLookup("[tc12reportid]", "[tblTC_1_2_report]", "[report_date] =#" & Now() & "#")
    sql3 = "SELECT * from qryTC_1_2_Report_Ori_Side union Select * from qryTC_1_2_report_QC_Side"
    
    
    
    Set rs = CurrentDb.OpenRecordset(sql3)
            If Not (rs.EOF And rs.BOF) Then
                rs.MoveFirst
                Do Until rs.EOF = True
                     sql4 = "INSERT INTO tblTC_1_2_report_detail (tc12reportID, [Labjob], [Sample], sampleID], [OriginalAnalyst], [QCAnalyst], [atID], [OriType], [QCType], [OriCount], [QCCount], [QCID], [ID_Criteria], [Major_Criteria], [Minor_Criteria], [Void], [Void_Comment], [Criteria_Status]) SELECT " & a & ", '" &  rs("Labjob") & "', '" rs("sample") & "', " & rs("sampleID") & ", " & rs("OriginalAnalyst") & ", '" & rs("QCAnalyst") & "', " & rs("AnalysisID") & ", '" & rs("OriType") & "', '" & rs("QCType") & "', " & rs("OriCount") & ", " & rs("QCCount") & ", " & rs("QCID") & ", " & rs("ID_Criteria") & ", " & rs("Major_Criteria") & ", " & rs("Minor_Criteria") & ", " & rs("void") & ", '" & rs("void_comment") & "', '" & rs("criteria_status") & "';"
                     DoCmd.RunSQL sql4
                    rs.MoveNext
                Loop
            Else
                MsgBox "There are no records in the recordset."
            End If


    I realize it's a bit of a bear, but the data types of these fields are, in order:
    Integer, string, string, integer, integer, string, integer, string/null, string/null, integer, integer, integer, integer, double, double, Yes/No, string/null, string

    If you would prefer this information in a different format, or any additional information, let me know. I appreciate any help in advance!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    put debug.print sql4 befor your runsql line then copy and paste the result displaying in the immediate window into a new query and run it. No idea what error 3134 is but it should tell you more clearly where the problem lies - note the message detail

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Missing beginning brackeet:

    sampleID]

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    I put your query into text editor and designed it there, so syntax problems are more visible, and added comments on faulty syntax, preceeded with --.

    sql14=
    "INSERT INTO tblTC_1_2_report_detail
    (
    tc12reportID,
    [Labjob],
    [Sample],
    sampleID], -- missing [
    [OriginalAnalyst],
    [QCAnalyst],
    [atID],
    [OriType],
    [QCType],
    [OriCount],
    [QCCount],
    [QCID],
    [ID_Criteria],
    [Major_Criteria],
    [Minor_Criteria],
    [Void], [Void_Comment],
    [Criteria_Status]
    &nbsp
    SELECT
    " & a & ",
    '" & rs("Labjob") & "',
    '" rs("sample") & "', -- '" & rs("sample") & "',
    " & rs("sampleID") & ",
    " & rs("OriginalAnalyst") & ",
    '" & rs("QCAnalyst") & "',
    " & rs("AnalysisID") & ",
    '" & rs("OriType") & "',
    '" & rs("QCType") & "',
    " & rs("OriCount") & ",
    " & rs("QCCount") & ",
    " & rs("QCID") & ",
    " & rs("ID_Criteria") & ",
    " & rs("Major_Criteria") & ",
    " & rs("Minor_Criteria") & ",
    " & rs("void") & ",
    '" & rs("void_comment") & "',
    '" & rs("criteria_status") & "';" -- OK scrap the rest! I forgot you use recordset here. I'm not sure about recordset query syntax at moment, I seldom use it. -- remove semicolon, as you cant stop query string here.
    -- Scrap it! -- FROM ...

    --Scrap it! The main problem - your query string is unfinished. You can't use SELECT without FROM! And there are minor syntax errors for good measure too!

  5. #5
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Quote Originally Posted by Bulzie View Post
    Missing beginning brackeet:

    sampleID]
    Good catch, corrected.




    Quote Originally Posted by Ajax View Post
    put debug.print sql4 befor your runsql line then copy and paste the result displaying in the immediate window into a new query and run it. No idea what error 3134 is but it should tell you more clearly where the problem lies - note the message detail

    This does not seem to do anything different. I get the exact same error message as before. My code reads:

    Code:
    ...
                    sql4 = "INSERT INTO tblTC_1_2_report_detail (tc12reportID, [Labjob], [Sample], [sampleID], [OriginalAnalyst], [QCAnalyst], [atID], [OriType], [QCType], [OriCount], [QCCount], [QCID], [ID_Criteria], [Major_Criteria], [Minor_Criteria], [Void], [Void_Comment], [Criteria_Status]) SELECT " & a & ", '" & rs("Labjob") & "', '" & rs("sample") & "', " & rs("sampleID") & ", " & rs("OriginalAnalyst") & ", '" & rs("QCAnalyst") & "', " & rs("AnalysisID") & ", " & rs("OriType") & ", '" & rs("QCType") & "', " & rs("OriCount") & ", " & rs("QCCount") & ", " & rs("QCID") & ", " & rs("ID_Criteria") & ", " & rs("Major_Criteria") & ", " & rs("Minor_Criteria") & ", " & rs("void") & ", " & rs("void_comment") & ", '" & rs("criteria_status") & "';"
                    Debug.Print sql4
                    DoCmd.RunSQL sql4

    Quote Originally Posted by ArviLaanemets View Post
    Hi

    I put your query into text editor and designed it there, so syntax problems are more visible, and added comments on faulty syntax, preceeded with --.

    sql14=
    "INSERT INTO tblTC_1_2_report_detail
    (
    tc12reportID,
    [Labjob],
    [Sample],
    sampleID], -- missing [
    [OriginalAnalyst],
    [QCAnalyst],
    [atID],
    [OriType],
    [QCType],
    [OriCount],
    [QCCount],
    [QCID],
    [ID_Criteria],
    [Major_Criteria],
    [Minor_Criteria],
    [Void], [Void_Comment],
    [Criteria_Status]
    &nbsp
    SELECT
    " & a & ",
    '" & rs("Labjob") & "',
    '" rs("sample") & "', -- '" & rs("sample") & "',
    " & rs("sampleID") & ",
    " & rs("OriginalAnalyst") & ",
    '" & rs("QCAnalyst") & "',
    " & rs("AnalysisID") & ",
    '" & rs("OriType") & "',
    '" & rs("QCType") & "',
    " & rs("OriCount") & ",
    " & rs("QCCount") & ",
    " & rs("QCID") & ",
    " & rs("ID_Criteria") & ",
    " & rs("Major_Criteria") & ",
    " & rs("Minor_Criteria") & ",
    " & rs("void") & ",
    '" & rs("void_comment") & "',
    '" & rs("criteria_status") & "';" -- OK scrap the rest! I forgot you use recordset here. I'm not sure about recordset query syntax at moment, I seldom use it. -- remove semicolon, as you cant stop query string here.
    -- Scrap it! -- FROM ...

    --Scrap it! The main problem - your query string is unfinished. You can't use SELECT without FROM! And there are minor syntax errors for good measure too!

    I believe you are incorrect on that last point. You don't need FROM if you're selecting constants as far as the SQL is concerned. While those are all variables in VBA, once they're fed through to SQL, they're constants, so you don't need a FROM.

    For example, a = 3 and rs("Labjob") = 17-1234 in this case, so SQL gets "SELECT 3, '17-1234',...". It doesn't need a FROM statement to know what those are, because they're just constants

    I am still getting the same error, though. Any other ideas on where my error might be?

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Why don't you do it another way to get to the heart of it. Create another recordset rs2 , add the following code

    Dim a as integer
    Dim rs, rs2 as DAO.recordset
    Dim sql3 as string
    Dim sql4 as string

    a = DLookup("[tc12reportid]", "[tblTC_1_2_report]", "[report_date] =#" & Now() & "#")
    sql3 = "SELECT * from qryTC_1_2_Report_Ori_Side union Select * from qryTC_1_2_report_QC_Side"



    Set rs = CurrentDb.OpenRecordset(sql3)
    Set rs2 = CurrentDb.OpenRecordset("tblTC_1_2_report_detail")

    If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    rs2.AddNew
    rs2!LabJob = rs!LabJob
    rs2!Sample = rs!Sample
    .. (add all the other fields
    rs2.Update
    rs.MoveNext
    Loop
    Else
    MsgBox "There are no records in the recordset."
    End If

    Put a breakpoint and step through the code to see which lines are having issues.

  7. #7
    darkwind is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2017
    Location
    Central U.S.
    Posts
    47
    Quote Originally Posted by Bulzie View Post
    Why don't you do it another way to get to the heart of it. Create another recordset rs2 , add the following code

    Dim a as integer
    Dim rs, rs2 as DAO.recordset
    Dim sql3 as string
    Dim sql4 as string

    a = DLookup("[tc12reportid]", "[tblTC_1_2_report]", "[report_date] =#" & Now() & "#")
    sql3 = "SELECT * from qryTC_1_2_Report_Ori_Side union Select * from qryTC_1_2_report_QC_Side"



    Set rs = CurrentDb.OpenRecordset(sql3)
    Set rs2 = CurrentDb.OpenRecordset("tblTC_1_2_report_detail")

    If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    rs2.AddNew
    rs2!LabJob = rs!LabJob
    rs2!Sample = rs!Sample
    .. (add all the other fields
    rs2.Update
    rs.MoveNext
    Loop
    Else
    MsgBox "There are no records in the recordset."
    End If

    Put a breakpoint and step through the code to see which lines are having issues.
    Very clever way to look for the problem. I wish I'd seen that post earlier

    Anyway, I did eventually end up finding a solution...

    Code:
    sql4 = "INSERT INTO tblTC_1_2_report_detail (tc12reportID, [Labjob], [Sample], [sampleID], [OriginalAnalyst], [QCAnalyst], [atID], [OriType], [QCType], [OriCount], [QCCount], [QCID], [ID_Criteria], [Major_Criteria], [Minor_Criteria], [Void], [Void_Comment], [Criteria_Status]) Values (" & a & ", '" & rs("Labjob") & "', '" & rs("sample") & "', '" & rs("sampleID") & "', '" & rs("OriginalAnalyst") & "', '" & rs("QCAnalyst") & "', '" & rs("AnalysisID") & "', '" & rs("OriType") & "', '" & rs("QCType") & "', '" & rs("OriCount") & "', '" & rs("QCCount") & "', '" & rs("QCID") & "', '" & rs("ID_Criteria") & "', '" & rs("Major_Criteria") & "', '" & rs("Minor_Criteria") & "', " & rs("void") & ", '" & rs("void_comment") & "', '" & rs("criteria_status") & "');"
    Not that pretty, but it does work and will fulfill my purposes, so good enough.

    Thanks for the help, guys!

  8. #8
    blakebaker008 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2017
    Posts
    3
    Thanks for the help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Syntax Error In Insert Into Statement
    By ULMA in forum Access
    Replies: 32
    Last Post: 02-24-2015, 06:00 PM
  2. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  3. Syntax Error: Insert Into statement in VBA
    By Kimbertha in forum Programming
    Replies: 1
    Last Post: 07-24-2012, 05:02 PM
  4. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums