Results 1 to 9 of 9
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    CurrentDb.Execute INSERT syntax error 3134

    I don't see the cause of the error.



    Screenshot of the error in Debug and the current value of strSQL in the IW:
    Click image for larger version. 

Name:	Syntax.jpg 
Views:	13 
Size:	50.8 KB 
ID:	23116

    Screenshot of table:
    Click image for larger version. 

Name:	tblRegister.jpg 
Views:	13 
Size:	30.5 KB 
ID:	23117

    And, the code: (No error code yet)
    Code:
    Private Sub cmdSave_Click()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' Fields TDate, TTypeID, TType, Description and Memo are a part of all records to
    ' be saved.  The fields and values that need to be added are appended depending of
    ' their current values.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim strSQL As String
    Dim strValues As String
    
    strSQL = "INSERT INTO tblRegistry(TDate, TTypeID, TType, Description, TMemo"
    strValues = " VALUES(""" & tbNewDate & """," & intNewTTypeID
    strValues = strValues & ",""" & tbNewTType & """"
    strValues = strValues & ",""" & tbNewDescription & """"
    strValues = strValues & ",""" & tbNewMemo & """"
    
        If intNewCatID > 0 Then
            strSQL = strSQL & ", CatID"
            strValues = strValues & "," & intNewCatID
        End If
      
        If intNewSubCatID > 0 Then
            strSQL = strSQL & ", SubCatID"
            strValues = strValues & "," & intNewSubCatID
        End If
    
        If intNewAcctID > 0 Then
            strSQL = strSQL & ", AcctID"
            strValues = strValues & "," & intNewAcctID
        End If
        
        If Not IsNull(tbNewDebit) Then
            strSQL = strSQL & ", Debit"
            strValues = strValues & "," & tbNewDebit
        End If
         
        If Not IsNull(tbNewCredit) Then
            strSQL = strSQL & ", Credit"
            strValues = strValues & "," & tbNewCredit
        End If
        
    strSQL = strSQL & ")"
    strValues = strValues & ")"
    
    Debug.Print strSQL & strValues
    
    CurrentDb.Execute strSQL

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is TDate a date/time type field? If yes, then the value needs # delimiter, not " mark.

    VALUES(#" & tbNewDate & "#,"

    I prefer to use apostrophe instead of doubled quote marks for text delimiter.

    strValues = strValues & ",'" & tbNewTType & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    This fails as well:
    INSERT INTO tblRegistry(TDate, TTypeID, TType, Description, TMemo, AcctID, Debit) VALUES(#12/19/2015#,10,"Xfr-Out","Transfer Money Out of This Account","",13,1000)

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    The updated code:
    Code:
    strSQL = "INSERT INTO tblRegistry(TDate, TTypeID, TType, Description, TMemo"
    strValues = " VALUES(#" & tbNewDate & "#," & intNewTTypeID
    strValues = strValues & ",'" & tbNewTType & "'"
    strValues = strValues & ",'" & tbNewDescription & "'"
    strValues = strValues & ",'" & tbNewMemo & "'"
    
        If intNewCatID > 0 Then
            strSQL = strSQL & ", CatID"
            strValues = strValues & "," & intNewCatID
        End If
      
        If intNewSubCatID > 0 Then
            strSQL = strSQL & ", SubCatID"
            strValues = strValues & "," & intNewSubCatID
        End If
    
        If intNewAcctID > 0 Then
            strSQL = strSQL & ", AcctID"
            strValues = strValues & "," & intNewAcctID
        End If
        
        If Not IsNull(tbNewDebit) Then
            strSQL = strSQL & ", Debit"
            strValues = strValues & "," & tbNewDebit
        End If
         
        If Not IsNull(tbNewCredit) Then
            strSQL = strSQL & ", Credit"
            strValues = strValues & "," & tbNewCredit
        End If
        
    strSQL = strSQL & ")"
    strValues = strValues & ")"
    And with the same error:
    INSERT INTO tblRegistry(TDate, TTypeID, TType, Description, TMemo, Debit) VALUES(#12/19/2015#,0,'Xfr-Out','Transfer Money Out of This Account','',1000)

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is the exact error message? I don't see anything else wrong with the SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Click image for larger version. 

Name:	3134Error.jpg 
Views:	11 
Size:	29.1 KB 
ID:	23119

    As an aside, you'll notice that one of the fields is entitled "TMemo". When the underlying table was created, that field name was "Memo", which set off alarms in certain contexts. When that occurred, I recognized the issue and changed the field name in the table to "TMemo", which it should have been from the start. Anyway, changing that name necessitated updating the RecordSource query as well as one of the text box's bonding. In thinking that all of that might be at the root of the SQL problem, I eliminated "TMemo" from the field's list and the corresponding Values list. That made no difference to the 3134 error.

    What's troubling now is that I DO NOT see "TMemo" in the object browser but rather "Memo" remains. Could that be causing the 3134 error even though I excluded that field insertion in one of my tests?

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    How embarrassing! The assignment statement "strSQL = strSQL & strValues" was missing from the code. So, while there was a debug.print of "strSQL & strValues" that led me to believe all was okay, the "CurrentDb.Execute strSQL" WAS NOT getting the "Values" string for lacking to have combined the strings.

    Sorry I wasted everyone's time.

    Code:
    strSQL = strSQL & ")"
    strValues = strValues & ")"
    
    strSQL = strSQL & strValues
    
    CurrentDb.Execute strSQL

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I just saw that also.

    Not to worry.... been there, done that, got the bruises to show it!
    Probably will do it again.....

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ooops, I should have seen that, too! Was so focused on the SQL string, didn't read the whole procedure in detail.

    But wasn't a waste. The string did need fixing. Glad to help and that all working now.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. CurrentDB.Execute insert working just once
    By krausr79 in forum Access
    Replies: 4
    Last Post: 10-07-2015, 12:04 PM
  2. Error in CurrentDb.Execute
    By Stefan Moser in forum Access
    Replies: 5
    Last Post: 04-17-2015, 01:38 PM
  3. CurrentDB.Execute Insert Into with date variable
    By ketbdnetbp in forum Programming
    Replies: 2
    Last Post: 04-15-2015, 02:44 PM
  4. Syntax error 3134 for Add function
    By gaker10 in forum Programming
    Replies: 9
    Last Post: 06-16-2014, 12:06 PM
  5. Replies: 12
    Last Post: 01-24-2014, 02:18 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