This is what I use for all my lists
put this in the not in list :
Response = FillListsOne(NewData, "LtblCityLookup", "City")
If Response = acDataErrContinue Then
RunCommand acCmdUndo
End If
Put this in a module
Under Option Explicit add
Private m_rst As Recordset
Private m_IntNew As Integer
Private Const m_strMsg As String = " Is Not An Item In The List Would You Like To Add It"
Private Const m_strUndoMsg As String = " Is Not In the List Undoing Entry"
Private Const m_strTitle As String = "Undoing Invalid Entry"
'I Use theses as I have a number of list funtions
Public Function FillListsOne(StrItem As String, StrTable As String, _
StrField As String) As Integer
On Error GoTo Err_HandleErr
m_IntNew = MsgBox(StrItem & m_strMsg _
, vbInformation + vbYesNo, "Item Not In List")
If m_IntNew = vbYes Then
Set m_rst = CurrentDb.OpenRecordset(StrTable)
m_rst.AddNew
m_rst(StrField) = StrItem
m_rst.Update
FillListsOne = acDataErrAdded
Else
MsgBox StrItem & m_strUndoMsg, , m_strTitle
FillListsOne = acDataErrContinue
End If
Exit_HandleErr:
m_rst.Close
Set m_rst = Nothing
Exit Function
Err_HandleErr:
Select Case Err.Number
Case 91
Exit Function
Case Else
MsgBox "The Following Error Has Occured & vbCrLf" _
& "Error Number: " & Err.Number & vbCrLf & "Error Description" _
& vbCrLf & Err.Description
Resume Exit_HandleErr
Resume
End Select
End Function
