Run-Time Error 3134: Syntax error in INSERT INTO statement

[alfiajamel]

I cannot figure out what is wrong with this code. Please help!!

CurrentDb.Execute "INSERT INTO tblEntry (CreditorID, CategoryID, DueDate, BillDesc, BillAmount, BillNotes, PaymentAmount, PaymentDate, PaymentMethod, CheckNumber, PaymentNotes, TotalDue) " & _
"VALUES (" & cboCreditors.Column(0) & ", " & _
cboCategory.Column(0) & ", " & _
Format(txtDueDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtBillDesc & Chr$(34) & ", " & _
txtBillAmount & ", " & _
Chr$(34) & txtBillNotes & Chr$(34) & ", " & _
txtPaymentAmount & ", " & _
Format(txtPaymentDate, "\#yyyy\-mm\-dd\#") & ", " & _
cboPaymentMethod.Column(0) & ", " & _
txtCheckNumber & ", " & _
Chr$(34) & txtPaymentNotes & Chr$(34) & ", " & _
txtTotalDue & ")

 

[MajP]

You are trying to swallow the elephant in one bite. I would not waste my time trying to find an error in that code. Do yourself a favor and write some code that is modularized and something you could error check. Use common functions to save code and reuse common procedures.

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function SQLStr(varStr As Variant) As String
  SQLStr = "'" & varStr & "'"
End Function

Public Sub testSql()
 Dim strSql As String
 Dim creditorID As Long
 Dim categoryID As Long
 Dim dueDate As String
 Dim billDesc As String
 Dim billAmount As Currency
 Dim billNotes As String
 Dim paymentAmount As Currency
 Dim paymentDate As String
 Dim PaymentMethod As Long
 Dim checkNumber As Long
 Dim paymentNotes As String
 Dim totalDue As Currency
 
 creditorID = cboCreditors.Column(0)
 categoryID = cboCategory.Column(0)
 dueDate = SQLDate(txtDueDate)
 bilDesc = SQLStr(txtBillDesc)
 billAmount = Nz(txtBillAmount, 0)
 billNotes = SQLStr(txtBillNotes)
 paymentAmount = Nz(txtPaymentAmount, 0)
 paymentDate = SQLDate(txtPaymentDate)
 PaymentMethod = cboPaymentMethod.Column(0)
 checkNumber = checkNumber
 paymentNotes = SQLStr(txtPaymentNotes)
 totalDue = Nz(txtTotalDue, 0)
 
 strSql = "INSERT INTO tblEntry (CreditorID, CategoryID, DueDate, BillDesc, BillAmount, BillNotes, PaymentAmount, PaymentDate, PaymentMethod, CheckNumber, PaymentNotes, TotalDue) "
 strSql = strSql & "VALUES (" & creditorID & "," & categoryID & "," & dueDate & "," & billDesc & "," & billAmount & "," & billNotes & "," & paymentAmount & "," & paymentDate & "," & PaymentMethod & "," & checkNumber & "," & paymentNotes & "," & totalDue & ")"
 Debug.Print strSql
 'verify all is good
 CurrentDb.Execute strSql
End Sub

When you view the output of the debug, if you are still having issues post the resolved string.

 

[MajP]

one mistake I see in what I wrote, should be
checkNumber = txtCheckNumber

but all of these should be wrapped in a nz function. Probably better:

dim checkNumber as variant
checkNumber = nz(txtCheckNumber,"NULL")

Same for the other numeric values.
The currency values are handled by nz(,0)
The string values are handled by an empty string.

 

[alfiajamel]

Hi! Thank you so much!! Where do I put this code?

 

[alfiajamel]

Hi,

If I posted it correctly, here is the message I get:

Microsoft Visual Basic Run-Time error 3078’:
The Microsoft office Access database engine cannot find the input table or query ”. Make sure it exists and that its name is spelled correctty.
End

I pasted the code you gave me, with the corrections you pointed out, as a function procedure. Again, I cannot begin to tell you how much I appreciate your help!

 

[MajP]

Do you have a "tblEntry"?
Can you paste the results from the immediate window? It should be a long sql string.