Error in INSERT INTO statement

[plarsen]

Hi

I have made a query in Access XP and want to use it as a docmd.RunSQL statement in VB, but i get an "Syntax error in INSERT INTO statement" error.

My sql statement look like this:

INSERT INTO dbo_tblReportNoWork ( ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno )
SELECT dbo_tblSerServiceReports.ReportNo, -1 AS [Level], tblReportNoWork.FirstCFMReportNo, tblReportNoWork.CFMCase, tblReportNoWork.ListMonth, tblReportNoWork.CFMItemno
FROM tblReportNoWork INNER JOIN dbo_tblSerServiceReports ON tblReportNoWork.ReportNo = dbo_tblSerServiceReports.RefToOldReport
WHERE (((tblReportNoWork.Level)=0));

I hope you can help me!

 

[SiJP]

The syntax looks good above and works fine in QBE...

What is your VBA code?

------------------------
Hit any User to continue

 

[plarsen]

Here is the complete function!

Public Function test()
Dim SQLstr, SQLstr1 As String
Dim i As Integer

DoCmd.SetWarnings False
i = 0
lm = -1
lp = 1
SQLstr = "INSERT INTO tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
& "SELECT tblReportNoLevel0.ReportNumber AS ReportNo, 0 AS [Level], tblReportNoLevel0.ReportNumber, tblReportNoLevel0.CFMCase, tblReportNoLevel0.ListMonth, tblReportNoLevel0.ITNBR " _
& "FROM tblReportNoLevel0"
DoCmd.RunSQL SQLstr
Debug.Print SQLstr
Do Until i = 9
SQLstr = "INSERT INTO dbo_tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
& "SELECT dbo_tblSerServiceReports.ReportNo, -1 AS [Level], tblReportNoWork.FirstCFMReportNo, tblReportNoWork.CFMCase, tblReportNoWork.ListMonth, tblReportNoWork.CFMItemno, " _
& "FROM tblReportNoWork INNER JOIN dbo_tblSerServiceReports ON tblReportNoWork.ReportNo = dbo_tblSerServiceReports.RefToOldReport " _
& "WHERE (((tblReportNoWork.Level)=" & i & "))"
SQLstr1 = "INSERT INTO dbo_tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
& "SELECT dbo_tblSerServiceReports.ReportNo, " & lp & " AS [Level], tblReportNoWork.FirstCFMReportNo, tblReportNoWork.CFMCase, tblReportNoWork.ListMonth, tblReportNoWork.CFMItemno " _
& "FROM tblReportNoWork INNER JOIN dbo_tblSerServiceReports ON tblReportNoWork.ReportNo = dbo_tblSerServiceReports.RefToOldReport " _
& "WHERE tblReportNoWork.Level=" & i
Debug.Print SQLstr & Chr(13) & SQLstr1
DoCmd.RunSQL SQLstr 'This line returns an error!
DoCmd.RunSQL SQLstr1
i = i + 1
lm = lm - 1
lp = lp + 1
Loop
DoCmd.SetWarnings True
End Function

/Peter 'knirke' Larsen

 

[SiJP]

You've a comma (,) before your FROM statement when building SQLstr... try this:

Public Function test()
    Dim SQLstr, SQLstr1 As String
    Dim i As Integer
    
    DoCmd.SetWarnings False
    i = 0
    lm = -1
    lp = 1
    SQLstr = "INSERT INTO tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
        & "SELECT tblReportNoLevel0.ReportNumber AS ReportNo, 0 AS [Level], tblReportNoLevel0.ReportNumber, tblReportNoLevel0.CFMCase, tblReportNoLevel0.ListMonth, tblReportNoLevel0.ITNBR " _
        & "FROM tblReportNoLevel0"
    DoCmd.RunSQL SQLstr
    Debug.Print SQLstr
    Do Until i = 9

'// SiJP: Removed comma just before the FROM statement of this string bulid
        SQLstr = "INSERT INTO dbo_tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
            & "SELECT dbo_tblSerServiceReports.ReportNo, -1 AS [Level], tblReportNoWork.FirstCFMReportNo, tblReportNoWork.CFMCase, tblReportNoWork.ListMonth, tblReportNoWork.CFMItemno " _
            & "FROM tblReportNoWork INNER JOIN dbo_tblSerServiceReports ON tblReportNoWork.ReportNo = dbo_tblSerServiceReports.RefToOldReport " _
            & "WHERE (((tblReportNoWork.Level)=" & i & "))"

        SQLstr1 = "INSERT INTO dbo_tblReportNoWork (ReportNo, [Level], FirstCFMReportNo, CFMCase, ListMonth, CFMItemno) " _
            & "SELECT dbo_tblSerServiceReports.ReportNo, " & lp & " AS [Level], tblReportNoWork.FirstCFMReportNo, tblReportNoWork.CFMCase, tblReportNoWork.ListMonth, tblReportNoWork.CFMItemno " _
            & "FROM tblReportNoWork INNER JOIN dbo_tblSerServiceReports ON tblReportNoWork.ReportNo = dbo_tblSerServiceReports.RefToOldReport " _
            & "WHERE tblReportNoWork.Level=" & i
        Debug.Print SQLstr & Chr(13) & SQLstr1
        DoCmd.RunSQL SQLstr              'This line returns an error!
        DoCmd.RunSQL SQLstr1
        i = i + 1
        lm = lm - 1
        lp = lp + 1
    Loop
    DoCmd.SetWarnings True
End Function

------------------------
Hit any User to continue

 

[plarsen]

Thanks SiJP

Now it works fine with another ajustment (wrong table name)!

/
Peter 'knirke' Larsen

http://knirke.dk?lang=engl

 

[SiJP]

w00t!

It's always worth checking the debug window using ?sqlstr and pressing Enter, then dumping the output into the QBE and see what errors you get

------------------------
Hit any User to continue