I have a table, tblTaskList, which consists of 2 fields - TaskLstID and strTask.
The combo box (named txtTask) rowsource is "SELECT tblTaskList.TaskLstID, tblTaskList.strTask FROM tblTaskList;"
The bound field in the combo is 2, the text field, not 1 the primary key field.
Limit to list is no. The combo is bound to a field in tblTasks.
When a user types in a new task that is not in the list I use an after update event to mimick the not in list event.
Code:
Private Sub txtTask_AfterUpdate()
If DCount("*", "tblTaskList", "strTask = """ & Me.txtTask & """") = 0 Then
Select Case MsgBox(Chr(34) & UCase(Me.txtTask) & Chr(34) & " is not in the list." & vbNewLine & vbNewLine & "Do you want to save it for future use?", vbYesNo, "Confirm Save")
Case vbYes
CurrentDb.Execute ("Insert into tblTaskList(strTask) values(""" & Me.txtTask & """)"), dbFailOnError
Me.txtTask.Requery
Case vbNo
End Select
End If
End Sub