If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One Combo Box won't work??
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 |
#2
|
|||
|
|||
One Combo Box won't work??
On Tue, 3 Nov 2009 18:38:01 -0800, Sklyn
wrote: 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 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 |
#3
|
|||
|
|||
repli
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/tutorials...vb-tester.aspx |
#4
|
|||
|
|||
One Combo Box won't work??
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. "Sklyn" wrote: 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 |
#5
|
|||
|
|||
repli
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 click the vertical gray bar to the left of the code window. This hsould cause a dot to appear in the bar, and the line of code to be highlighted. Now run the code by attempting to update the combo box with a value not on the list. Also, you could place this line of code after the strSQL line: Debug.Print strSQL This will cause strSQL to be written to the immediate window (press Ctrl + G after running the code, or look for it when the code reaches the break point you set. Please post the INSERT for a combo box in which it works. As mentioned, you could be having the problem is you are trying to add a value to a Number field, in which case the quotes are incorrect. ajay indulkar wrote: i dont no 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/tutorials...vb-tester.aspx -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 |
#6
|
|||
|
|||
repli
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 "BruceM via AccessMonster.com" wrote: 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 click the vertical gray bar to the left of the code window. This hsould cause a dot to appear in the bar, and the line of code to be highlighted. Now run the code by attempting to update the combo box with a value not on the list. Also, you could place this line of code after the strSQL line: Debug.Print strSQL This will cause strSQL to be written to the immediate window (press Ctrl + G after running the code, or look for it when the code reaches the break point you set. Please post the INSERT for a combo box in which it works. As mentioned, you could be having the problem is you are trying to add a value to a Number field, in which case the quotes are incorrect. ajay indulkar wrote: i dont no 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/tutorials...vb-tester.aspx -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 . |
#7
|
|||
|
|||
repli
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 |
#8
|
|||
|
|||
repli
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.. "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 "BruceM via AccessMonster.com" wrote: 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 click the vertical gray bar to the left of the code window. This hsould cause a dot to appear in the bar, and the line of code to be highlighted. Now run the code by attempting to update the combo box with a value not on the list. Also, you could place this line of code after the strSQL line: Debug.Print strSQL This will cause strSQL to be written to the immediate window (press Ctrl + G after running the code, or look for it when the code reaches the break point you set. Please post the INSERT for a combo box in which it works. As mentioned, you could be having the problem is you are trying to add a value to a Number field, in which case the quotes are incorrect. ajay indulkar wrote: i dont no 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/tutorials...vb-tester.aspx -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200911/1 . |
#9
|
|||
|
|||
repli
I missed that one. You could have put brackets around the field name, but it
is better to use a non-reserved word. Allen Browne has a utility for checking such things: http://www.allenbrowne.com/Ap****ueBadWord.html Sklyn wrote: 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.. Ronaldo, No, the box is text just like the rest. [quoted text clipped - 150 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 |
Thread Tools | |
Display Modes | |
|
|