Need help with INSERT INTO

[Gazzyy]

I keep getting a syntax error when trying to run this code. I have looked over it a hundred times and cant find the problem. Somebody please help! (Sorry if the code is a little jumbled, had to copy and paste code from phone).

CurrentDb.Execute "INSERT INTO ReuseTestResults(FieldName, DateTested, TotalVol, TotalMass, Mesh8, Mesh10, Mesh12, Mesh14, Mesh16, Mesh20, Mesh30, Mesh40, Mesh50, Mesh60, Mesh70, Mesh100, MeshPan, AppDens, Mesh8Results, Mesh10Results, Mesh12Results, Mesh14Results, Mesh16Results, Mesh20Results, Mesh30Results, Mesh40Results, Mesh50Results, Mesh60Results, Mesh70Results, Mesh100Results, PanResults, Recovered 8, Recovered 10, Recovered 12, Recovered 14, Recovered 16, Recovered 20, Recovered 30, Recovered 40, Recovered 50, Recovered 60, Recovered 70, Recovered 100, Recovered Pan) " & _

" VALUES(" & Me.txtFieldname & ",'" & Me.txtDatetested & "','" & Me.txtTotalvol & "','" & Me.txtTotalmass & "','" & Me.txt8mass & "','" & Me.txt10mass & "','" & Me.txt12mass & "','" & Me.txt14mass & "','" & Me.txt16mass & "','" & Me.txt20mass & "','" & Me.txt30mass & "','" & Me.txt40mass & "','" & Me.txt50mass & "','" & Me.txt60mass & "','" & Me.txt70mass & "','" & Me.txt100mass & "','" & Me.txtPanmass & "','" & Me.txtAppdens & "','" & Me.txt8results & "','" & Me.txt10results & "','" & Me.txt12results & "','" & Me.txt14results & "','" & Me.txt16results & "','" & Me.txt20results & "','" & _

Me.txt30results & "','" & Me.txt40results & "','" & Me.txt50results & "','" & Me.txt60results & "','" & Me.txt70results & "','" & Me.txt100results & "','" & Me.txtPanresults & "','" & Me.txt8recovered & "','" & Me.txt10recovered & "','" & Me.txt12recovered & "','" & Me.txt14recovered & "','" & Me.txt16recovered & "','" & Me.txt20recovered & "','" & Me.txt30recovered & "','" & Me.txt40recovered & "','" & Me.txt50recovered & "','" & Me.txt60recovered & "','" & Me.txt70recovered & "','" & Me.txt100recovered & "','" & Me.txtPanrecovered & "')"





End Sub

 

[Gasman]

Construct a string(s) with those values and Debug.Print it/them.?

Why does TxtFieldname not have any quotes?

 

[Gazzyy]

Not sure how to debug.print.
As far as I know, the correct syntax for the FIRST value is VALUES(" & Me.txtID & ",'" & etc.

 

[CJ_London]

you have spaces in your field names e.g.

Recovered 8,

if you have spaces then you must use square brackets

[Recovered 8],

aside from the extra care you need to take when coding because of the spaces, spaces can also cause problems when the field or control is referenced in vba code - vba requires substituting an underscore for a space - so now you have two different names for the same thing.

 

[arnelgp]

if you have Space on your field name, enclosed then in square bracket:

Recovered 8, Recovered 10, Recovered 12,

change to:

[Recovered 8], [Recovered 10], [Recovered 12], …

 

[Gazzyy]

Added brackets to those field names and it seemed to solve the syntax error issue. Now I'm getting "Too few parameters. Expected 2." Any thoughts on that? Thanks for the help so far.

 

[arnelgp]

if the field you are updating is Numeric, you don't enclosed them in quotation mark.
better yet build a function to handle those delimiters for you:

Public Function fnAnySQL(ByVal strSQL As String, ParamArray p() As Variant)
    Dim param As Parameter
    Dim i As Integer
    With CurrentDb.CreateQueryDef("", strSQL)
        For i = 0 To .Parameters.count - 1
            .Parameters(i) = p(i)
        Next
        If InStr(strSQL, "SELECT") = 1 And InStr(strSQL, "INTO") = 0 Then
            ' Simple select query and not Create table query
            Set fnAnySQL = .OpenRecordset(dbOpenDynaset)
        Else
            ' Action queries
            .Execute
        End If
    End With
            
End Function

to call it:

call fnAnySQL("INSERT INTO ReuseTestResults(FieldName, DateTested, TotalVol, TotalMass, Mesh8, Mesh10, Mesh12, Mesh14, Mesh16, Mesh20, Mesh30, Mesh40, Mesh50, Mesh60, Mesh70, Mesh100, MeshPan, AppDens, Mesh8Results, Mesh10Results, Mesh12Results, Mesh14Results, Mesh16Results, Mesh20Results, Mesh30Results, Mesh40Results, Mesh50Results, Mesh60Results, Mesh70Results, Mesh100Results, PanResults, [Recovered 8], [Recovered 10], [Recovered 12], [Recovered 14], [Recovered 16], [Recovered 20], [Recovered 30], [Recovered 40], [Recovered 50], [Recovered 60], [Recovered 70], [Recovered 100], [Recovered Pan]) " & _
"VALUES(p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44)", Me.txtFieldname , Me.txtDatetested, Me.txtTotalvol,  e.txtTotalmass, Me.txt8mass , Me.txt10mass, Me.txt12mass, Me.txt14mass, Me.txt16mass,  Me.txt20mass, Me.txt30mass, Me.txt40mass, Me.txt50mass, Metxt60mass , Me.txt70mass, Me.txt100mass, Me.txtPanmass, Me.txtAppdens,  Me.txt8results, Me.txt10results, Me.txt12results, Me.txt14results, Me.txt16results, Me.txt20results, Me.txt30results, Me.txt40results, Me.txt50results, Me.txt60results, Me.txt70results, Me.txt100results, Me.txtPanresults , Me.txt8recovered, Me.txt10recovered, Me.txt12recovered, Me.txt14recovered, Me.txt16recovered, Me.txt20recovered, Me.txt30recovered, Me.txt40recovered, Me.txt50recovered, Me.txt60recovered, Me.txt70recovered, Me.txt100recovered, Me.txtPanrecovered)