0

Getting a run-time error when running the following command:

mySQL = "INSERT INTO incidentlog (mon, yr, observation) VALUES (" & Me.Combo37.Column(0) & ", " & Me.Combo39.Column(0) & ", " & Me.Combo10.Column(0) & ": " & Me.Text12 & "" & vbCrLf & "--" & Me.Combo35 & "" & Me.Combo37 & "" & Me.Combo39 & "/" & Me.Combo18 & "/" & Me.Text16 & "/COR " & Me.Text21 & " " & Me.Combo25.Column(2) & ")"
Debug.Print mySQL
DoCmd.RunSQL mySQL

debug prints right, but I can't seem to figure out where the issue is. combo35, combo39, and text16 are all numerical fields.

debug print (edited information out):

INSERT INTO incidentlog (mon, yr, observation) VALUES (Jan, 2022, PO-02: EDITED OUT.
--01Jan2022/EDITED OUT/0700/COR my notes here USER)
user2002716
  • 120
  • 2
  • 11
  • Please edit your question and add the output of `Debug.Print mySQL`. -- And PLEASE give your controls meaningful names, not Combo10 or Text12. – Andre Oct 13 '22 at 09:57

1 Answers1

1

As the last field, observation, is text, you need to wrap it in single quotes:

mySQL = "INSERT INTO incidentlog (mon, yr, observation) 
VALUES (" 
& Me.Combo37.Column(0) & ", " 
& Me.Combo39.Column(0) & ", '" 
& Me.Combo10.Column(0) & ": " & Me.Text12 & "" & vbCrLf & "--" & Me.Combo35 & "" & Me.Combo37 & "" & Me.Combo39 & "/" & Me.Combo18 & "/" & Me.Text16 & "/COR " & Me.Text21 & " " & Me.Combo25.Column(2) 
& "')"
Applecore
  • 3,934
  • 2
  • 9
  • 13
  • I knew I was missing it just too busy to figure out where. I appreciate it! – user2002716 Oct 13 '22 at 10:27
  • 1
    [Bobby Tables](https://xkcd.com/327/) says hello. Better use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables (especially user input!) with SQL. – Andre Oct 13 '22 at 10:39
  • 1
    To be fair, I'd probably use a recordset (slightly more overhead, but you don't have to worry about single quotes in the data) – Applecore Oct 13 '22 at 11:00
  • Indeed this would be easier and better readable. – Andre Oct 13 '22 at 11:50