One Combo Box won't work??

S

Sklyn

Hi, I have a form with 12 Combo Boxes and the last one won't work.
Well it works but won't update the table/list if a new entry is added.
All the others are the same using the same code in the "On Not In List" Event.
All the rest work perfect, just the last box returns an error:

Run-time error '-2147217900 (80040e14)':
Syntax error in INSERT INTO statement.

When I click Debug it highlights "cmd.Execute" in my code.
Here is the code I am using:

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

I have checked the properties and all appears the same as other boxes, have
also check the referring table for errors.
Any idea what would cause this so I can check that?
I see that its telling me its an issue with INSERT INTO, but I have checked
and redone that part of the code many times now.

Thanks for ur thoughts
 
T

Tom van Stiphout

On Tue, 3 Nov 2009 18:38:01 -0800, Sklyn

One way to debug this is to set a breakpoint at the "strSQL =" line,
and paste that sql statement into a new query in SQL view. Switch to
Design view, and the parser will show you the errors of your ways :)

-Tom.
Microsoft Access MVP
 
A

ajay indulkar

i dont no



Sklyn wrote:

One Combo Box won't work??
03-Nov-09

Hi, I have a form with 12 Combo Boxes and the last one will not work.
Well it works but will not update the table/list if a new entry is added.
All the others are the same using the same code in the "On Not In List" Event.
All the rest work perfect, just the last box returns an error:

Run-time error '-2147217900 (80040e14)':
Syntax error in INSERT INTO statement.

When I click Debug it highlights "cmd.Execute" in my code.
Here is the code I am using:

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

I have checked the properties and all appears the same as other boxes, have
also check the referring table for errors.
Any idea what would cause this so I can check that?
I see that its telling me its an issue with INSERT INTO, but I have checked
and redone that part of the code many times now.

Thanks for ur thoughts

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
XML/XSL VB Tester
http://www.eggheadcafe.com/tutorial...-4e21-9b2c-13f77a74daff/xmlxsl-vb-tester.aspx
 
R

RonaldoOneNil

Is the last combo box used for a numerical value (picture) where all the
others are text ?
If this is the case you will not need the quotes around it and that will
cause the error.
 
S

Sklyn

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 :)
 
S

Sklyn

Well, It turns out it is a VERY basic issue....

POSITION is a reserved word..

So i changed all instances to "JobTitle" and it works perfect..

Should have checked that first hey! :)

Thanks people..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top