Try this:
Code:
Private Sub pilgrimchurch_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String
Response = acDataErrContinue
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "[" & NewData & "] is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Church Name...")
If i = vbYes Then
strSQL = "Insert Into tbl_church ([churchname]) " & _
"values (""" & NewData & """);"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
End If
End Sub
You are aware that you have no error handling code to catch any errors, right?