I have a combo box that has a control source called casestatus. If a value entered is not on the list and event fires. If I click ok it entered the new value in the lookup table. If I cancel I keep getting promt with the not on list error form access. How can I click cancel on adding to lookup tale and move on?
Private Sub Combo178_NotInList(NewData As String, Response As Integer)
Dim strTmp As String
DoCmd.setwarning = False
'Get confirmation that this is not just a spelling error.
strTmp = "Add '" & NewData & "' as a new Case Type?"
If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
'Append the NewData as a record in the Categories table.
strTmp = "INSERT INTO tblSubLookup ( Thetext, TheDropdown) " & _
"SELECT """ & NewData & """ AS TheText,('Case Type');"
DBEngine(0)(0).Execute strTmp, dbFailOnError
'Notify Access about the new record, so it requeries the combo.
Response = acDataErrAdded