Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 12:35 AM   #1
Gazzyy
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Gazzyy is on a distinguished road
Need help with INSERT INTO

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

Gazzyy is offline   Reply With Quote
Old 07-12-2019, 12:39 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 689 Times in 668 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Need help with INSERT INTO

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

Why does TxtFieldname not have any quotes?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Gazzyy (07-12-2019)
Old 07-12-2019, 12:52 AM   #3
Gazzyy
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Gazzyy is on a distinguished road
Re: Need help with INSERT INTO

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

Gazzyy is offline   Reply With Quote
Old 07-12-2019, 12:53 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,019
Thanks: 40
Thanked 3,577 Times in 3,455 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Need help with INSERT INTO

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-12-2019, 12:56 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,573
Thanks: 58
Thanked 2,427 Times in 2,327 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 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], …
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2019, 12:57 AM   #6
Gazzyy
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Gazzyy is on a distinguished road
Re: Need help with INSERT INTO

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.
Gazzyy is offline   Reply With Quote
Old 07-12-2019, 01:16 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,573
Thanks: 58
Thanked 2,427 Times in 2,327 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
Reply

Tags
access , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Insert Statement strSQL = "INSERT INTO Battlescar Modules & VBA 4 12-08-2017 10:03 AM
INSERT query does not insert all data in query marlan Queries 32 09-17-2015 01:35 PM
Bulk INSERT doesn't insert all records dirkps Modules & VBA 5 03-18-2010 12:20 AM
insert an array with INSERT INTO filo65 Modules & VBA 4 01-04-2006 12:09 AM
Insert Object without using the Insert Object Open Dialog Box. clivepot Modules & VBA 1 04-17-2003 06:57 AM




All times are GMT -8. The time now is 12:34 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World