NotInlist event error 2

[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

 

[THWatson]

akasmia
Just one small thing to check: The "Limit To List" for the combo box has to be set to Yes in order for the code to work.

Tom

 

[TheAceMan1]

How are ya akasmia . . . . .

Try this ([blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]   Dim Msg As String, Style As Integer, Title As String, SQL As String
   
   Msg = "This name: '" & NewData & "' " & _
         "Does not exist do you want to add it to the list?"
   Style = vbQuestion + vbOKCancel
   Title = "Add New Item?"
   
   If MsgBox(Msg, Style, Title) = vbOK Then
      SQL = "INSERT INTO [purple][b]TableName[/b][/purple] ([purple][b]FieldName[/b][/purple]) " & _
            "VALUES ('" & NewData & "');"
      DoCmd.RunSQL SQL
      
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If[/blue]

See Ya! . . . . . .

 

[akasmia]

Thank you TheAceMan1 !.

It works well but form does not show the new record for user to add the remaining firlds.

I have had to add the following

Me!cmboSearch_by_First_Name.Undo

DoCmd.ShowAllRecords

' Find the record that matches the control.
If Not IsNothing(cmboSearch_by_First_Name) Then
Me.RecordsetClone.FindFirst "[ID] = " & Str(Me![cmboSearch_by_First_Name])
End If


It is not not behave well but that's the best I can do for now.

Thanks again !

 

[ZmrAbdulla]

Did you check this at mvps.org?

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

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[TheAceMan1]

Roger That akasmia . . . . .

The biggest problem is you [blue]can't/shouldn't change record while the NotInList event is executing.[/blue] This will cause a type of recursion.

To get around this:
[ol][li]In the [blue]Declarations[/blue] section of the module, copy/paste the following:

[blue]Private NewLastName As String[/blue]

[/li]
[li]In the code, add the line in [purple]purple[/purple] where you see it:

[blue]  Dim Msg As String, Style As Integer, Title As String, SQL As String
   
   Msg = "This name: '" & NewData & "' " & _
         "Does not exist do you want to add it to the list?"
   Style = vbQuestion + vbOKCancel
   Title = "Add New Item?"
   
   If MsgBox(Msg, Style, Title) = vbOK Then
      SQL = "INSERT INTO TableName (FieldName) " & _
            "VALUES ('" & NewData & "');"
      DoCmd.RunSQL SQL
      
      [purple][b]NewLastName = NewData[/b][/purple]
      
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If[/blue]

[/li]
[li]Finally in the [blue]AfterUpdate[/blue] event of the combobox, copy/paste the following:

[blue]  If NewLastName <> "" And Not Me.NewRecord Then
      DoCmd.RunCommand acCmdRecordsGoToNew
      Me!cmbFindbyLastName = NewLastName
      Me!First_Name.SetFocus
   End If
   
   NewLastName = ""[/blue]

[/li][/ol]
Don't forget to [blue]disable[/blue] the code you mentioned in your prior post before testing.

[purple]Give it a whirl & let me know . . . . [/purple]

See Ya! . . . . . .

 

[akasmia]

Hi TheAceMan1!

Thanks... works very well!

I have had to modify it a bit:

Rem Try this...........................................
If NewLastName <> "" And Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
' Me!cmbFindbyLastName = NewLastName <-- Generates err!
Me.Requery[U/]
Me!cmbFindbyLastName.Requery
DoCmd.GoToRecord , , acLast
Me!First_Name.SetFocus
End If

NewLastName = ""
Rem End of try this.....................................

Once again many thanks !!.

 

[akasmia]

Hi TheAceMan1!

Thanks... works very well!

I have had to modify it a bit:

Rem Try this...........................................
If NewLastName <> "" And Not Me.NewRecord Then
DoCmd.RunCommand acCmdRecordsGoToNew
' Me!cmbFindbyLastName = NewLastName <-- Generates err!
Me.Requery
Me!cmbFindbyLastName.Requery
DoCmd.GoToRecord , , acLast
Me!First_Name.SetFocus
End If

NewLastName = ""
Rem End of try this.....................................

Once again many thanks !!.