INSERT INTO not inserting anything into anything :-( !

INSERT INTO not inserting anything into anything :-( !

Post by plh » Fri, 22 Apr 2011 05:18:54


Hi All,
Office 2010:
The whole context is way below, but the gist is that the command:

sSQL = "INSERT INTO tblToolsFiltered " _
&
"(IDOld,Tool,Material,Coating,Type,TNumber,Location1033,Bin,LocationTC,Size)
VALUES " _
& "(" & l & "," & sTool & "," & lMaterial & "," &
lCoating & "," _
& lType & "," & sTNumber & "," & sLocation1033 & "," &
sBin & "," _
& sLocationTC & "," & snSize & ")"
Debug.Print sSQL
d.Execute sSQL

Doesn't put anything into table tblToolsFiltered. When I open it up it
is still empty. There are no errors raised. I would certainly
appreciate some help because I am stumped.

An example of sSQL is:

INSERT INTO tblToolsFiltered
(IDOld,Tool,Material,Coating,Type,TNumber,Location1033,Bin,LocationTC,Size)
VALUES (19,'REAM CARB .0930',1,1,5,'T-12533','','','',0.093)

Thank You!
-plh

Context:

Private Sub cmdFind_Click()
Dim r1 As Recordset
Dim R2 As Recordset
Dim d As Database
Dim l As Long
Dim sSQL, sTool, sTNumber, sLocation1033, sBin, sLocationTC As String
Dim lMaterial, lCoating, lType As Long
Dim snSize As Single
On Error GoTo Hell
Set d = CurrentDb
Set r1 = d.OpenRecordset("tblToolsFiltered")

With r1
Do While .EOF = False
'.Edit
.Delete
'.Update
.MoveNext
Loop
End With
Set R2 = d.OpenRecordset("tblTools", , dbReadOnly)
With R2
Do While .EOF = False
l = .Fields("IDTool")
sTool = "'" & .Fields("Tool") & "'"
sTNumber = "'" & .Fields("TNumber") & "'"
sLocation1033 = "'" & .Fields("Location1033") & "'"
sBin = "'" & .Fields("Bin") & "'"
sLocationTC = "'" & .Fields("LocationTC") & "'"
lMaterial = .Fields("Material")
lCoating = .Fields("Coating")
lType = .Fields("Type")
snSize = .Fields("Size")
If InStr(UCase(.Fields("Tool")), UCase(Me.txtSelect)) > 0
Then
sSQL = "INSERT INTO tblToolsFiltered " _
&
"(IDOld,Tool,Material,Coating,Type,TNumber,Location1033,Bin,LocationTC,Size)
VALUES " _
& "(" & l & "," & sTool & "," & lMaterial & "," &
lCoating & "," _
& lType & "," & sTNumber & "," & sLocation1033 & "," &
sBin & "," _
& sLocationTC & "," & snSize & ")"
Debug.Print sSQL
d.Execute sSQL
End If
.MoveNext
Loop
d.Close
End With
Exit Sub
Hell:
If Err.Number = 3167 Or Err.Number = 94 Then
Resume Next
Else
MsgBox "Error #: " & Err.Number & vbCrLf & "Desc: " &
Err.Description
End If


End Sub
 
 
 

INSERT INTO not inserting anything into anything :-( !

Post by Ken Snel » Sun, 24 Apr 2011 01:38:05

ou're not including the delimiting ' characters for the string values.

This snippet:
"," & sTool & ","
should be this:
",'" & sTool & "',"

And so on for all the other variables that contain string values.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"plh" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...