We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

My Insert Into Statement is producing a "Syntax Error in INSERT INTO Statement"

Medium Priority
304 Views
Last Modified: 2012-06-22
Hi there,

My code seems to be producing a Run Time Error 3134.

The code is trying to get a 'WebsiteID' from the tblWebsite, to insert the current website ID being currently worked on, into a table tblASGeneralWebsiteReg.

Here's the code I have:

Dim strInsert1 As String

strInsert1 = "Insert into tblASGeneralWebsiteReg ([WebsiteID])" & vbCrLf
    strInsert = strInsert & "select tblWebsite.WebsiteID as WebsiteID" & vbCrLf
    strInsert = strInsert & "from tblWebsite" & vbCrLf
    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID

 DoCmd.RunSQL strInsert1

Any ideas please? Oh, I have a similar strInsert statement before this with almost exactly the same thing that works. That inserts the WebsiteID and another field into another table. I've turned that off for the moment to try getting this statement above to work.
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
to begin with, drop the alias

strInsert1 = "Insert into tblASGeneralWebsiteReg ([WebsiteID])" & vbCrLf
    strInsert = strInsert & "select tblWebsite.WebsiteID" & vbCrLf
    strInsert = strInsert & "from tblWebsite" & vbCrLf
    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
2nd part is to check that

Me!WebsiteID

does have a value. If it doesn't, you get an incomplete SQL statement.
CERTIFIED EXPERT
Top Expert 2016

Commented:
you have to provide spaces,  strInsert = strInsert & " select
--------------------------------------------------------^ space
and no need for vbcrlf
and here i am only using the variable string strInsert

strInsert = "Insert into tblASGeneralWebsiteReg ([WebsiteID])"
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID"
    strInsert = strInsert & " from tblWebsite"  
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID

     debug.print strInsert

      DoCmd.RunSQL strInsert

so that you will see the resulting sql string is correctly formatted, have it printed with

    debug.print strInsert

Paul JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011

Commented:
probably need to put some single quotes around Me!WebsiteID as well:

strInsert1 = "Insert into tblASGeneralWebsiteReg ([WebsiteID])" & 
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID"
    strInsert = strInsert & " from tblWebsite"
    strInsert = strInsert & " where tblWebsite.WebsiteID = '" & Me!WebsiteID & "'"
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
There is nothing wrong with vbCrLf in place of spaces to separate lines/blocks.  That makes no functional difference and is only semantics.

Author

Commented:
Thanks guys.

I changed the original one, but now when added with the other, the tblASGeneralWebsiteReg works correctly, but the tblASWebsiteSite doesn't seem to work at all.

Any ideas please?


Private Sub Command19_Click()
Me.Refresh

 Dim strInsert As String
 
strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)" & vbCrLf
    strInsert = strInsert & "select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID" & vbCrLf
    strInsert = strInsert & "from tblWebsite, tblASSitesToSubmitTo" & vbCrLf
    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID

    strInsert = "Insert into tblASGeneralWebsiteReg ([WebsiteID])"
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID"
    strInsert = strInsert & " from tblWebsite"
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID
   
  ''  DoCmd.SetWarnings False
    DoCmd.RunSQL strInsert
   '' DoCmd.SetWarnings True
   
End Sub
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
use this, you have to do it separately (run the insert statement separately)

    strInsert = "Insert into tblASGeneralWebsiteReg ([WebsiteID])"
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID"
    strInsert = strInsert & " from tblWebsite"
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID
   
  ''  DoCmd.SetWarnings False
    DoCmd.RunSQL strInsert
   '' DoCmd.SetWarnings True

strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)"
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID"
    strInsert = strInsert & " from tblWebsite, tblASSitesToSubmitTo"
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID

    DoCmd.RunSQL strInsert
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2016

Commented:
again sorry the second one will not work

what is th relation of table tblASSitesToSubmitTO to table tblWebsite?

Author

Commented:
Brilliant guys. Thanks for such a quick response too. I've also learned how to do this. :)
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You were only missing RunSQL the in between.  I think you mangled your code following capricorn1's suggestion.
Your original probably had vbCrLf's in the 2nd part

Private Sub Command19_Click()
Me.Refresh

 Dim strInsert As String
 
strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)" & vbCrLf
    strInsert = strInsert & "select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID" & vbCrLf
    strInsert = strInsert & "from tblWebsite, tblASSitesToSubmitTo" & vbCrLf
    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID

    DoCmd.RunSQL strInsert

    strInsert = "Insert into tblASGeneralWebsiteReg ([WebsiteID])" & vbCrLf
    strInsert = strInsert & "select tblWebsite.WebsiteID as WebsiteID" & vbCrLf
    strInsert = strInsert & "from tblWebsite" & vbCrLf
    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
   
  ''  DoCmd.SetWarnings False
    DoCmd.RunSQL strInsert
   '' DoCmd.SetWarnings True
   
End Sub
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.