Dynamic Table Insert - VBA

  • Thread starter Thread starter troubledstudent
  • Start date Start date
T

troubledstudent

I have a SQL query in which I want the user to select a table to update. All
definitions and column headings in the table are identical, but the data is
different. I keep getting a syntax error in which I have tried to debug but
I keep getting the same error. Any suggestions? Thanks

Private Sub rec_add_Click()
Dim sqladd As String

sportclub = Me.text1.Value

sqladd = "INSERT INTO " & sportclub & " (Name, [Birth Date], Phone, Gender,
Email, [Campus Address]" & _
", City, State, Zip, [School ID #], [Emergency Contact], [Emergency Phone],
" & _
"[# of years in club], [Years in School], Barometer, Flyer, Website, Friend,
" & _
"[Rec Sport Staff], Other, [Social Interaction], [Stress Reduction], Fun, "
& _
"[Physical Fitness], [Learn New Skill], [Gain Knowledge], [Leadership
Skills], " & _
"[Other Reason])" & _
" Values (""" & Me.text2.Value & """, """ & Me.text3.Value & """, " & _
"""" & Me.text4.Value & """, """ & Me.text5.Value & """, """ &
Me.text6.Value & """, " & _
"""" & Me.text7.Value & """, """ & Me.text8.Value & """, """ &
Me.text9.Value & """, " & _
"""" & Me.text10.Value & """, """ & Me.text11.Value & """, """ &
Me.text12.Value & """, " & _
"""" & Me.text13.Value & """, """ & Me.text14.Value & """, " & Me.opt1.Value
& ", " & _
"" & Me.opt3.Value & ", " & Me.opt5.Value & ", " & Me.opt2.Value & ", " &
Me.opt4.Value & ", " & _
"" & Me.opt6.Value & ", " & Me.opt7.Value & ", " & Me.opt9.Value & ", " &
Me.opt11.Value & ", " & _
"" & Me.opt8.Value & ", " & Me.opt10.Value & ", " & Me.opt12.Value & ", " &
Me.opt13.Value & ", " & _
"" & Me.opt14.Value & ")"

DoCmd.RunSQL sqladd
 
This has no syntax errors, but that doesn't mean it will execute correctly.
That would depend on correct syntax for the table's data types, but the
compiler will not catch that:

sqladd = "INSERT INTO " & sportclub & " (Name, [Birth Date], Phone, Gender,
Email, " & _
"[Campus Address], City, State, Zip, [School ID #], [Emergency Contact], " & _
"[Emergency Phone], [# of years in club], [Years in School], Barometer,
Flyer, " & _
"Website, Friend, [Rec Sport Staff], Other, [Social Interaction], [Stress
Reduction], " & _
"Fun, [Physical Fitness], [Learn New Skill], [Gain Knowledge], [Leadership
Skills], " & _
"[Other Reason]) Values (""" & Me.text2.Value & """, """ & Me.text3.Value &
""", " & _
"""" & Me.text4.Value & """, """ & Me.text5.Value & """, " & _
"""" & Me.text6.Value & """, " & """" & Me.text7.Value & """, """ &
Me.text8.Value & _
""", """ & Me.Text9.Value & """, """ & Me.text10.Value & """, " & _
"""" & Me.Text11.Value & """, """ & """" & Me.text12.Value & """, " & _
"""" & Me.text13.Value & """, """ & """" & Me.text14.Value & """, " & _
Me.opt1.Value & ", " & Me.opt3.Value & ", " & Me.opt5.Value & ", " &
Me.opt2.Value & _
", " & Me.opt4.Value & ", " & Me.opt6.Value & ", " & Me.opt7.Value & ", " & _
Me.opt9.Value & ", " & Me.opt11.Value & ", " & Me.opt8.Value & ", " & _
Me.opt10.Value & ", " & Me.opt12.Value & ", " & Me.opt13.Value & ", " &
Me.opt14.Value & ");"
--
Dave Hargis, Microsoft Access MVP


troubledstudent said:
I have a SQL query in which I want the user to select a table to update. All
definitions and column headings in the table are identical, but the data is
different. I keep getting a syntax error in which I have tried to debug but
I keep getting the same error. Any suggestions? Thanks

Private Sub rec_add_Click()
Dim sqladd As String

sportclub = Me.text1.Value

