Not In List acDataErrAdded is not working 1

[MelissaKT]

I could really use some help. I can't figure out what I've done. I did a database upgrade, but copied most of my forms and code so it wouldn't take so long to finish. One of the things that I copied was a not in list event that worked fine in the old database. The not in list event is firing, it runs through all of the code, it appends the data to the table but I still get the "The text you entered isn't an item in the list" error. Here is the code:

Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
On Error GoTo HandleErrors
Dim ctl As Control
    DoCmd.SetWarnings False
    ' Return Control object that points to combo box.
    Set ctl = Me.cboVendor
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        DoCmd.RunSQL "INSERT INTO tblVendor (VendorName) Values ('" & UCase(SQLFixup(NewData)) & "');"
        
       
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If
ExitHere:
    Exit Sub
HandleErrors:
    MsgBox err.Number & " " & err.Description
    Resume ExitHere
End Sub

 

[PHV]

Perhaps this ?
...
Response = acDataErrAdded
DoCmd.RunSQL "INSERT INTO tblVendor (VendorName) Values ('" & UCase(SQLFixup(NewData)) & "');"
DoEvents
ctl.RowSource = ctl.RowSource
...

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

 

[MelissaKT]

PHV, Thank you for responding. That still doesn't work. It goes all the way through the routine and then, when it gets to "exit sub", the error "The text you entered isn't an item in the list" comes up. I just don't understand why it isn't working. I saw on a Microsoft Web site that sometimes it has to do with formatting. But, in this case, the combo box is bound to an integer. The name is a string. Am I missing something?

 

[PHV]

My guess is that UCase(SQLFixup(NewData)) is not the same as NewData ...

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

 

[MelissaKT]

I figured it out! The combo box was set upon a table that has Vendor ID, Vendor Name and a delete flag. The sql statement for the combo box was basically "SELECT vendorid, vendorname from tblvendor where deleteflag = 0". Well, the default value of the table wasn't set. SO, it wouldn't let me update it since it didn't match the criteria. I hope I'm making sense!

Thank you for your help.

 

[PHV]

Bottom line, comment out the SetWarnings when in development phase ...

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