View Single Post
Old 07-12-2019, 01:16 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need help with INSERT INTO

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)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote