[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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

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.
0
jammin140900
Asked:
jammin140900
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
cyberkiwiCommented:
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
0
 
cyberkiwiCommented:
2nd part is to check that

Me!WebsiteID

does have a value. If it doesn't, you get an incomplete SQL statement.
0
 
Rey Obrero (Capricorn1)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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Paul JacksonCommented:
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 & "'"
0
 
cyberkiwiCommented:
There is nothing wrong with vbCrLf in place of spaces to separate lines/blocks.  That makes no functional difference and is only semantics.
0
 
jammin140900Author 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
0
 
cyberkiwiCommented:
You cannot do one RunSQL with both.

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])"
    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
0
 
Rey Obrero (Capricorn1)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
0
 
Rey Obrero (Capricorn1)Commented:
sorry
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"
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID

    DoCmd.RunSQL strInsert
0
 
Rey Obrero (Capricorn1)Commented:
again sorry the second one will not work

what is th relation of table tblASSitesToSubmitTO to table tblWebsite?
0
 
jammin140900Author Commented:
Brilliant guys. Thanks for such a quick response too. I've also learned how to do this. :)
0
 
cyberkiwiCommented:
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
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now