Results 1 to 3 of 3
  1. #1
    amcintosh is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    6

    syntax Error insert into

    Hi all



    Having a bit of issue with the VBA code below, keeps throwing up a syntax error and I not sure on what's causing it?

    Code:
    SQL = "INSERT INTO ITEM-GradeBrand (ItemCode,GradeBrand,GBValue,GBDescr) " _
        & "VALUES ('" & IC & "','" & GB & "','" & GV & "','0');"
    The IC, GB and GV codes are originating from a string variable

    The full version of the Sub
    Code:
    Private Sub StockType_AfterUpdate()
    
    
    Dim SQL As String
    
    
    
    
    Me.GST_Applies = DLookup("GST", "LUP-ITEM-Stockgroup", "[Type]='" & Me.StockType & "'")
    Me.GRN_Item = DLookup("GRN", "LUP-ITEM-Stockgroup", "[Type]='" & Me.StockType & "'")
    
    
    Dim CMode As Integer, GB As String, GV As String, IC As String
    Dim db As Database
    
    
    Set db = Application.CurrentDb
    
    
    
    
    IC = Me.ItemCode
    
    
    CMode = 1
    
    
    Do While CMode < 10
    
    
    Select Case CMode
    
    
    Case 1
    GB = "Brand"
    GV = "0"
    
    
    Case 2
    GB = "Grade"
    GV = "0"
    
    
    Case 3
    GB = "Size"
    GV = "ND"
    
    
    Case 4
    'exit loop
    Exit Sub
    End Select
    
    
    SQL = "INSERT INTO ITEM-GradeBrand (ItemCode,GradeBrand,GBValue,GBDescr) " _
        & "VALUES ('" & IC & "','" & GB & "','" & GV & "','0');"
    
    
    DoCmd.RunSQL SQL
    
    
    CMode = CMode + 1
    
    
    Loop
    
    
    End Sub
    any help would be appreciated

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,736
    ALWAYS output problem sql to the immediate window and examine. If it looks OK, copy & paste into a new query and attempt switch to datasheet view and see what happens. My guess is that the problem is using special characters in object names. Access probably doesn't like it when you try to subtract one word from another: ITEM-GradeBrand but that's a guess because I'd never use such characters, nor start an object name with a number.
    Try [ITEM-GradeBrand]. If that's not it, try the test query. You could post the Debug.Print output from the immediate window as well.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    amcintosh is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    6
    Thanks for that.

    Putting the table name in the square brackets had resolved it

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

Similar Threads

  1. Syntax Error In Insert Into
    By Eranka in forum Access
    Replies: 5
    Last Post: 11-05-2019, 02:43 AM
  2. Replies: 4
    Last Post: 03-26-2019, 02:40 PM
  3. syntax error in insert into
    By rameshjctr in forum Forms
    Replies: 5
    Last Post: 02-05-2016, 05:41 AM
  4. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  5. INSERT INTO Syntax Error
    By eww in forum Programming
    Replies: 1
    Last Post: 04-06-2011, 10:28 AM

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 - Senior Forums