sqladd = "INSERT INTO " & sportclub & " (Name, [Birth Date], Phone, Gender,
Email, [Campus Address]" & _
", City, State, Zip, [School ID #], [Emergency Contact], [Emergency Phone],
" & _
"[# of years in club], [Years in School], Barometer, Flyer, Website, Friend,
" & _
"[Rec Sport Staff], Other, [Social Interaction], [Stress Reduction], Fun, "
& _
"[Physical Fitness], [Learn New Skill], [Gain Knowledge], [Leadership
Skills], " & _
"[Other Reason])" & _
" Values (""" & Me.text2.Value & """, """ & Me.text3.Value & """, " & _
"""" & Me.text4.Value & """, """ & Me.text5.Value & """, """ &
Me.text6.Value & """, " & _
"""" & Me.text7.Value & """, """ & Me.text8.Value & """, """ &
Me.text9.Value & """, " & _
"""" & Me.text10.Value & """, """ & Me.text11.Value & """, """ &
Me.text12.Value & """, " & _
"""" & Me.text13.Value & """, """ & Me.text14.Value & """, " & Me.opt1.Value
& ", " & _
"" & Me.opt3.Value & ", " & Me.opt5.Value & ", " & Me.opt2.Value & ", " &
Me.opt4.Value & ", " & _
"" & Me.opt6.Value & ", " & Me.opt7.Value & ", " & Me.opt9.Value & ", " &
Me.opt11.Value & ", " & _
"" & Me.opt8.Value & ", " & Me.opt10.Value & ", " & Me.opt12.Value & ", " &
Me.opt13.Value & ", " & _
"" & Me.opt14.Value & ")"

DoCmd.RunSQL sqladd
Click to expand...
 
I thnk your syntax error is because (if I counted right) there are 28 fields
and only 27 values. (BTW, the "value" property is the default, so it is not
necessary to type ".value".)

Like Dave said, you could also have errors due to the data types. Is the
field [Birth Date] a DATE/TIME or TEXT data type? If it is a DATE/TIME data
type, you need to use the proper delimiters.


Other things to be aware of in the future:

"Name" is a reserved word in Access and shouldn't be used for object names.
Plus, it is not descriptive; Name of what... a person, location, thing,
event,... See

http://www.allenbrowne.com/AppIssueBadWord.html

You shouldn't use spaces in object names. It is more difficult because you
have to use brackets around it. It is easy to read this "BirthDate" or
"Birth_Date".


The Ten Commandments of Access

http://www.mvps.org/access/tencommandments.htm




Don't use special characters in names: #,$,/,@, etc.

Look up "Naming conventions"...

Here is one site:

http://www.mvps.org/access/general/gen0012.htm


Take the time to rename the controls on your forms. Should I use Me.text2 or
Me.text3 for the [Birth Date] value in the SQL??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


troubledstudent said:
I have a SQL query in which I want the user to select a table to update. All
definitions and column headings in the table are identical, but the data is
different. I keep getting a syntax error in which I have tried to debug but
I keep getting the same error. Any suggestions? Thanks

Private Sub rec_add_Click()
Dim sqladd As String

sportclub = Me.text1.Value

sqladd = "INSERT INTO " & sportclub & " (Name, [Birth Date], Phone, Gender,
Email, [Campus Address]" & _
", City, State, Zip, [School ID #], [Emergency Contact], [Emergency Phone],
" & _
"[# of years in club], [Years in School], Barometer, Flyer, Website, Friend,
" & _
"[Rec Sport Staff], Other, [Social Interaction], [Stress Reduction], Fun, "
& _
"[Physical Fitness], [Learn New Skill], [Gain Knowledge], [Leadership
Skills], " & _
"[Other Reason])" & _
" Values (""" & Me.text2.Value & """, """ & Me.text3.Value & """, " & _
"""" & Me.text4.Value & """, """ & Me.text5.Value & """, """ &
Me.text6.Value & """, " & _
"""" & Me.text7.Value & """, """ & Me.text8.Value & """, """ &
Me.text9.Value & """, " & _
"""" & Me.text10.Value & """, """ & Me.text11.Value & """, """ &
Me.text12.Value & """, " & _
"""" & Me.text13.Value & """, """ & Me.text14.Value & """, " & Me.opt1.Value
& ", " & _
"" & Me.opt3.Value & ", " & Me.opt5.Value & ", " & Me.opt2.Value & ", " &
Me.opt4.Value & ", " & _
"" & Me.opt6.Value & ", " & Me.opt7.Value & ", " & Me.opt9.Value & ", " &
Me.opt11.Value & ", " & _
"" & Me.opt8.Value & ", " & Me.opt10.Value & ", " & Me.opt12.Value & ", " &
Me.opt13.Value & ", " & _
"" & Me.opt14.Value & ")"

DoCmd.RunSQL sqladd
Click to expand...
 
Back
Top