Syntax error

[DJRhino1175]

Private Sub Part_Number_NotInList(NewData As String, Response As Integer)
 
    On Error GoTo Part_Number_NotInList_Err
 
    Dim intAnswer As Integer
    Dim strSQL As String
 
    intAnswer = MsgBox("The Part Number" & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Part Number")
 
    If intAnswer = vbYes Then
 
'Change area as needed
 
        strSQL = "INSERT INTO  tblPartNumber[CZEKCD] " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new Part number has been added to the list." _
            , vbInformation, "Part Number"
        Response = acDataErrAdded
 
    Else
 
'Change area of target
 
        MsgBox "Please choose Part Number from the list." _
            , vbInformation, "Part Number"
        Response = acDataErrContinue
 
    End If
 
Part_Number_NotInList_Exit:
    Exit Sub
 
Part_Number_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Part_Number_NotInList_Exit
 
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.

[cactusdata]

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

strSQL = "INSERT INTO tblPartNumber ([CZEKCD]) " & _
                 "VALUES ('" & NewData & "');"

[DJRhino1175]

That was it, can't believe I missed the ().

Thanks again.

[NeoPa]

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.