1

I've got a table called {Cadets}, and a form called {addCadets}.

Cadets table has the following fields: {Cdt_ID} (PK field), {Class_ID], {L_Name}, {F_Name}, {Det_Email}, {Phone}, {Tag_ID}, and {Active} [A checkbox/t-f flag]

My form is inserting dynamic data from 5 unbound fields into {L_Name}, {F_Name}, {Class_ID}, {Det_Email}, and {Phone}; and inserting a static Null into the {Tag_ID} field. The command that I'm using:

DoCmd.RunSQL "INSERT INTO Cadets ( Class_ID, L_Name, F_Name, Det_Email, Phone, Tag_ID, Active) VALUES (" & Me.Class_ID & ", " & " '" & Me.L_Name & "', " & " '" & Me.F_Name & "', " & " '" & Me.Det_Email & "', " & " '" & Me.Phone & "', " & Tag & ", " & Active & ")"

keeps getting flagged for a Syntax error. Coworker of mine and I can't find the syntax error, even after combing it over closely. We're using Access 2001-2013, latest versions and so forth. The form generates all the data correctly into a message box, but won't write it into the table due to the syntax error.

starsin
  • 11
  • 3

1 Answers1

0

Too many " & "

should be 

DoCmd.RunSQL "INSERT INTO Cadets ( Class_ID, L_Name, F_Name, Det_Email, Phone, Tag_ID, Active) VALUES (" & Me.Class_ID & ", '" & Me.L_Name & "', '" & Me.F_Name & "','" & Me.Det_Email & "','" & Me.Phone & "', " & Tag & ", " & Active & ")"
RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26
  • I tried that, no dice. I've got another statement in the same file that uses the same syntax (all the &'s following each item) and it works like a champ. I just don't see what I'm missing... – starsin May 10 '17 at 20:36
  • I could, yeah. Only...it's now giving me a type conversion failure instead of the syntax error, and I didn't change a darn thing about it... edit: now – starsin May 10 '17 at 20:58
  • that's why i reckon too... ' ' for string because your initial post got those for numeric fields – RoMEoMusTDiE May 10 '17 at 21:04
  • Just made some changes to the statement, and it threw up a syntax error on the phone number field. trying to debug some on my own as well. – starsin May 10 '17 at 21:07
  • Run-time error '3075': Syntax error (missing operator) in query expression '(111)111-1122' <- that's just a garbage value I put in to test the insert function. I tried removing the '" and leaving it with just " for the Phone field. – starsin May 10 '17 at 21:10
  • what's your data type for phone and size? – RoMEoMusTDiE May 10 '17 at 21:13
  • Data-type is Short Text {looking at that, I might want to change that to a number field, shouldn't I?} and there is no field limit. – starsin May 10 '17 at 21:17
  • Short Text or just Text? if you run the query in msaccess goes okay? – RoMEoMusTDiE May 10 '17 at 21:29
  • It's short text. Also have an input mask on it so that I can format it for the typical user, so I can't change it to a Number data type field. – starsin May 10 '17 at 21:32