Create a combo box. on event Not in list, put code that would add a new record to it if it's not in list.
Besides the text box, create a command button that would open a form that is attached to a table you want to edit. Upon click of button, it should show whatever is in the combo box.
Do not forget requery
Private Sub ContractFunder_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ContractFunder_Click
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strfunder As String
Dim strsql As String
Dim ctl As Control
Dim stDocName As String
' Return Control object that points to combo box.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("FunderInformation")
Set ctl = Me!ContractFunder
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
strfunder = Forms!contracts!ContractFunder.Value
rst.AddNew
rst(1).Value = NewData
rst.Update
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
rst.Close
Me.ContractFunder.Value = NewData
Me.ContractFunder.Requery
stDocName = "FunderInformation"
'ctl.RowSource = NewData
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, , "Add"
'MsgBox "after open form"
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
Exit_ContractFunder_Click:
Exit Sub
Err_ContractFunder_Click:
MsgBox Err.Description
Resume Exit_ContractFunder_Click
