NotInList event - text isn't item in list error

  • Thread starter Thread starter Tim Zych
  • Start date Start date
T

Tim Zych

I am trying to use the NotInList event to add a value to a table if it does
not exist in a combobox.

After I add a value to the table, I am getting an error "The text you
entered isn't an item in the list", even though I can see it in the
dropdown.

The combobox is 2 columns. The 1st column is EmplID and is bound to EmplID
in the form's record source. The row source is "SELECT
[tblEmployee].[EmplID], [FirstName] FROM tblEmployee;"

The form's record source is "SELECT tblIssue.EmplID FROM tblIssue;"

I have 2 questions:

-- How do I avoid the error that I get after I update the table with
NewData?

-- How can I view the newly-added value in the combobox, after it has been
added to the table? I have tried:

cboAssignTo.Column(1) = NewData

but that doesn't seem to work. If I just add a command button with
standalone code:
cboAssignTo.Column(1) = "Value that is already in the textbox"

Access returns and error:
Run time errro 424
Object Required

Private Sub cboAssignTo_NotInList( _
NewData As String, Response As Integer)

Dim strMsg As String
Dim rs As ADODB.Recordset

strMsg = "'" & NewData & "' is not in the list." & vbLf & vbLf
strMsg = strMsg & "Do you want to add it?"

If MsgBox(strMsg, vbQuestion + vbYesNo) = vbNo Then
MsgBox "Please try again."
Exit Sub
End If

Set rs = New ADODB.Recordset

rs.Open "tblEmployee", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

rs.AddNew
rs!FirstName = NewData
rs.Update
rs.Close
Set rs = Nothing

cboAssignTo.Undo
cboAssignTo.Requery

End Sub


Thanks
 
Back
Top