Results 1 to 10 of 10
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Syntax Error on INSERT INTO statement


    Good Afternoon:

    I have tried multiple variations on this INSERT INTO statement, and keep getting a syntax error on the SQL portion.

    Code:
    Private Sub cmdSaveExit_Click()
    Dim strRecordSQL As String
    If IsNull(Me.tbxProductUPC) And IsNull(Me.tbxOrderNo) And IsNull(Me.cbxEmployee) = True Then
        MsgBox ("You must complete all fields.")
        Exit Sub
    Else
    End If
     
    strRecordSQL = "INSERT INTO WOTracking(Employee, UPC, OrderNo, DateTime) VALUES ('" & Me.cbxEmployee.Value & "','" & Me.tbxProductUPC.Value & "','" & Me.tbxOrderNo.Value & "','" & Me.dteDateTime.Value & "')"
    CurrentDb.Execute strRecordSQL
    
    
    End Sub
    I am sure it is something simple, but for the life of me cannot see it. Any help would be greatly appreciated.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are the values to be inserted all text datatype? Are quotes needed???

    You need:
    quotes around a text value
    nothing around a number
    # surrounding date values

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    All but dtDateTime are ShortText. Corrected dtDateTime as follows:

    Code:
    strRecordSQL = "INSERT INTO WOTracking (Employee, UPC, OrderNo, DateTime) VALUES ('" & Me.cbxEmployee.Value & "','" & Me.tbxProductUPC.Value & "','" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
    CurrentDb.Execute strRecordSQL
    Still throwing syntax error. Quotes are not needed, this is just how I typically write/reuse my previous code.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just did a mockup to see how the SQL was being rendered using debug.print and some local variables.

    Code:
    Sub tryInsertSyntax()
    Dim strrecordsql As String
    Dim empval As String: empval = "EmpVal"
    Dim upc As String: upc = "12345HF"
    Dim orderno As String: orderno = "abcdefG"
    Dim dt As Date: dt = #2/23/2019#
    'strrecordsql = "INSERT INTO WOTracking (Employee, UPC, OrderNo, DateTime) VALUES ('" & Me.cbxEmployee.Value & "','" & Me.tbxProductUPC.Value & "','" & Me.tbxOrderNo.Value & "',#" & dtDateTime & "#)"
    strrecordsql = "INSERT INTO WOTracking (Employee, UPC, OrderNo, DateTime) VALUES ('" & empval & "','" & upc & "','" & orderno & "',#" & dt & "#)"
    Debug.Print strrecordsql
    'CurrentDb.Execute strrecordsql
    End Sub
    Gives this
    Code:
    INSERT INTO WOTracking (Employee, UPC, OrderNo, DateTime) VALUES ('EmpVal','12345HF','abcdefG',#23-Feb-19#)
    
    which looks OK to me if all are short text except date (which is dateTime)
    (23-Feb-19) is because of my regional setting

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    I think I have found the problem. OrderNo is defined as short text, but it is also my primary key. Could that be the issue? Can the primary key be text?

    Code:
    strRecordSQL = "INSERT INTO WOTracking (Employee, UPC, OrderNo, DateTime) VALUES ('" & Me.cbxEmployee.Value & "','" & Me.tbxProductUPC.Value & "',' & Me.tbxOrderNo.Value & ',#" & dtDateTime & "')"
    I tried this modification and still syntax error.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes a PK can be text.
    Can you post a copy of your database -remove anything private first?
    Don't necessarily need a lot of data, just enough to do some "testing".

  7. #7
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    db Attached

    Attached is the db. Much of the code in there is still in progress.

    Database deleted at poster's request. Orange

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm? OrderNo is a number
    Click image for larger version. 

Name:	dccjr1.PNG 
Views:	17 
Size:	26.2 KB 
ID:	36830

    All tables should have a Primary Key

    Why 2 tables SCMaster Sc Master?

    I have modified your table field name from DateTime to Date_Time and revised the module that was giving the error.
    Updated database is in the zip file attached. I added 2 test records to WOTracking.

    Database deleted at poster's request. Orange

  9. #9
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thank you so much. I was trying it as a Number between replies. Everything is great. How do I mark this as Solved?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Syntax Error In Insert Into Statement
    By Eranka in forum Access
    Replies: 7
    Last Post: 06-02-2018, 03:48 AM
  2. Syntax error in INSERT INTO statement
    By darkwind in forum Programming
    Replies: 7
    Last Post: 05-09-2017, 01:43 AM
  3. Syntax Error In Insert Into Statement
    By ULMA in forum Access
    Replies: 32
    Last Post: 02-24-2015, 06:00 PM
  4. Syntax Error: Insert Into statement in VBA
    By Kimbertha in forum Programming
    Replies: 1
    Last Post: 07-24-2012, 05:02 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