Is it really t_position rather than t_program as in the example that worked?

When you paste into a query you need to get rid of some quotes and provide a
NewData value, as VBA and SQL to not use quite the same language:

INSERT INTO t_position(Position) VALUES("Aaa")

Again, do the same thing with the SQL that works (including Debug.Print in
the VBA code).

Sklyn wrote:
Ronaldo,
No, the box is text just like the rest.

Tom,
I pasted
strSQL = "INSERT INTO t_position(Position) VALUES(""" & _
NewData & """)"
Into a new query in sql view and switched to design view, Got a msg
“Invalid SQL statement;expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’,
or ‘UPDATE’.”
When I put INSERT; at the beginning I get a msg
“Syntax error in INSERT INTO statement.”

This is the first time I’ve done anything like this so please be patient.

Bruce,
The exact text of the error message is
"Run-time error '-2147217900 (80040e14)':
Syntax error in INSERT INTO statement."

When I run the code with the breakpoint set it just highlights the line
yellow and puts a yellow arrow on top of the 2nd line dot.??
What is meant to happen?

I put “Debug.Print strSQL” after the line and in the immediate window it shows
“INSERT INTO t_position(Position) VALUES("Aaa")”
Aaa is the value not in list I tried to add.

Here is a code that works:

Private Sub Program_NotInList(NewData As String, Response As Integer)
Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of makes?"

strSQL = "INSERT INTO t_program(program) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub

And the code that does not work:

Private Sub Position_NotInList(NewData As String, Response As Integer)
Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of makes?"

strSQL = "INSERT INTO t_position(Position) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub

Thanks so much guys for your efforts

What is the exact text of the error message? Did you set the break point as
suggested? If you are unfamiliar with the process, open the VBA editor and
[quoted text clipped - 66 lines]
XML/XSL VB Tester
http://www.eggheadcafe.com/tutorials...vb-tester.aspx

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1