Not in List Continuous Forms 1

[Elvis72]

I have a Combobox on a Subform that is set to continuous forms.

The below Not in List Even works great until I go to the next record.

Everything is fine until I close the form that pops up, then it reverts back to record 1 and replaces whats there.

Private Sub TitleID_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Customers form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Customer form's Form_Load event
' procedure.
DoCmd.OpenForm "FrmPositionTitle", , , , acAdd, acDialog, NewData
End If

' Look for the customer the user created in the Customers form.
Result = DLookup("[TitleID]", "TblStaffRequirements", "[TitleID]=" _
& DMax("[TitleID]", "TblStaffRequirements"))

If IsNull(Result) Then
' If the customer was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded

' If the customer was created, set the Response argument to
' indicate that new data is being added.
Me.Undo
Me.Requery

Me.TitleID = Result

Response = acDataErrContinue
End If
End Sub

 

[PHV]

What happens if you get rid of the following lines ?
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Me.Undo
Me.Requery

Me.TitleID = Result

Response = acDataErrContinue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

Thank you thank you thank you!~

I thought I had to have it to reload the data to show the new item!~