Results 1 to 11 of 11

Insert SQL not running

  1. #1
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87

    Insert SQL not running

    I have an INSERT SQL statement that is not inserting the record into the table. It is not throwing any error, but when I open the table nothing is there. I have double checked the table name and order of my fields and values. Here is the insert section:




    Code:
    Dim dteDateTime As Date
    
    cenSQL = "','"
    endSQL = "');"
    dteDateTime = Now()
    
    
    RecordSQL = ""
            RecordSQL = "INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)" _
                & "VALUES('" & Me.tbxProductUPC.Value & cenSQL & Me.cbxDefectCat.Value & cenSQL & Me.cbxEmployee.Value & cenSQL & Me.cbxMarketplace.Value & cenSQL & Me.cbxDefect.Value & cenSQL & Me.tbxCustName.Value & cenSQL & Me.tbxOrderNum.Value & cenSQL & Me.cbxStatus.Value & cenSQL & Me.tbxCallNotes.Value & cenSQL & Me.tbxDefectNotes.Value & cenSQL & Me.cbxInitiatedBy.Value & cenSQL & dteDateTime & endSQL
            CurrentDb.Execute RecordSQL

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    Add a debug.print recordSQL before the currentdb.execute and comment the currentdb.execute line to see the rendered SQL

  3. #3
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    The rendered line is:

    INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)VALUES('5555555','Customer','Chris Freeman','Amazon','Broken/Damaged','Bob Smith','66666666','Replacement Provided','None','None','NA','1/30/2019 9:12:21 AM');

    All fields and values are correct

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    All of your fields are text datatype?
    Can you show us the design of table DefectEvents?

  5. #5
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    2 are not, Date_Time and OrderNumber

    Click image for larger version. 

Name:	Screenshot (1).png 
Views:	15 
Size:	13.0 KB 
ID:	37180

    I will look for how to change the format for these.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,579
    The date field should have # for the delimiters instead of ', the number field shouldn't have any delimiters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    OK, I think I am close to correcting this.

    Code:
    RecordSQL = "INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)" _
                & "VALUES(" & Me.tbxProductUPC.Value & "," & Me.cbxDefectCat.Value & "," & Me.cbxEmployee.Value & "," & Me.cbxMarketplace.Value & "," & Me.cbxDefect.Value & "," & Me.tbxCustName.Value & ", & Me.tbxOrderNum.Value & ," & Me.cbxStatus.Value & "," & Me.tbxCallNotes.Value & "," & Me.tbxDefectNotes.Value & "," & Me.cbxInitiatedBy.Value & ",#" & dteDateTime & "#');"
    My debug yields :

    Code:
    INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)VALUES(444555,Customer,Chris Freeman,Amazon,Did Not Receive,Bob Smith, & Me.tbxOrderNum.Value & ,Replacement Provided,None,None,,#1/31/2019 8:43:06 AM#');
    But I am getting a Syntax Error (missing operator in query expression 'Chris Freeman'. It is putting the name of the control tbxOrderNum in the statement. I seem to be missing a delimiter.
    Any advice?

  8. #8
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,041
    You still need the delimiters for the text fields. Also add a space between Date_Time) VALUES (

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    As davegri said, you need:

    -quote delimiters on a text value
    -hashtag/octothorpe/# on date values
    -nothing for number values

    Try this (render the sql with debut.print)
    Code:
    RecordSQL = "INSERT INTO DefectEvents (UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, CallNotes, DefectNotes, CallInit, Date_Time)" _
                & "VALUES('" & Me.tbxProductUPC & "','" & Me.cbxDefectCat & "','" & Me.cbxEmployee & "'," & Me.cbxMarketplace & "','" & Me.cbxDefect & "','" & Me.tbxCustName & "'," & Me.tbxOrderNum & ",'" & Me.cbxStatus & "','" & Me.tbxCallNotes & "',"' & Me.tbxDefectNotes & "','" & Me.cbxInitiatedBy & "',#" & dteDateTime & "#);"

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,579
    Maybe this will help understand:

    http://www.baldyweb.com/BuildSQL.htm
    Last edited by pbaldy; 01-31-2019 at 02:11 PM. Reason: fix spelling goof
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    dccjr3927 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    87
    Thanks to everyone for the help. pbaldy, I will spend some time with this, hopefully decreasing the sizable gaps in my knowledge. Thanks again to everyone.

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

Similar Threads

  1. Replies: 9
    Last Post: 05-30-2017, 01:19 PM
  2. Replies: 1
    Last Post: 08-29-2016, 07:50 PM
  3. Replies: 6
    Last Post: 09-14-2015, 06:05 PM
  4. Replies: 6
    Last Post: 07-01-2015, 10:56 AM
  5. Replies: 3
    Last Post: 05-27-2015, 01:04 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
  •  
Tech Forums: Microsoft Office Forums