Solved prevent message "the text you Entered isnt an item in the list"

[FahadTiger]

Hi Experts..
i used the below code for entering new record inside combobox..if i choose yes from popup message its fine
but when i choose NO..display popup message (the text you Entered isnt an item in the list)!!
How can i prevent display this message?
thank you all
----------------------
Private Sub DoctorID_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
strTmp = "Add '" & NewData & "' as a new doctor?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
strTmp = "INSERT INTO doctors ( DoctorName ) " & _
"SELECT """ & NewData & """ AS DoctorName;"
DBEngine(0)(0).Execute strTmp, dbFailOnError
Response = acDataErrAdded
End If
End Sub

 

[theDBguy]

Try moving the Response=acDataErrAdded line outside of your If/Then block.

 

[FahadTiger]

Try moving the Response=acDataErrAdded line outside of your If/Then block.

i did like this:
Response = acDataErrAdded
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
but still message

 

[theDBguy]

i did like this:
Response = acDataErrAdded
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
but still message

How about try placing it at the end?

If...
...
End If
Response = acDataErrAdded

 

[FahadTiger]

How about try placing it at the end?

If...
...
End If
Response = acDataErrAdded

i do it before..and same thing
if there is no answer..i think the only one to change the message to force the user to click ok..and make delete button if he want to delete record in combo:
If MsgBox(strTmp, vbOKOnly + vbDefaultButton2 + vbQuestion, "Not in list") = vbOK Then

 

[isladogs]

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

Dim strTmp As String
strTmp = "Add '" & NewData & "' as a new doctor?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    strTmp = "INSERT INTO doctors ( DoctorName ) " & _
    "SELECT '" & NewData & "' AS DoctorName;"
    DBEngine(0)(0).Execute strTmp, dbFailOnError
    Response = acDataErrAdded
Else
    Response = 0
    Me.DoctorID = ""
End If

End Sub

 

[arnelgp]

Private Sub DoctorID_NotInList(NewData As String, Response As Integer)
    Dim strTmp As String
    'add response in case "No" is the answered below question.
    Response = acDataErrContinue
    
    strTmp = "Add '" & NewData & "' as a new doctor?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
        strTmp = "INSERT INTO doctors ( DoctorName ) " & _
        "SELECT """ & NewData & """ AS DoctorName;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        Response = acDataErrAdded
    End If
End Sub

 

[FahadTiger]

you are awsome my experts ..thank you isladogs and arnelgp... its perfect your solutions