By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,050 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.

Syntax error

100+
P: 163
Expand|Select|Wrap|Line Numbers
  1. Private Sub Part_Number_NotInList(NewData As String, Response As Integer)
  2.  
  3.     On Error GoTo Part_Number_NotInList_Err
  4.  
  5.     Dim intAnswer As Integer
  6.     Dim strSQL As String
  7.  
  8.     intAnswer = MsgBox("The Part Number" & Chr(34) & NewData & _
  9.         Chr(34) & " is not currently listed." & vbCrLf & _
  10.         "Would you like to add it to the list now?" _
  11.         , vbQuestion + vbYesNo, "Part Number")
  12.  
  13.     If intAnswer = vbYes Then
  14.  
  15. 'Change area as needed
  16.  
  17.         strSQL = "INSERT INTO  tblPartNumber[CZEKCD] " & _
  18.                  "VALUES ('" & NewData & "');"
  19.         DoCmd.SetWarnings False
  20.         DoCmd.RunSQL strSQL
  21.         DoCmd.SetWarnings True
  22.         MsgBox "The new Part number has been added to the list." _
  23.             , vbInformation, "Part Number"
  24.         Response = acDataErrAdded
  25.  
  26.     Else
  27.  
  28. 'Change area of target
  29.  
  30.         MsgBox "Please choose Part Number from the list." _
  31.             , vbInformation, "Part Number"
  32.         Response = acDataErrContinue
  33.  
  34.     End If
  35.  
  36. Part_Number_NotInList_Exit:
  37.     Exit Sub
  38.  
  39. Part_Number_NotInList_Err:
  40.     MsgBox Err.Description, vbCritical, "Error"
  41.     Resume Part_Number_NotInList_Exit
  42.  
  43. End Sub
I get a "Syntax Error in INSERT INTO statement" message when running the above code.

Code compiles with no errors. I use this same code on other databases but for some reason this one is giving me fits.

This is for updating a table through a combobox that has the limit to list as yes.

I tried using a "." and "!" between "tblPartNumber[CZEKCD]" this part of the statement but it changes nothing.

Does anyone see a possible issue or reason why?

Thanks for any help provided.
1 Week Ago #1

✓ answered by cactusdata

I doubt you have this working anywhere. The syntax is:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblPartNumber ([CZEKCD]) " & _
  2.                  "VALUES ('" & NewData & "');"

Share this Question
Share on Google+
3 Replies


P: 18
I doubt you have this working anywhere. The syntax is:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblPartNumber ([CZEKCD]) " & _
  2.                  "VALUES ('" & NewData & "');"
1 Week Ago #2

100+
P: 163
That was it, can't believe I missed the ().

Thanks again.
1 Week Ago #3

NeoPa
Expert Mod 15k+
P: 31,494
Hi DJ.

Glad that got sorted for you by CactusData but do remember how to deal with such problems in future (How to Debug SQL String).

This one was very straightforward but had it not been then your post would have been short of the required information - as explained in my linked article.

Please try to remember for future threads.

Also, I've set CactusData's post as Best Answer for you.
6 Days Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.