I just had to figure this out today myself, and here's what I came up with. IT WORKS TOO!
On combo box, limit to list needs to be 'yes'
and the name needs to be changed below to whatever name your combo box has.
Private Sub ActivityNames_NotInList(NewData As String, Response As Integer)
'Handle non-list items entered into
'ActivityNames combo box
Dim bytResponse As Byte
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
'On Error GoTo HandleErr
bytResponse = MsgBox("Do you want to add " _
& NewData & " to the list?", vbYesNo)
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
.Open "ltblKeyWords", cnn, adOpenDynamic, _
adLockOptimistic
If bytResponse = vbYes Then
.AddNew "KeyWord", NewData
.Update
Response = acDataErrAdded
ElseIf bytResponse = vbNo Then
Response = acDataErrContinue
ActivityNames.Undo
GoTo ExitHere
End If
End With
ExitHere:
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & _
Err.Description, vbOKOnly
Resume ExitHere
End Sub
