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

isnt it

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

select not values

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

Could it be because Type is a reserved word?
Problem names and reserved words in Access (http://www.allenbrowne.com/AppIssueBadWord.html)

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...

"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.

Thanks Everyone for you help

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


thanks bunches
Niyx

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

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!

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

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

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...