NotInList Event

[Guest]

Hi,

I'm having a bit of trouble with some code behind the NotInList event. I
want to the user to have the option of adding a new contact method to a list
after being promted. The problem is however that the list itself is a combo
box that draws its data from a table 'ContactMethodList'. I have been
successful in the past using the code below but because the list is located
elsewhere this time it doesn't seem to work. My code is attached below:

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


Any help appreciated.

 

[Albert D. Kallal]

old:
strSQL = "INSERT INTO
ContactMethod([ContactMethodList].[ContactMethod]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
new:

strSQL = "insert into contactMethodList ContactMethod " & _
"Values ('" & NewData & "')"
currentdb.Execute strSql