Link to home
Create AccountLog in
Avatar of samandrew
samandrew

asked on

Syntax error on INSERT INTO statement

Hi I have the following code and get an error on my JCpallets INSERT INTO line can anyone spot my error please.

Private Sub cmdAdjust_Click()
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM DailyProduction WHERE Date = #" & txtDate.Text & "#"
 
 rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
     
      mysql = "UPDATE DailyProduction Set StanCode = " & txtBoxDividend.Text & ", PreCode = " & txtBoxDividend1.Text & " Where [Date] = #" & txtDate.Text & "#"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY UPDATED", vbOKOnly
Else
      mysql = "INSERT INTO DailyProduction (StanCode, PreCode, [Date]) Values (" & txtBoxDividend.Text & "," & txtBoxDividend1.Text & ", #" & txtDate.Text & "#)"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY CREATED", vbOKOnly
End If
 
rs.Close

mysql = "SELECT Count(*) AS RecExists FROM JCpallets WHERE Date = #" & txtDate.Text & "#"
rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
     
      mysql = "UPDATE JCpallets Set StanPallet1 = " & txtBox1.Text & ", StanPallet2 = " & txtBox2.Text & ",StanPallet3 = " & txtBox3.Text & ",StanPallet4 = " & txtBox4.Text & ", StanPallet5 = " & txtBox5.Text & ",PrePallet1 = " & txtBox6.Text & ", PrePallet2 = " & txtBox7.Text & ",PrePalle3 = " & txtBox8.Text & ", PrePallet4 = " & txtBox9.Text & ", PrePallet5 = " & txtBox10.Text & ", Where [Date] = #" & txtDate.Text & "#"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY UPDATED", vbOKOnly
Else
      mysql = "INSERT INTO JCpallets (StanPallet1, StanPallet2, StanPallet3, StanPallet4, StanPallet5, PrePallet1, PrePallet2, PrePallet3, PrePallet4, PrePallet5, [Date]) Values (" & txtBox1.Text & "," & txtBox2.Text & "," & txtBox3.Text & "," & txtBox4.Text & "," & txtBox5 & "," & txtBoxs1.Text & "," & txtBoxs2.Text & "," & txtBoxs3.Text & "," & txtBoxs4.Text & "," & txtBoxs5.Text & ", #" & txtDate.Text & "#)"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY CREATED", vbOKOnly
End If
 
rs.Close
End Sub

Many Thanks
Andy
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

what is the database?
on which of the 2 INSERTs does it fail?
Besides AngelIII's questions, it would be helpful to know what the error message is.

I do notice Access-specific Date wrapping, e.g. "#" & txtDate.text & "#"

If you're not working against MS Access, those # signs should probably be single quotes -- '

Avatar of samandrew
samandrew

ASKER

Hi Both
Hi I have the following code and get an error on my JCpallets <<<<<<<<<<,INSERT INTO<<<<< line can anyone spot my error please.

Syntax error in INSERT INTO <<<<<<<<<<<<<<<<<<<<<<<

I am using Microsoft Access Office 2007

Many Thanks
Andy
hi

are all your fields except the date numeric type? does all textboxes contain a value(number)? pls try this.
mysql = "INSERT INTO JCpallets (StanPallet1, StanPallet2, StanPallet3, StanPallet4, StanPallet5, PrePallet1, PrePallet2, PrePallet3, PrePallet4, PrePallet5, [Date]) Values (" & VAL(txtBox1.Text) & "," & VAL(txtBox2.Text) & "," & VAL(txtBox3.Text) & "," & VAL(txtBox4.Text) & "," & VAL(txtBox5.Text) & "," & VAL(txtBoxs1.Text) & "," & VAL(txtBoxs2.Text) & "," & VAL(txtBoxs3.Text) & "," & VAL(txtBoxs4.Text) & "," & VAL(txtBoxs5.Text) & ", #" & txtDate.Text & "#)"

Open in new window

Hi ee rlee

Have tried your code and now works with the INSERT STATEMENT, however am now getting a syntax error on the UPDATE STATEMENT. Also noticed I had named the txtBox wrong so have now changed these to txtBoxs.

mysql = "SELECT Count(*) AS RecExists FROM JCpallets WHERE Date = #" & txtDate.Text & "#"
rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
     
      mysql = "UPDATE JCpallets Set StanPallet1 = " & txtBox1.Text & ", StanPallet2 = " & txtBox2.Text & ",StanPallet3 = " & txtBox3.Text & ",StanPallet4 = " & txtBox4.Text & ", StanPallet5 = " & txtBox5.Text & ",PrePallet1 = " & txtBoxs1.Text & ", PrePallet2 = " & txtBoxs2.Text & ",PrePalle3 = " & txtBoxs3.Text & ", PrePallet4 = " & txtBoxs4.Text & ", PrePallet5 = " & txtBoxs5.Text & ", Where [Date] = #" & txtDate.Text & "#"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY UPDATED", vbOKOnly
Else
      mysql = "INSERT INTO JCpallets (StanPallet1, StanPallet2, StanPallet3, StanPallet4, StanPallet5, PrePallet1, PrePallet2, PrePallet3, PrePallet4, PrePallet5, [Date]) Values (" & txtBox1.Text & "," & txtBox2.Text & "," & txtBox3.Text & "," & txtBox4.Text & "," & txtBox5 & "," & txtBoxs1.Text & "," & txtBoxs2.Text & "," & txtBoxs3.Text & "," & txtBoxs4.Text & "," & txtBoxs5.Text & ", #" & txtDate.Text & "#)"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY CREATED", vbOKOnly
End If
 
rs.Close
End Sub

Many Thanks
Andy
try this
If rs!RecExists > 0 Then
      mysql = "UPDATE JCpallets Set StanPallet1 = " & VAL(txtBox1.Text) & ", StanPallet2 = " & VAL(txtBox2.Text) & ",StanPallet3 = " & VAL(txtBox3.Text) & ",StanPallet4 = " & VAL(txtBox4.Text) & ", StanPallet5 = " & VAL(txtBox5.Text) & ",PrePallet1 = " & VAL(txtBoxs1.Text) & ", PrePallet2 = " & VAL(txtBoxs2.Text) & ",PrePalle3 = " & VAL(txtBoxs3.Text) & ", PrePallet4 = " & VAL(txtBoxs4.Text) & ", PrePallet5 = " & VAL(txtBoxs5.Text) & ", Where [Date] = #" & txtDate.Text & "#"
      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY UPDATED", vbOKOnly
Else
      mysql = "INSERT INTO JCpallets (StanPallet1, StanPallet2, StanPallet3, StanPallet4, StanPallet5, PrePallet1, PrePallet2, PrePallet3, PrePallet4, PrePallet5, [Date]) Values (" & VAL(txtBox1.Text) & "," & VAL(txtBox2.Text) & "," & VAL(txtBox3.Text) & "," & VAL(txtBox4.Text) & "," & VAL(txtBox5.Text) & "," & VAL(txtBoxs1.Text) & "," & VAL(txtBoxs2.Text) & "," & VAL(txtBoxs3.Text) & "," & VAL(txtBoxs4.Text) & "," & VAL(txtBoxs5.Text) & ", #" & txtDate.Text & "#)"      Get_My_Connection.Execute mysql
      MsgBox "ALDI PRODUCTION FIGURES SUCESSFULLY CREATED", vbOKOnly
End If

Open in new window

Hi still get SYNTAX error in UPDATE STATEMENT, although the INSERT INTO works on all my cmdButtons

Many Thanks
Andy
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi
Has done the trick

Many Thanks
Andy