Notinlist event on same form error

[akasmia]

Ok this is very simple BUT I am not making it work !.

I am using a single table underlying the same form which contains the combobox cmbFindbyLastName. When I enter new data in the combobox the message generated by the msgbox function keep hounding me !. Keeps saying the same thing "smith" not found do you want to add it?. When I finally give up and hit cancel then I get an error: Runtime error 3709; the search key was not found in any record.




HERE IS THE CODE:

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

    Dim ctl As Control
    Set ctl = Me.cmbFindbyLastName
    
    If MsgBox("This name: '" _
        & NewData & "' Does not exist do you want to add it to the list?", _
        vbOKCancel, "Add New Item?") = vbOK Then
        ' Undo combobox data just typed...
        ctl.Undo
        ' Add new record using the same form
        DoCmd.GoToRecord , , acNewRec
        ' Set forcus on the combo box
        Me.cmbFindbyLastName.SetFocus
        ' Store new name
        Me.cmbFindbyLastName.Text = NewData
        ' Requiry form ( update table)
        Me.Requery
        ' requiry combo box
        Me.cmbFindbyLastName.Requery
        ' Go to the last record ( newly added record)
        DoCmd.GoToRecord , , acLast
        ' Set focus on first name textb
        Me.First_Name.SetFocus
        ' Continue without displaying default error message.
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
        
End Sub

 

[Trevil]

One problem in your code:

"Me.cmbFindbyLastName.Text = NewData" causes the 'NotInList' event to fire again (and again and ...)

See this link:

http://visualbasic.about.com/library/weekly/aa060703b.htm

"Hmmm, it worked when I tested it....