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

You'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" <(E-Mail Removed)> wrote in message
news:d2c50927-faf4-4b43-ab8a-(E-Mail Removed)...
> 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