Trouble with NotInList

[laurat]

Here is the code I have in my NotInList event

Private Sub ClockNo_NotInList(NewData As String, Response As Integer)

Dim cn As Connection
Dim rs As ADODB.Recordset


If MsgBox(NewData & " is not in the selection provided. Would you like to add it?", vbQuestion + vbYesNo, "Unknown Course") = vbYes Then
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

With rs
.Open "Supervisor", cn, adOpenStatic, adLockPessimistic
.AddNew
.Fields("ClockNo") = StrConv(NewData, vbProperCase)
.Update
.Close
End With

Response = acDataErrAdded
Else
Me.ClockNo.Undo
Response = acDataErrContinue
End If

Set rs = Nothing
Set db = Nothing

End Sub

My error message pops up, then a Microsoft error message pops up and says you must choose data from the list and when I click ok it sticks me back into that field. So then when I try to enter the same thing again I get an error that says I am trying to enter data that is already in the table. How can I get the Microsoft message not to pop up? Also do you see anything wrong with my code?

 

[quest4]

Just a simple thought, have you tried adding: DoCmd.SetWarnings False
Just don't forget to turn them back on. hth.

 

[laurat]

When I debug it is stopping at the line of code that says .Update if that helps.

quest, where would I ad that code?

 

[quest4]

I would try them just after the DIm statement and just before the End Sub. I have heard of this before, I think this took care of it but I am not positive. Trying to get around system warning is always a pain in the butt. What I usually will do if I need it, is turn off the notinlist and in the txtbox's AfterIpDate event and add a DCount statement where if it =0 then your stuff. Just a thought for you. hth.