Problem with Notinlist

[Niyx]

Hello all

I have tried searching for a solution to my problem but still can't figure it out.

I have a form with a combo box on called Type which is linked to the field Type in a table called tbCommunications, the Type field in the tbCommunications table is a lookup field which gets its list from a table called tbCommunication Types.

Basically I have the following code in the notinlist event for the combo box Type so that a user can choose to add their entry to the list.

Private Sub Type_NotInList(NewData As String, Response As Integer)
On Error GoTo Type_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The communication type " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Partnerships - Communications")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tbCommunication Types([Communication Type]) " _
        & "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new communication type has been added to the list." _
            , vbInformation, "Partnerships - Communications"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose communication type from the list." _
            , vbInformation, "Partnerships - Communications"
        Response = acDataErrContinue
    End If
Type_NotInList_Exit:
    Exit Sub
Type_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Type_NotInList_Exit

However when i run the code i get the message 'syntax error in the INSERT INTO statement.

Note: I got the original code for this from http://www.fontstuff.com/access/acctut20.htm

Can anyone help?

Niyx

 

[gemma-the-husky]

isnt it

strSQL = "INSERT INTO tbCommunication Types([Communication Type]) " _
& "SELECT ('" & NewData & "');"

select not values

 

[Niyx]

Thanks for your suggestion Gemma, but i still get the same error message

 

[RuralGuy]

Could it be because Type is a reserved word?
Problem names and reserved words in Access

 

[namliam]

Dont use spaces in table names and you prevent errors like this !

try this:
strSQL = "INSERT INTO [tbCommunication Types] ([Communication Type]) " _
& "VALUES ('" & NewData & "');"

and FYI
Instead of
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
you can simply use
currentdb.execute strSQL

does the same but less fuss...

 

[tehNellie]

"INSERT INTO tbCommunication Types([Communication Type]) "

looks to be your problem, if theres a space in the table name you also need to wrap it in square brackets. if the "types" isn't part of the table name it isn't part of the syntax.

 

[Niyx]

Thanks Everyone for you help

Problem is solved now, I removed the spaces from the table name and it now works.


thanks bunches
Niyx

 

[gemma-the-husky]

i think namliam hit it, spotting the space in the table name

 

[namliam]

Rule #1: Dont use spaces in any names anywhere anytime within a database, except for labels on forms and such... not in objects/fieldnames etc... !!!
Rule #2: Dont replace a space by using "_" either!

 

[gemma-the-husky]

now i dont agree with not using the underscore - does that have implications in SQL or something?

 

[WayneRyan]

Maybe,

Don't use an underscore IF there's a chance of a "similar" object which
differs only by the use of a space; such as:

Object 1: Some_Form_Control
Object 2: [Some Form Control]

Even though these are two different objects, Access will *sometimes* refer to
Object 2 as --> Some_Form_Control

If you never use embedded spaces, I don't think there are any problems with
using underscores.

At least I think that's how it works,
Wayne

 

[namliam]

The problem with underscores is
1) It is hard to see the difference between _ and __
2) It is hard to keep it consistant
3) *AAARGH*
4) IMHO it is a pain to type while coding...