NotInList Event

[laura525]

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

I am having a problem with this code. When I try to enter data that is not in the list the message pops up. When I click Yes (I would like to add it) it clears the form. I want it to go to the supervisor field so I can add a supervisor to go with the ClockNo.

 

[mph1]

Hipefully the following will help.

http://www.mvps.org/access/forms/frm0015.htm

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106