help on NotInList SQL syntax error please..

[dxp]

Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please

Private Sub CDT_NotInList(NewData As String, Response As Integer)

On Error GoTo CDT_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add to the list now?" _
, vbQuestion + vbYesNo, "DentureBase")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new CDT has been added to the list." _
, vbInformation, "DentureBase"
Response = acDataErrAdded
Else
MsgBox "Please choose a CDT from the list." _
, vbInformation, "DentureBase"
Response = acDataErrContinue
End If
CDT_NotInList_Exit:
Exit Sub
CDT_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CDT_NotInList_Exit


End Sub
Thanks very much

 

[MStef]

Private Sub NN_NotInList (.............
Response = acDataErrorContinue
Msgbox "Wrong data, choose from combo!"
End Sub

 

[dxp]

thanks MStef for getting back, but I think the error lies in this portion of the SQL,have you any ideas?

strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"

 

[CraigDolphin]

Try renaming your table to avoid the use of the apostrophe (single quote).

 

[Moniker]

You're also trying to use a value list like it's SQL Server or DB2. In addition to removing the 's (incorrect English anyway ), the INSERT INTO in Access has a different syntax:

INSERT INTO YourTableName (Field1, Field2, FieldX)

If you're selecting from an existing table, the SELECT that follows would look like this:

SELECT Field1, Field2, Field X FROM YourSourceTableName;

If you're selecting values, you would define those values in the SQL, like this:

SELECT "ABC" As Field1, "DEF" As Field2, "GHI" As FieldX;

Note that the value selection (no source table) SQL does not include a FROM clause.

To see how to handle different data types (dates, numbers, etc.), make yourself a dummy table with each of the types of data you want to insert into. For example, make this table:

[b]t_Append_Testing[/b]

[u]FieldName[/u]   [u]DataType[/u]
TextField   Text
NumField    Number
DateField   Date/Time
BolField    Yes/No

Save the table (don't worry about primary keys or anything -- this is a dummy table). Now open a new query, do not add any tables to it, and change the query type to "Append Query". It will ask you what table you want to append to, so select "t_Append_Testing".

Put in anything for TextField, some number for NumField, #1/1/2008# (with the number signs) for DateField, and True (no quotes) for BolField. Make sure you assign each one to the proper "Append To" field. Take a look at the SQL view of what you just did and you'll see the proper formatting.

 

[pbaldy]

Actually, the VALUES clause is perfectly acceptable syntax for Access. I suspect the problem is the apostrophe, as previously mentioned.

 

[dxp]

Thanks for the replys and the suggestions. I ended up using this code
Private Sub MyCombo_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_ErrorHandler

' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf

' conenction and recordset object variables
Dim cn As Connection
Dim rs As ADODB.Recordset

' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "tbl_CDTs", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("CDT_Name") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset connection
End With
Response = acDataErrAdded ' confirm record added
Else
Me.CDT.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If

Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set cn = Nothing
Exit Sub

Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler

End Sub

Still got the error...until I removed the ' as you suggested then it worked just right. Thanks very much for your help.Guys this has been driving me mad for days... On to the next problem

 

[RuralGuy]

Here's some links if you don't have them already:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[dxp]

Thanks for the links RuralGuy, I will certainly be reading them closley...I have learned a very frustrating lesson today

 

[RuralGuy]

Any time...that's why we are here.