More syntax errors

D

doobybug1

Registered User.
Local time
Today, 10:48
Joined
May 22, 2012
Messages
36
I am trying another insert statement

Code: 
  sql = "INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)" & _
             "VALUES(" & refNo & "," & Volume & ", " & Title & "," & ContractNo & ", '" & Format(DateOfCreation, "dd-mmm-yyyy") & "', " & Description & ", " & subjTerms & ", " & page & ", " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ")"

Debug.Print sql is giving me this output:
Code: 
INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)VALUES(451,1, test,1, '18-Jan-1818', test, test, 1-3, 1, 2, 4, 2, 1)

which I guess it is correct, however when the code reaches

CurrentDb.Execute sql

I am getting Runtime error 3134 Syntax error in Insert Into Statement...any ideas why?
 
I am posting the whole code of the sub below:
Code: 
Private Sub btnSave_Click()
    Dim refNo As String
    Dim Volume As Integer
    Dim Title As String
    Dim ContractNo As Integer
    Dim PlaceOfCreationNo As Integer
    Dim DateOfCreation As Date
    Dim Description As String
    Dim subjTerms As String
    Dim TypeOfContractNo As Integer
    Dim page As String
    Dim lan1 As String
    Dim lan2 As String
    Dim lan3 As String
    
    Dim sql As String

     
    If (IsNull([Form_tblCaseInfo subform].txtContractNo) Or IsNull([Form_tblCaseInfo subform].txtPage) Or IsNull([Form_frmCaseInfo].cmbNotary.Column(0)) Or IsNull([Form_frmCaseInfo].cmbVolume)) Then
         MsgBox "Notary, Volume, Contract No and Page/Folio cannot be left blank"
        
    Else
            refNo = [Form_frmCaseInfo].cmbNotary.Column(0)
            Volume = [Form_frmCaseInfo].cmbVolume.Value
            ContractNo = Me.txtContractNo
            
            If (Not IsNull(Me.txtTitle)) Then
                Title = Me.txtTitle
            Else
                Title = ""
            End If
            
           If (Not IsNull(Me.txtDate)) Then
                DateOfCreation = Me.txtDate
            End If
            
            If (Not IsNull(Me.txtPage)) Then
                page = Me.txtPage
            End If
         
            If (Not IsNull(Me.cmbPlaceOfCreation.Column(0))) Then
                PlaceOfCreationNo = Me.cmbPlaceOfCreation.Column(0)
            End If
            
            If (Not IsNull(Me.cmbTypeOfContract.Column(0))) Then
                TypeOfContractNo = Me.cmbTypeOfContract.Column(0)
            End If
            
            If (Not IsNull(Me.txtDescription)) Then
                Description = Me.txtDescription
            Else
                Description = ""
            End If
            If (Not IsNull(Me.txtSubjectTerms)) Then
                subjTerms = Me.txtSubjectTerms
            Else
                subjTerms = ""
            End If
          
            If (Not IsNull(Me.cmbLang1.Column(0))) Then
                lan1 = Me.cmbLang1.Column(0)
            Else
                lan1 = 4
            End If
            If (Not IsNull(Me.cmbLang2.Column(0))) Then
                lan2 = Me.cmbLang2.Column(0)
            Else
                lan2 = 4
            End If
            If (Not IsNull(Me.cmbLang3.Column(0))) Then
                lan3 = Me.cmbLang3.Column(0)
            Else
                lan3 = 4
            End If
            
             sql = "INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)" & _
             "VALUES(" & refNo & "," & Volume & ", " & Title & "," & ContractNo & ", '" & Format(DateOfCreation, "dd-mmm-yyyy") & "', " & Description & ", " & subjTerms & ", " & page & ", " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ")"
             
             Debug.Print sql
'            On Error GoTo ErrorMessage
             CurrentDb.Execute sql
'
'
'             cmbNotary.Value = Null
'            Me.txtVolume = Null
'            Me.txtDateStart = Null
'            Me.txtDateEnd = Null
             
    End If
     
    
    
Exit Sub

ErrorMessage:
    MsgBox "Record already exists"

End Sub
 
Need a space before VALUES. Also, date/time fields use # as a delimiter. Quotes for text, nothing for numeric.
 
And is 1-3 supposed to be text? It will evaluate to -2 if it's a numeric field.
 
And if it wasn't clear, your text values need quotes around them. Like:

"..." & ", '" & Title & "'," &...
 
1-3 is text yes

I have amended the sql as follows:
Code: 
sql = "INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)" & _
             " VALUES(" & refNo & "," & Volume & ", " & Title & "," & ContractNo & ", # " DateOfCreation " #, " & Description & ", " & subjTerms & ", " & page & ", " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ")"
I am getting a new message now:

the expression onclick you entered on the event property setting produced the following error
In the help section it states:

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.
 
Included quotes but still getting a syntax error

Code: 
  sql = "INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)" & _
             " VALUES('" & refNo & "'," & Volume & ", '" & Title & "'," & ContractNo & ", # " DateOfCreation " #, '" & Description & "', '" & subjTerms & "', '" & page & "', '" & lan1 & "', '" & lan2 & "', '" & lan3 & "', " & PlaceOfCreationNo & ", " & TypeOfContractNo & ")"

Those fields that are saved in the table as numeric values I didn't include single quotes...should I include them there too?
 
I did the quotes as I mentioned above but nothing :( it's driving me crazy!
 
What's the debug producing now?
 
Still Syntax error on the Currentdb.execute...this is how my sql looks like now:

Code: 
sql = "INSERT INTO tblCaseInfo(NotaryRefNo, Volume, Title, ContractNo, DateOfCreation, Description, Subject Terms, Page/Folio, Language1, Language2, Language3, PlaceOfCreationNo, TypeOfContractNo)" & _
             " VALUES('" & refNo & "'," & Volume & ", '" & Title & "'," & ContractNo & ", # " & DateOfCreation & " #, '" & Description & "', '" & subjTerms & "', '" & page & "', " & lan1 & ", " & lan2 & ", " & lan3 & ", " & PlaceOfCreationNo & ", " & TypeOfContractNo & ")"

Volume, ContractNo, lan1, lan2, lan3, PlaceOfCreationNo, TypeOfContractNo are all numeric fields...the rest are text except the date field

The table has a primary key which is an autonumber that I am assuming it gets populated automatically when I perform the Insert command :banghead:
 
Again, what does the debug.print produce now? You don't want spaces between the # and the value. You are correct, the autonumber field shouldn't be included.
 
You must log in or register to reply here.

Similar threads

R
Uploading Image to MySql database
Replies
5
Views
6,588
preetisoft2
P
R
Syntax error
Replies
1
Views
2,744
dan-cat
dan-cat

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom