Run-time error '-2147217900 (80040e14)'

[Guest]

Hi,
I tried to write codes to let users add new customer names to a predesigned
list (tblCustomerList, field: Customer Name), but I keep on getting an error
saying "Run-time error '-2147217900(80040e14)' Syntax error in INSERT INTO
statement from the following code:

Private Sub CboCustomerName_NotInList(NewData As String, _
Response As Integer)
Dim new_data As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
'double up any apostrophes before Insert
new_data = Replace(NewData, "'", "''")
Response = acDataErrAdded
conn.Execute "Insert Into " & _
"tblCustomerList(Customer Name) Values('" & _
new_data & "')"
End Sub

I'm not sure why it's not working........

 

[Guest]

Try putting square brackets around Customer Name, since this field appears to
include a space:

conn.Execute "Insert Into " & _
"tblCustomerList([Customer Name]) Values('" & _
new_data & "')"

It is best to avoid the use of special characters (including spaces) and
reserved words in anything that you assign a name to in Access.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thank you so much Tom.
It worked and I also changed the naming to avoid space.
If I had named the field "CustomerName" instead of "Customer Name" I
wouldn't have needed to place square brackets around it? Or it's better to
always place square brackets around the field name?

Try putting square brackets around Customer Name, since this field appears to
include a space:

conn.Execute "Insert Into " & _
"tblCustomerList([Customer Name]) Values('" & _
new_data & "')"

It is best to avoid the use of special characters (including spaces) and
reserved words in anything that you assign a name to in Access.

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi,
I tried to write codes to let users add new customer names to a predesigned
list (tblCustomerList, field: Customer Name), but I keep on getting an error
saying "Run-time error '-2147217900(80040e14)' Syntax error in INSERT INTO
statement from the following code:

Private Sub CboCustomerName_NotInList(NewData As String, _
Response As Integer)
Dim new_data As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
'double up any apostrophes before Insert
new_data = Replace(NewData, "'", "''")
Response = acDataErrAdded
conn.Execute "Insert Into " & _
"tblCustomerList(Customer Name) Values('" & _
new_data & "')"
End Sub

I'm not sure why it's not working........

 

[Guest]

If I had named the field "CustomerName" instead of "Customer Name" I

wouldn't have needed to place square brackets around it?
Correct.


Or it's better to always place square brackets around the field name?

You don't need to use square brackets as long as you pick a good naming
convention and stick with it (ie. no spaces, special characters, or reserved
words). Adding the brackets won't hurt anything. I wouldn't necessarily
consider it "better" to add something that is not required. Your choice.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________