FORMS - add to list problem

[eaglei66]

I have a form that adds items to my lookup table if the item is not already in the list. the problem i am having is if it was a typo and the user says "NO" it totally blanks out the form and makes you start over. Is there a way to make it just allow you to make a correction to the entered text.

here is the code that I am using.:

Private Sub producerID_NotInList(NewData As String, Response As Integer)
Response = MsgBox("The Producer you entered is not in the list." & vbCrLf & vbCrLf _
& "Would you like to add it?", vbQuestion + vbYesNo, "New Producer Location?")
If Response = vbYes Then
Set DB = CurrentDb
Set rs = Recordset
Set rs = DB.OPENRECORDSET("Producers")
With rs
.AddNew
.Fields("producers") = NewData
.Update
End With
Response = acDataErrAdded
Else
Me.Undo
Response = acDataErrContinue
End If
End Sub


thanks in advance for your help.

Eaglei66

 

[Pat Hartman]

Me.Undo backs out updates to ALL fields. You just want to backout the update to the current field so you should use Me.ProducerID.Undo.

Also, although it isn't causing a problem here, you should define your own variable to hold the response to the message box. "Response" is the variable that is passed back to Access to tell it what to do when the sub ends.