Need help with INSERT INTO (1 Viewer)

Gazzyy · Jul 12, 2019

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 · Jul 12, 2019

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

Why does TxtFieldname not have any quotes?

Gazzyy · Jul 12, 2019

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 · Jul 12, 2019

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 · Jul 12, 2019

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

Code:

Recovered 8, Recovered 10, Recovered 12,

change to:

Code:

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

Gazzyy · Jul 12, 2019

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 · Jul 12, 2019

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:

Code:

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:

Code:

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)

Need help with INSERT INTO (1 Viewer)

Gazzyy

New member

Gasman

Enthusiastic Amateur

Gazzyy

New member

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Gazzyy

New member

arnelgp

..forever waiting... waiting for jellybean!

Users who are viewing this thread